How to Import UTF-8 Encoded Excel Spreadsheet Into MyQL Database!

Amazon Let's say you've built content in an Excel spreadsheet and you'd like to import the content into a MySQL database table. The columns of your spreadsheet would correspond to columns of your database table. Let's say the content's encoding is UTF-8 and it could be in a language other than English. What's the fastest and easiest way to import a UTF-8 encoded spreadsheet into MySQL? I certainly don't want to write a program to do that.

The short answer is you can use PhpMyAdmin to import a UTF-8 encoded spread sheet into your MySQL database without writing any code. However there may be many hoops to jump through before you succeed. Let me walk you through the entire process.

I use the following software:

PhpMyAdmin 3.4.10.1
Excel 2007
MySQL client version: mysqlnd 5.0.8-dev - 20102224 - $Revision: 321634 $
MySQL Server version: 5.5.20-log Protocol version: 10

Step 1: Create MySQL Database and Set up PhpMyAdmin
You should already know how to create the database table you need in MySQL. Also set up PhpMyAdmin if you haven't done so.

The assumption is each column in your Excel spreadsheet corresponds to a column in the database table. The order of columns is not important as you will be able to tell PhpMyAdmin the column order if it's not the same as the column order of your database table.

Step 2: In Excel, Save Your Spreadsheet as CSV
Simply save your spreadsheet as a CSV by picking "CSV (Comma Seperated Value) (*.csv)" option in the drop down list of the Save dialog.

A caveat is when you save a spreadsheet as csv Excel tries to play "smart" and may ignore columns that are totally empty even though you intentionally leave them empty.

To solve this problem, put a random character in the said column and save it as csv. Then Excel would preserve that column. Open the csv and delete the random character.

Step 3: Import CSV into MySQL Using PhpMyAdmin
In PhpMyAdmin click on 'Import' at the top and specify CSV as the format. Click Go to complete the import.

You are done. Browse your MySQL database and see if the content has been imported properly. If not, read below for troubleshooting.

Troubleshoot
1. If you get 'run out of memory' issue then increase your memory in php.ini. Find 'memory_limit' and set it to 256M or something. Note the bigger the csv the more memory needed to process the csv.
2. If PhpMyAdmin only imports the English content and not the other content written in some other language, then open the csv in Notepad and save the csv as UTF-8 in Notepad.
3. If you get the 'Invalid column count in CSV input on line 1' error or 'Invalid field count in CSV input on line 1' error in PhpMyAdmin, then open your csv with Notepad and do a global replacement of "" with \". Then make sure you have '\' (less quotes) in the text field next to "Columns escaped with".

Questions? Let me know!