Load data infile error : data too short for column

Question

I have a CSV file that I need to upload in MySQL. The table desc is :

website         varchar(500)    
major_cat       varchar(500)   
sub_cat         varchar(500)  
ref             varchar(500)  
commision       varchar(10)  
additional_fee  varchar(5)   
shipping        varchar(5)

Below is a CSV data example :

Amazon,All,All,AmazonAllAll,5%,10,12.36%   
Angoor,All,All,AngoorAllAll,5%,0,12.36%  
Big Basket,All,All,Big BasketAllAll,5%,0,12.36%  
Car Khana,All,All,Car KhanaAllAll,5%,0,12.36%

I tried using the below load data infile query :

load data infile 'AAAAA Products.csv' 
into table products 
fields terminated by ',' 
lines terminated by '\n';

I get the below error

ERROR 1406 (22001): Data too long for column 'shipping' at row 1

Please help me with this. I'm stuck really bad.

Ray Kiddy Ray Kiddy · Accepted Answer · 2014-02-22T08:58:21

This may seem like a strange guess, but I am noticing that you have a space at the beginning of the first line above. This sometimes occurs when a non-visible character is at the end of that line.

When I was on a Mac and had \r\n at the end of line, instead of the expected \n, I would see this.

Look at the actual data in your csv file and not just what you see when you cat out the file. For example, "od -c " will show you if you have non-printing characters at the end of that first line.

Load data infile error : data too short for column

1 Answers