Importing CSV’s using PHPmyAdmin

When working on the photo part of my web site, which is a many-to-many database layout, I recently ran into the following frustration. I have found that it is much easier to manipulate my tables in Excel, and then uploading the resulting CSV back into MySQL. But I kept getting a wide variety of errors stating that I had an error.

invalid field count beginning in line XXXX.

Here is a link to one of many people having the same problem.

http://www.phpbuilder.com/board/showthread.php?t=10328310

There are actually two bugs in Excel that are causing invalid field counts. Check out this link:

http://support.microsoft.com/kb/77295/en-us#appliesto

The issue arises when the last column has blank fields in it. That was certainly the case with me. The trick is to place at least one character in each row of the last field. I decided to add &nbsp to each field that was not already populated. The logic in Excel looked like this:

=if(f4=””,”&nbsp”,f4)

That did the trick but it also uncovered one more bug. When you save the file as a CSV out of Excel, Excel treats the double quote different than mySQL. Excel puts two double quotes together indicating that one should be ignored. PhPmyAdmin has an exception character which defaults to a back slash (\). You cannot use double quote as the exception character because then PhPmyAdmin will ignore all instances of the double quote which is disastrous.

So here’s the work around. Save the files as a CSV in Excel. Next, open the CSV in a text editor such as notepad. Then do a global replace of (“”) with (\”). Then upload the CSV and you are done.

It seems simple now, but it took me a day to figure it all out and I did not find a good reference on the net for this issue so I thought I would add it to my blog.

Another thing. The character set of the file should be changed to Latin1 as it defaults to UTF8. This will avoid a lot of problems when using Latin characters such as é, ç, and ô.