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?

Improve this question

1 Answer 1

Reset to default
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
Improve this answer
3
  • If I can give chocolates then I would give one to you. Your solution magically worked. Merry Christmas. Commented Dec 23, 2015 at 10:02
  • No working when I tried, in double quotes comma takes as separator not as a single value Commented 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' Commented Nov 15, 2021 at 0:16

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.