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

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!

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.

other page


5 Comments

  1. Chris

    Thank you very much!!!! So simple…

    I dont know how many hours i spent to fix it.
    Now i know. Thank you for sharing.

    • Designermagic

      You are welcome Chris. This took me some frustrating down-time before fixing it too.

  2. Kalle Johansson

    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

    • Designermagic

      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!

  3. Kalle Johansson

    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

It's good to talk!

Your email address will not be published.