Converting CSV to mySQL

Regular Readers: you’ll want to ignore this. It’s boring, technical stuff. I’m just posting this here as a little public service act. There is another, real post just below this one.

Everyone else who actually searched for this: read on.

I’ve had major headaches trying to import data from Excel into MySQL. I never found anything online that adequately addressed the issues I had. After a lot of trial & error, I’ve found a way to do it. I then wrote down every step for myself because I know 5 months from now, I will completely forget what I did.

I decided to share this information to save others the trouble so they can avoid errors such as the dreaded “Invalid field count in CSV input.” This assumes you are familiar with using phpMyAdmin and Excel.

These steps worked for me. If they don’t work for you, well I’m sorry. I tried.

1. Create your table using phpMyAdmin. Make sure you have the same number of fields here as the fields in the data you want to import.

2. Clean up your Excel file. Try to avoid having semicolons or quotes in your data or it’ll mess with the import. If you must have those, you need to use the escape character \ before every single instance. The easiest way to clean that up is to use the ‘Find & Replace’ in Excel.

There is also a problem with blank field values, even if you create the field in MySQL with a null value. No matter what I’ve tried, I run into errors when attempting to import fields with empty data.

To get around that, I use ‘Find & Replace,’ to replace the empty fields with  . I click the ‘options’ button and check ‘Match case’ and ‘Match entire cell contents.’ Then click ‘Replace All.’

After that, save it as a normal Excel file. It’s probably a good idea to save it with a different name just in case you accidentally deleted something you needed without realizing it. I like to play it safe & always back up everything before making changes to files, especially files with thousands of records.

3. Open the Excel file in Open Office Calc. Open Office is a free, open source program that is compatible (& comparable) with MS Office that you can download here. I use it because it allows you to specify exactly how you want your CSV file saved. If there is a way to do the same thing in Excel, I have yet to find it.

4. Within Open Office Calc, save your Excel file as a text CSV file using the following values:

Character set: utf8
Fields terminated by ;
Text delimiter “

I know, I know. CSV stands for “comma separated” value so why are we using a semicolon? Well it’s because a semicolon seems to be the default setting in phpMyAdmin. I prefer not to mess with defaults, the logic being they must have picked it for a reason.

5. Open your CSV file in notepad to double check that the field contents are within quotes and the fields are separated by semicolons. Make sure you don’t have your field headers in the first row. (If you do, just delete it.)

6. Go into phpMyAdmin. Select your database and table. Then click the “Import” tab. Browse to the file you just saved. The default character set of the file should be utf8 but if it’s not, change it to that. The format is, of course, CSV.

My options are always:

Format of imported file: CSV using LOAD DATA
Fields terminated by ;
Fields enclosed by ”
Fields escaped by \
Lines terminated by auto

Click the “Go” button. All of your data should have been imported into the database. Click the ‘Browse’ tab to check it. Like I said, this is what works for me. If it doesn’t work for you, well go find someone who’s smarter than I am to help you out. I’m not an expert on this. I’m not an expert on much of anything.