LOAD DATA LOCAL INFILE special case

Question

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 1

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 '"';