I want to load a local csv with the following columns:
product_id, product_name, aisle_id, department_id
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
create table products (
PRIMARY KEY (product_id)
LOAD DATA LOCAL INFILE '~/my_location/products.csv'
replace INTO TABLE products
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
ERROR 1406 (22001): Data too long for column 'product_name' at row
1147,"Pop Up Notes, 3\"" X 3\""",87,17
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