0

I want to load a local csv with the following columns:

product_id, product_name, aisle_id, department_id

Typical data in the table looks like this:

72,Organic Honeycrisp Apples,100,21

73,Jasmine Tea Unfiltered Ginger Ale,77,7

74,Artisan Chick'n & Apple Sausage,14,20

75,"Hemp Protein, Organic",65,11

76,Spinach Basil Garlic Linguini,12,9

77,Coconut Chocolate Chip Energy Bar,3,19

The table has been created with the following query:

create table products (  
product_id int,
  product_name varchar(50),
  aisle_id int,
  department_id int,
  PRIMARY KEY (product_id)
);

I am trying to load data into this table with the following query:

LOAD DATA LOCAL INFILE '~/my_location/products.csv' 
replace INTO TABLE products 
FIELDS 
  TERMINATED BY ',' 
  OPTIONALLY ENCLOSED BY '"' 
  ESCAPED BY '\\' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;

However, I keep getting this error:

ERROR 1406 (22001): Data too long for column 'product_name' at row 1147

I checked the row 1147 in the csv file, it reads:

1147,"Pop Up Notes, 3\"" X 3\""",87,17

In other words, the escaping of quotations within the product_name field is not working. Any idea what do I need to do to fix this? Thanks.

hnovice
  • 43
  • 5

1 Answers1

0

It looks like the quotes are escaped by a backslash and a quote, so the ESCAPED BY should be '\\"'

However, I'm not sure that MySQL allows a multi-character ESCAPED BY. You may find that you need to preprocess the file and convert the \"s to either \ or ".

  • Thanks. MySQL does not allow muti-character `ESCAPED BY`. I preprocessed the file using `sed -i -e 's/\\""/\\"/g' ~/git/my_location/products.csv`, which replaced all the `\""` by `\"`. Exactly like you said. Thanks again. – hnovice Jun 12 '17 at 00:01