Importing Excel CSV’s using PHPMyAdmin

I have developed some new technologies for the web site which enables a dynamic slide show to be displayed through some simple SQL calls. The one thing that I have learned about importing CSV files is that it is far from easy. While trying to solve these problems, I did a lot of queries and again there was little to no solutions on the web. So in addition, to my last article, here are two more common problems and solutions when importing CSV files using Microsoft Excel.

Problem One

Excel Extra Commas in the CSV file

This problem was driving me nuts. Excel puts random commas in a CSV file. Guess what happens when you have extra commas in a Comma Separated Values File? It crashes and does not import. Google search shows that the common fix is to review the file for extra commas and edit them out manually. Since my database is now fairly big, this was not an option. There were literally thousands of extra commas that had to be edited out.

What’s the solution? Don’t use Excel. I started using Open Office and it does not have the same bug. In fact, I am going to start using Open Office as much as possible to see how difficult is the learning curve. Note: Excel has another bug, where it does not put the double quotes around certain text fields.

Problem Two

Be Careful with Character Sets

My database is stored in Latin_Swedish. This is very important to me, because I use foreign characters in my database and in my blogs. Characters such as é,ç, and ê. In order for this to work correctly, you have to be very consistent when importing your CSV’s or none of your foreign characters will be displayed in the database correctly. Instead of the foreign character, it will be stored with a ?.

The solution is simple. When importing the file, make sure you choose the proper character encoding. PHPMyAdmin defaults to UTF8, and then in my case, was the source of losing all my precious international characters.

I hope these two tips helps someone out there.