MySQL Import CSV with Multiple Field Separators

Question

I'm trying to import this CSV file into MySQL that appears to be optionally enclosed by more than one character. Unfortunately, MySQL only supports one character as a field separator.

I am stuck doing this entire process in SQL as it is part of a larger program- so scripting is out of the question.

Example of the data:

 reportdata, commission, total, chargeback, company
 ",123,""116ドル.00 "",""604ドル.00 "",""(88ドル.00)"", foo

I'm stumped apart from attempting to do a bunch of string operations on a temp table.

Any ideas?

Answer 1

Since you are not allowed to script but SQL try the following:

CREATE TABLE importweirddata( txt varchar(255) ) ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'whatever_importfile.txt'
INTO TABLE importweirddata
LINES TERMINATED BY '\r\n';
UPDATE importweirddata SET txt = REPLACE(txt,'\"\"','\"');
SELECT * FROM importweirddata INTO 'improved_importfile.txt';
DROP TABLE importweirddata;

There you go, another text file that has the doubled double quotes stripped.

If you want all doubled double quotes replaced with single quotes, do this UPDATE:

CREATE TABLE importweirddata( txt varchar(255) ) ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'whatever_importfile.txt'
INTO TABLE importweirddata
LINES TERMINATED BY '\r\n';
UPDATE importweirddata SET txt = REPLACE(txt,'\"\"','\'');
SELECT * FROM importweirddata INTO 'improved_importfile.txt';
DROP TABLE importweirddata;

Now, go import that file (improved_importfile.txt) and have fun with it.

Give it a Try !!!

• Kevin: That works! I had to make some slight changes but the method is correct. The modifications were just made were minimal- just enough to jive with the remainder of the application. Thank you very much!