error 1265. Data truncated for column when trying to load data from txt file

Question

I have table in mysql table table looks like

create table Pickup ( PickupID int not null, ClientID int not null, PickupDate date not null, PickupProxy  varchar (40) , PickupHispanic bit default 0, EthnCode varchar(2), CategCode varchar (2) not null, AgencyID int(3) not null,  Primary Key (PickupID), FOREIGN KEY (CategCode) REFERENCES Category(CategCode), FOREIGN KEY (AgencyID) REFERENCES Agency(AgencyID), FOREIGN KEY (ClientID) REFERENCES Clients (ClientID), FOREIGN KEY (EthnCode) REFERENCES Ethnicity (EthnCode) );  sample data from my txt file  1065535,7709,1/1/2006,,0,,SR,6 1065536,7198,1/1/2006,,0,,SR,7 1065537,11641,1/1/2006,,0,W,SR,24 1065538,9805,1/1/2006,,0,N,SR,17 1065539,7709,2/1/2006,,0,,SR,6 1065540,7198,2/1/2006,,0,,SR,7 1065541,11641,2/1/2006,,0,W,SR,24 

when I am trying to submit it by using

LOAD DATA INFILE 'Pickup_withoutproxy2.txt' INTO TABLE pickup; 

it throws error

Error Code: 1265. Data truncated for column 'PickupID' at row 1

I am using MySQL 5.2

Answer 1

This error means that at least one row in your Pickup_withoutproxy2.txt file has a value in its first column that is larger than an int (your PickupId field).

An Int can only accept values between -2147483648 to 2147483647.

Review your data to see what's going on. You could try to load it into a temp table with a varchar data type if your txt file is extremely large and difficult to see. Easy enough to check for an int once loaded in the database.

Good luck.