Everyone has the need to import and export data from a CSV into their MySQL database at some point or another. A quick search of Google reviles there are many different ways of doing this, and in my expectance some are far better than others. In this tutorial I’m going to show you my favourite way to import CSV data into MySQL using the “LOAD LOCAL DATA FILE” command.

To do this first you must understand what a CSV file is, it stands for Comma Separated File, as the name suggests it consists of data separated by a comma. When you create a new CSV file you enter each Column followed by a comma, then you’re ready to move to the next row you enter a line terminator (usually \n). This simple way of formatting data is vary us full because nearly all database and spreadsheet programs are compatible with it. This enables you to layout the data you want in excel then export it to a CSV then import this CSV into your MySQL database.

To do this all you need to do is issue a SQL command in the same way you would any SQL command, either by PHP, the MySQL console or using some tool such as MySQL Workbench. The command you should execute is:

LOAD DATA LOCAL INFILE '[File Name]'
INTO  [Table]
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
([field], [field], [field]);

Now you may need to change this command depending on your database, the filds you’ll need to change are:

• [File Name] Substitute [File Name] for location of your CSV file
• INTO TABLE [Table]: Substitute [Table] for the table name your are trying to populate
• ([field], [field], [field]); Here substitute the field names you want to populate, remember that the order of these fields represent the order of columns in your CSV file.
• TERMINATED BY & ENCLOSED BY This is the general convention used by excel when you export as CSV, however if you know different you may need to change these

And that’s it; once you execute this command the data stored in the CSV file will be imported into the database. There are a few things to watch out for,

• Don’t Include Column Names In The CSV, This will always end as tears as I bet you don’t want the field names stored in your database, and they are often the wrong data type.
• Be Careful of Empty Rows, Its very easy to insert blank rows, either before your data or after your data when working in excel, its worth checking your CSV in notepad to ensure there are no empty rows, they will look like a line of comer’s and will nearly always cause the import to error.

Please leave any Comments or feedback, Thanks.