mysql load data infile it contain more data than there were input column

Question

I'm a new to mysql, I try load csv file to mysql. the csv like:

1,"a,b"
2,bc
3,d

the table like this:

create table test(ind varchar(10),var varchar(20));

when I load this csv file:

load data infile 'test.csv' into table test
fields terminated by ',' ;

I change this the warning:

row 1 was truncated: it contained more data than there were input columns I try this:

load data infile 'test.csv' into table test
fields terminated by ','
optionally enclosed by '"'

it doesn't work.

the common of "a,b" cause this error. but I don't know how to solve this question.

Answer 1

It sounds like maybe LOAD DATA isn't properly picking up on your line breaks. Try adding LINES TERMINATED BY ... to your call:

LOAD DATA INFILE 'test.csv' INTO TABLE test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'         -- use '\n' if on Linux
(ind, var)

With the above call, MySQL should not view the comma inside the first quoted term "a,b" as being a field separator, but rather just part of the text of that column.