4

I have a CSV file with fields given as follows.

number1, string1, "string2, string3", number2, string4, "string5, string6"

where number's are numbers and string's are strings. I am trying to read this as follows, I am ignoring first header line

LOAD DATA LOCAL 
  INFILE 'filename.csv' 
  INTO TABLE table_name 
  FIELDS TERMINATED BY ','  
  IGNORE 1 LINES;

But due to embedded commas in some fields, this command is giving causing to overlap the fields. How would I correctly do this in MySQL?

1 Answer 1

8

You just need to add OPTIONALLY ENCLOSED BY '"':

LOAD DATA INFILE 'filename.csv' INTO TABLE table_name 
  FIELDS TERMINATED BY ',' 
  OPTIONALLY ENCLOSED BY '"'
  IGNORE 1 LINES;

Documentation link.

File:

n1,s1,s23,n2,s4,s56
1,string1,"str2,str3",2,string4,"str5, str6"

Table and test:

mysql> create table tbl_name ( 
                               n1 integer, 
                               s1 varchar(10), 
                               s23 varchar(10),
                               n2 integer, 
                               s4 varchar(10), 
                               s56 varchar(10) 
                               );
Query OK, 0 rows affected (0.06 sec)

mysql> LOAD DATA INFILE 'c:\\temp\\data.txt' INTO TABLE tbl_name
    ->   FIELDS TERMINATED BY ','
    ->   OPTIONALLY ENCLOSED BY '"'
    ->   IGNORE 1 LINES;
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
3
  • If I can give chocolates then I would give one to you. Your solution magically worked. Merry Christmas.
    – user9026
    Dec 23, 2015 at 10:02
  • No working when I tried, in double quotes comma takes as separator not as a single value Aug 2, 2018 at 15:14
  • 1
    Took me some time to realize that I also needed to change LINES TERMINATED BY '\n' to LINES TERMINATED BY '\r\n'
    – John T
    Nov 15, 2021 at 0:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.