6

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?

RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
Kevin
  • 163
  • 1
  • 6

1 Answers1

3

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 !!!

RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
  • 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! – Kevin Sep 12 '12 at 16:42