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:
Export to .csv from Open Office
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:
Change that semi-colon!
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.
Thank you very much!!!! So simple…
I dont know how many hours i spent to fix it.
Now i know. Thank you for sharing.
You are welcome Chris. This took me some frustrating down-time before fixing it too.
Hi !
Great to see that i’m heading in the right direction, however, while performing the way you recommend, i still get wierd characters (swedish åäö gets displayed as strange characters). I myself belive that i’m, although saving as a .csv file in OpenOffice, getting an ANSI-based file thus malforming characters. Just wondering if you have any useful experience in this matter ?
Thanks a mille in advance !
Kalle
Hi Kalle
Sorry – I haven’t had this issue. It might be worth running this by my colleague over at Edmonds Commerce who helped me initally resolve this issue. Post back and let me know how you get on, Joseph is very helpful!
Hi again !
Actually as i turns out, i managed to sort it all out. Instead of choosing “CSV using LOAD DATA”, i just chose “CSV”, and there it was, proper characters. Thanks a lot though for taking time out to read my question.
Cheers !
Kalle