CSV import problem; on different servers import throws error or succeeds

Question

Hi folks,

We recently migrated a site from Superb.net to MediaTemple. Part of the upkeep of this site is a 60000+ record export (in 3 CSVs) from Raiser's Edge which I import into mySQL.

The tables retained the same schema before and after the move. This week when I went to do my import I found that each of the CSVs caused an invalid field count error thousands of rows into the data. In one case the error occurs more than 12000 rows into the data.

I examined three rows for each error, before the line #, after, and the row itself. They look fine. They have no quoted values, no bad characters, nothing I can see wrong. They have the correct number of fields. There are no quoted values in the whole file. Verified this in UltraEdit text editor. There are no commas in the data.

After trying the import using mysqlimport and then a LOAD DATA INFILE query and finding both to be disallowed I contacted MediaTemple who said sorry both of those were not available to me. I could upgrade mysql on our dedicated virtual server but then any problems are not theirs. MediaTemple says this is a version issue with mySQL.

So on a whim I took the CSV and tried the import on the old server and lo and behold it rolled in fine. I don't know the mySQL version on the old server (Superb) but they run phpMyAdmin 2.11.8.1.

MediaTemple is running mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (i686) using readline 5.0 and phpMyAdmin 2.8.2.4.

Does this ring any bells? Make any sense to anyone? Any advice?

Thank you,

JG

Answer 1

This may not fall in the category 'answer', but here's my 2 cents. To be honest, I don't recognize your problem and I can't think of any sensible explanation.

But, the time it took to troubleshoot and to type up this post, wouldn't that be better spent writing some sort of shell/perl/php script that does the import for you? Of course, it wouldn't be blazing fast, but hey, we're talking 60K records here, that shouldn't take more than a couple of minutes tops.

Answer 2

In the end I felt pretty dim for not having realized I should just install the current version of phpMyAdmin. It was in the end clearly a version bug as the upgrade solved the import problem with no further contortions.