CODEWITHSUNDEEP
mysqlcsvimport
Dmytro Pastovenskyi
Dmytro Pastovenskyi

Reputation: 5429

How to import CSV into mysql if values contains comma

I've a CSV file where values may have comma as part of value

"09200, France, Paris", "Tower ""Olivia"""
"09200, Spain, Barselona", Shop - perfect

However once I import data it breaks value on 4 columns (based on number of comma in row). What do I do wrong? Please see my import command below.

LOAD DATA LOCAL INFILE '~/Downloads/file.csv' INTO TABLE my_table CHARACTER SET utf8 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@col1,@col2) set address=@col1,name=@col2;

Upvotes: 3

Views: 4662

Answers (1)

user1864610
user1864610

Reputation:

Add an ENCLOSED BY clause to your query:

LOAD DATA LOCAL INFILE '~/Downloads/file.csv' 
  INTO TABLE my_table 
  CHARACTER SET utf8 
  FIELDS TERMINATED BY ',' 
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  (@col1,@col2) set address=@col1,name=@col2;

Upvotes: 4

Related Questions