CODEWITHSUNDEEP
mysqlcsv
swt83
swt83

Reputation: 2011

MySQL "LOAD DATA INFILE" and Missing Double Quotes

I'm trying to load a CSV into MySQL using the LOAD DATA INFILE technique. It's working fine, but I have a problem where some columns use double quotes and some do not.

Example:

something,123,something,"Bauer, Jack",123,something

What happens is the commas inside the quotes break the import, so my data is all jacked up at the end. Not sure how to get the import to escape commas inside the double quotes.

mysql --user=<USER> --password=<PASS> -e "LOAD DATA INFILE '<FILENAME>' INTO TABLE <TABLENAME> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (col1, col2, col3, ...)" <DATABASE>

Upvotes: 28

Views: 34307

Answers (2)

Andreas Krueger
Andreas Krueger

Reputation: 1507

You need to execute the statement LOAD DATA INFILE with the additional option

FIELDS OPTIONALLY ENCLOSED BY '"'

Thus the whole statement becoming

    LOAD DATA INFILE '<FILENAME>' INTO TABLE <TABLENAME>
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(col1, col2, col3, ...)

For further readings, please consult the excellent MySQL Reference Manual e.g. for MySQL 5.1 GA.

Upvotes: 57

Bob
Bob

Reputation: 533

Hopefully you have figured out what to do by now, but If you haven't hopefully this will help you out.

I have had trouble in Excel 2007 exporting to a customized CSV file. I found that you can change the fields terminator here: http://www.tek-tips.com/viewthread.cfm?qid=1635599&page=15

I prefer to use the pipe | character as it is uncommon, and a little more flexible when dealing with inch", foot" measurements.

Upvotes: 0

Related Questions