hnovice hnovice -4 years ago 44
MySQL Question

How to escape quotations while loading from local csv in mysql database

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.

Answer Source

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 ".

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download