How to escape commas inside CSV values when importing table to MySQL?

Question

I am trying to import a CSV file into a MySQL database table. The CSV rows look like the following:

"00602"," ","42042","15590","0","0","0","35","9","67","1978","20608","21434","3.1","54700","11384","31.3","30.4","32","18.382809","-67.186759","0","PR","Puerto Rico","U","","787","AGUADA","URB SAN CRISTOBAL","AGUADA","003","72","4","N","0060","","","10380","","Metro","Aguadilla-Isabela-San Sebastián PR","Aguadilla PR MSA","","","","N","0","0","0","0","","0","0","0","0","0","322569","0","","","5456","415","9577","V17136"," "," ","",""
"00602"," ","42042","15590","0","0","0","35","9","67","1978","20608","21434","3.1","54700","11384","31.3","30.4","32","18.382809","-67.186759","0","PR","Puerto Rico","U","","787","AGUADA","COMUNIDAD LAS FLORES","AGUADA","003","72","4","N","0060","","","10380","","Metro","Aguadilla-Isabela-San Sebastián PR","Aguadilla PR MSA","","","","N","0","0","0","0","","0","0","0","0","0","322569","0","","","5456","415","9577","V17136"," "," ","",""

etc...

As you can see, the fields are enclosed in double quotes and separated by commas. However some rows have commas inside the values like so (notice the last value):

"00501"," ","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","40.81518","-73.0455","25","NY","New York","N","","631","HOLTSVILLE","I R S SERVICE CENTER","SUFFOLK","103","36","5","Y","5602","5380","408","35620","35004","Metro","New York-Northern New Jersey-Long Island NY-NJ-PA","New York-Northern New Jersey-Long Island","Nassau-Suffolk, NY PMSA","Northeast","Middle Atlantic","N","7","0","0","0","B","0","0","0","0","0","18640775","2807500","","","0","1","1","V13916","U"," ","New York-Newark-Bridgeport, NY-NJ-CT-PA","Nassau-Suffolk, NY"

This is how my import statement looks like:

LOAD DATA INFILE '/file.csv'
REPLACE INTO TABLE zipcodes 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

But it's not working as expected. For example, the first inserted row has a column with the following value:

Nassau-Suffolk, NY""00501

instead it should have been:

Nassau-Suffolk, NY

and 00501 should have been inserted as the first column value on the next row.

How to adjust my SQL to work right?

Solution

From the MySQL Reference Manual, use the following options instead:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'

Update to match your usage, but the important part is to drop the OPTIONALLY from OPTIONALLY ENCLOSED BY.

Comments

[Sawant]

How do we achieve this in a stored procedure, since LOAD DATA is not allowed in SPs?

[Annie]

Adding the \r in the LINES TERMINATED BY '\r\n' section made it work for me! Thanks!