Yi Zhao Yi Zhao - 2 months ago 16
MySQL Question

LOAD DATA LOCAL INFILE special case

I have a CSV file, which contains something like the following:

NEW YORK, NYC
LOS ANGELES, LA


However, there is a special case which is

WASHINGTON,DC, DC


Because there are two comma in the washington case, I cannot load the file to DB properly since the table only has two columns.
I don't want to use java code to check each row contains more than one comma and edit the washington row to be "WASHINGTON, DC", DC.

I am wondering is there a way to rewrite my old LOAD DATA query to handle this special case?

Current incorrect result:

+--------+--------------+
| value | mapped_value |
+--------+--------------+
| NEW YORK | NYC |
| LOS ANGELES | LAX |
| WASHINGTON | DC |
+--------+--------------+


The expected result should be look like this:

+--------+--------------+
| value | mapped_value |
+--------+--------------+
| NEW YORK | NYC |
| LOS ANGELES | LAX |
| WASHINGTON,DC | DC |
+--------+--------------+


Table Query:

CREATE TABLE city_map (value varchar(255) , mapped_value varchar(255));


My current "LOAD DATA LOCAL INFILE" query is like following:

LOAD DATA LOCAL INFILE '/tmp/city.csv' INTO TABLE city_map FIELDS\n TERMINATED BY ',';

Answer

Your CSV should also have a delimiter like double quote " to enclose your fields. A separator is not enough in a CSV.

If you open your CSV with a text editor you should see them, otherwise your CSV is not valid as you have more commas than needed.

LOAD DATA LOCAL INFILE '/tmp/city.csv' INTO TABLE city_map FIELDS TERMINATED BY ',' ENCLOSED BY '"';