phpMyAdmin – Import Excel CSV into table

Importing a CSV file which was saved from Excel 2007/2010 into MySQL using phpMyAdmin is easy once you know how! Just carefully follow all of these steps:

1. Sanitize the CSV file: Open the CSV file in a text editor (preferably something better than notepad.exe, such as Notepad++). If the first row contains column names, delete it. If there are extra last rows at the bottom of the file, delete them (leave one completely blank line at the end). Do a search for “,,” (two commas in a row) and wherever you find that, add a value between those commas; the value should match the type of that column, so if it’s a numeric column, add a number (0 or -1), and so on. Do a search-and-replace for “” (two quotation marks) and replace all by ” (backslash quotation mark). Save!

2. Open phpMyAdmin: In phpMyAdmin, click the table, and then click the Import tab at the top of the page.

3. Import: Browse and open the (sanitized) csv file. Leave the charset as-is. Uncheck partial import unless you have a HUGE dataset (or slow server). The format should already have selected “CSV” after selecting  your file, if not then select it (not using LOAD DATA). If you want to clear the whole table before importing, check “Replace table data with file”. Optionally check “Ignore duplicate rows” if you think you have duplicates in the CSV file. Now the important part, set the next four fields to these values:

  • Fields terminated by: ,
  • Fields enclosed by: “
  • Fields escaped by:
  • Lines terminated by: auto

Currently these match the defaults except for “Fields terminated by”, which defaults to a semicolon. The crucially important sanitization step prepared the CSV file to work with these values so make sure you did in fact follow my sanitize step above. Now for column names, put a comma separated list of your table’s column names which match the CSV columns. This is case sensitive, no spaces after the commas, no quotes around the table names. Like this: column1,other_column,column3.

Now click the Go button, and it should run successfully. I tested this method with values that contained combinations of single quotes, double quotes and commas, and the values all seemed to import correctly after following everything above. If you have problems with this method please email me via the button on my homepage, and if you found this to be helpful please consider commenting or reposting and linking back to here so that perhaps this can rise to the top of search results. Thanks!

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

11 Responses to phpMyAdmin – Import Excel CSV into table

  1. Tweedle_Dee says:

    Thanks for posting this. It was helpful to me while getting started with this.

  2. Michael Yin says:

    Thank you so much. It really works!

  3. very tired soul says:

    EXCELLENT.

  4. joanne says:

    You have just saved me hours upon hours of inputting data manually into the database. I cannot thank you enough!!!

  5. Mark Morrison says:

    You just saved me hours of work after finding the the information from an ‘front end’ import wasn’t as expected. And of course my sanity in checking records 1 by 1 from the UI!

  6. Thanks. I had quotes at the beginning and end of fields so I had to run some Notepad++ regex in this order. These may help some folks:

    — Replace extra quotes at the beginning of a field
    ,”””
    ,””

    — Replace extra quotes at the end of a field
    “””,
    “”,

    — Replace extra quotes, ignoring enclosing ones at the end of a field
    “”([^,])
    “1

  7. Steve says:

    Does that mean there are supposed to be quotation marks bracketing each bit of data? As in “data1″,”data2”, etc? When I start with any CSV file, I just get the commas delimiting each field when I review it in TextWrangler or the like. Do I need to add the quotes if they’re not in there?

    • Richard says:

      No, quotes are optional and are typically only added around fields which contain commas (which, without the quotes would otherwise be mistaken for the delimiter).

  8. Patrick says:

    THANK YOU! This was very helpful!

  9. Steffen says:

    Thank you very much for your help.
    You just saved me 50$ for the software needed to do this automaticly.
    Great guide, worked as intended.

  10. black says:

    thank you for this tutorial.your tutorial solved my problem !!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s