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!