How to import CSV into mysql if values contains comma

Question

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;

user1864610 · Accepted Answer · 2015-07-23 23:21:24Z

4

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;

answered Jul 23, 2015 at 23:21

user1864610

Sign up to request clarification or add additional context in comments.

2 Comments

Dmytro Pastovenskyi Over a year ago

It did not work for me, however once I've changed FIELDS TERMINATED BY ',' to FIELDS TERMINATED BY ', ' - it started to work. Do you know why it is so?

Steve Seeger Over a year ago

That would work only if the CSV is formatted so there is a space after a comma between separations eg "1", "2", "3" would work but not "1","2","3". The first case solves the issue because the parser is looking to break up fields when it sees ...", "... which isn't inside of your field that contains commas with a space after it

Collectives™ on Stack Overflow

How to import CSV into mysql if values contains comma

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related