A useful technique when working with MySQL is to import data in a CSV into MYSQL using PhpMyAdmin.

This CSV could be created in Microsoft Excel or OpenOffice Calc. Unfortunately different systems like their csv files formatted in different ways and phpMyAdmin is no exception.

Following these steps and you should be able to import with no issues :

Note: Excel only wraps text fields in double quotes if the text contains commas or double quotes. OpenOffice wraps all text fields in double quotes.

Clean The CSV
Open the CSV file in a decent text editor such as Notepad++ or editPad.

If the first line contains column headings then delete the line.

Search for two double quotation marks ("") and replace with back-slash double quotation mark (\").

Save the csv file.

Here is a simple example :

1,"Normal text","Text, with, commas","Some 'quoted' text","Some ""double quoted text"""

Import Data
Open phpMyAdmin and navigate to the table you wish to populate.

Click the "Import" tab.



Click the "Browse" button and locate your CSV file.




Make sure "CSV" is selected as the import type.





For some reason known only to the developers the default field delimiter is a semi colon. This is a little strange for a COMMA seperated import! So make sure your settings are :

Set "Fields Terminated By" to a comma.
Set "Fields Enclosed By" to a double quote
Set "Fields Terminated By" to a backslash
Set "Lines Terminated By" to auto.

Enter a comma separated list of the column names represented in your CSV file eg. colname1,colname2,colname3,colname4

Once all the settings are made click "Go".

Hopefully the import was successful!

Troubleshooting
If the import fails here are some things to double check :

1) Make sure your data fits into the table columns. ie if a text field is limited to 100 characters make sure you don't attempt to insert too much text.

2) Make sure your data matches the data type of the table columns. Don't insert a block of text into a number field.

3) Make sure your data contains the correct number of columns in the correct order as specified in the "Column names" text box.

4) Make sure the number of columns matches the number of columns in your target table.

5) Make sure all commas in your text data are escaped with a backslash.

6) Make sure all double quotes are escaped with a backslash.