Trouble Importing CSV into phpMyAdmin
Having just had a big issue with this one, I thought I’d post a solution to an apparently common mistake with importing .csv files into a MySQL database through phpMyAdmin.
Firstly though, a big thanks to my colleague Joseph at Edmonds Commerce for helping me with this one.
To start with, I would recommend that .csv files are created using Numbers from the Apple iWork Suite or the very excellent FREE open source Office Suite called OpenOffice. In my experience, Excel does not always create the most reliable .csv files when I’ve tried working with file imports.
I used OpenOffice for this example, to convert an Excel .xls (NOT .xlsx 2007 version) file, by going to File > Save As… and choosing .csv from the format options. Here are my settings:
The field delimiter is a comma.
If you are wanting to load this file into a table in your MySQL database, clear the table out first. (Don’t drop it, just empty it.) Once that is done, go to the Import tab, browse to your .csv file and use the settings below:
For some reason, phpMyAdmin defaults to a semi-colon for the “Fields terminated by” entry. This needs to be changed to a comma. (You could have changed it in OpenOffice but I prefer to do it here.)
Once that is done, click the Go button to import your data.other page