View Full Version : Load Data - CSV

01-03-2012, 08:24 PM
Hi all and happy new year...hope you can help with my csv issue.

I'm trying to load some data into my db using a csv file, everything is fine until it come across a string that has "quotation marks" around it and messes up the table row.

Any ideas how to escape it?

Example CSV Data

"1","Joe","Bloggs",""16 AppleTree Drive""

Code used for importing data

LOAD DATA LOCAL INFILE '$csv' REPLACE INTO TABLE loader fields terminated by ',' enclosed by '\"' lines terminated by '\r\n' IGNORE 1 LINES ($col_names)

Thanks for your help

01-03-2012, 10:05 PM
This isn't a PHP issue, its a SQL import call. There should be an escape option of \ which is default in PHP csv handling and I would presume SQL as well.
The issue you will have is that you don't have it escaped. Without escaping those " in there, you cannot expect that the csv can be properly interpreted.
So, I'd say there are really two options. The first is to drop the enclosed by option, and insert the record with " surrounding the entry. A temporary table would suffice, and then use a SUBSTRING() call to trim them off and insert into the correct field.
Or, use PHP to read the data, strip out the surrounding "'s or escape them where needed, and then insert that into the database.

01-04-2012, 02:41 AM
Thanks for the reply, it's greatly appreciated.