Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
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
    
Thank you accepting my answer, and Welcome to the DBA StackExchange. –  RolandoMySQLDBA Sep 12 '12 at 16:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.