CODEWITHSUNDEEP
mysqlcsv
pang2016
pang2016

Reputation: 539

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

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.

Upvotes: 8

Views: 15120

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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.

Upvotes: 24

Related Questions