php – How to remove double quotations from inside value when importing csv into mysql table-ThrowExceptions

Exception or error:

I am using the code here to import a csv into a mysql table. It works great and ENCLOSED BY removes any double quotations.

My csv values are like “text text”,123,”text text”,456,”text text”

LOAD DATA LOCAL INFILE "'.$file.'"
INTO TABLE '.$table.'
FIELDS TERMINATED by \',\'
OPTIONALLY ENCLOSED BY \'"\'
ESCAPED BY \'"\'
LINES TERMINATED BY \'\r\n\'
(location,rating,board etc)

I have just found that some values have double quotations, inside the main double quotations.

Like 3rd column here – “text text”,123,”text “Special” text”,456,”text text”

Which imports as – text text,123,text “Special text,456,text text

I have tried the ESCAPED BY as shown but cannot get it to remove the inner double quotations. It is in a wordpress environment but above code does work (apart from my problem).

Any idea help or ideas would be great.

How to solve:

Try to escape the double quotes like this :

LOAD DATA LOCAL INFILE "'.$file.'"
INTO TABLE '.$table.'
FIELDS TERMINATED by \',\'
OPTIONALLY ENCLOSED BY \'"\'
ESCAPED BY \'\'
LINES TERMINATED BY \'\r\n\'
(location,rating,board etc)

Answer´╝Ü

You showed us this line from a CSV file

"text text",123,"text "Special" text",456,"text text"

Unfortunately, that’s not well-formed CSV. " characters inside column values must be escaped. So for that line to be well-formed it must look like one of these.

"text text",123,"text ""Special"" text",456,"text text"
"text text",123,"text \"Special\" text",456,"text text"

If this were my project, I’d go back to where I got this bad CSV file and try to get a good one.

If that’s not possible, and your file doesn’t have any commas embedded in column values, you could try this load command, taking off the OPTIONALLY ENCLOSED BY clause.

LOAD DATA LOCAL INFILE "'.$file.'"
INTO TABLE '.$table.'
FIELDS TERMINATED by \',\'
ESCAPED BY \'"\'
LINES TERMINATED BY \'\r\n\'
(location,rating,board etc)

You’ll then get some string values with " characters at their beginnings and ends. You can clean that up by queries like this

UPDATE table SET field = SUBSTRING(field,2) WHERE field LIKE '"%';
UPDATE table SET field = SUBSTRING(field, 1, LENGTH(field)-1) WHERE field LIKE '%"';

The first query takes off leading ” signs, and the second one takes off trailing ones.

Leave a Reply

Your email address will not be published. Required fields are marked *