Import CSV files into your MySQL database tables

Published: April 30th, 2014

LuxSci’s MySQL database hosting console now enables you to import data into MySQL database tables directly from a CSV (Comma Separated Variables) file, e.g. a simple and universal form that spreadsheets can be saved or exported to.

Why would you want to import data from CSV?  Well, you could do the same thing by writing SQL code and importing this to your database or by connecting remotely to your database and issuing commands.  But both of those methods take some time and expertise to set up.  If you have a spreadsheet of data or a CSV of data created from some other system (e.g. customer information, contact lists, inventories, etc.), you can simply upload this file and populate your database with its new contents in a matter of seconds.

Here is how it works

  1. Login to your LuxSci account as an account administrator
  2. Go to “Administration”
  3. Click on “Databases” at the top
  4. Click on the database to which you wish to import data from CSV
  5. Click on “Import from CSV” in the menu on the left

Next:

  1. Upload your CSV file
  2. Specify if the data should be imported into a new table (and you give it the name), or select an existing table
  3. Choose if any data in the existing table should be deleted before import of the new data

The system analyzes your CSV file and matches the header columns to your MySQL field names:

  1. It auto-suggests new column names for CSV fields that do not appear to match your database table
  2. It auto-selects matching columns, if any
  3. You can choose which column to import, which to skip, and customize the match back

You press “Import” and all your data gets imported in as specified.

 

Technical Note

MySQL column names have restrictions on the length of their names, what characters can be used, etc.  As such, there can not be a one-to-one mapping between your exact CSV field names and the MySQL column names in many cases.  So, the import system automatically tries to convert your field names into valid column names (by removing spaces and special characters, by truncating to 32 characters in length, by checking against “MySQL reserved words”, etc.).  It is best to use CSV column headers that are:

  • Not excessively long
  • Contain mostly letters and numbers
  • Are all unique (no duplicate column names)

The import will not import your file if it cannot make up unique MySQL column names for each of your CSV column headers.

Leave a Comment


You must be connected or logged in to post a comment. This is to reduce spam comments.

If you have not previously commented, you can connect using existing social media account, or register with a new username and password.