MySQL import in phpmyadmin (CSV) chokes on quotes

Question

I am trying to import a .csv file into a MySQL table via phpMyAdmin.

The .csv file is separated by pipes, formated like this:

data|d'ata|d'a"ta|dat"a|
data|"da"ta|data|da't'a|
dat'a|data|da"ta"|da'ta|

The data contains quotes. I have no control over the format in which I recieve the data -- it is generated by a third party.

The problem comes when there is a | followed by a double quote. I always get an "invalid field count in CSV input on line N" error.

I am uploading the file from the import page, using Latin1, CSV, terminated by |, separated by ".

I would like to just change the "enclosed by" character, but I keep getting "Invalid parameter for CSV import: Fields enclosed by". I have tried various characters with no success.

How can I tell MySQL to accept this format in phpMyAdmin?

Setting up these tables is the first step in writing a program that will use uploaded gzipped .csv files to maintain the catalog of an e-commerce site.

Answer 1

you could modify the csv files by adding a \ in front of every ' right?

Answer 2

Have you tried blanking the boxes that read "Fields enclosed by" and "Fields escaped by"? I have not used phpMyAdmin, but Google suggests others have had success with this method.

Answer 3

You might consider just writing your own LOAD DATA INFILE query, seems like you'll need one anyway since this process will be part of an application at some point.

Answer 4

I found a hack that works -- I use the $ as the "enclosed by" character and all is well. Since this is for a European site, I know that they'll never use it in the table content.

Answer 5

Dear,

you can use http://dbTube.org. It is a native excel importer for the PHP, mySQL enviroment.