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?
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With