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

When I had this issue, I was trying to update the enum fields by adding new value types, but the last enum value I thought was the last wasn't. For example,I thought i had These in my enum values: 'NEW_VIP','NEW_FREE','RESULT','UPDATE'

So I was trying to update by doing:

ALTER TABLE notifications MODIFY COLUMN type ENUM('NEW_VIP','NEW_FREE','RESULT','UPDATE','RESULT_BASE');

Meanwhile, what I actually had on the table was: 'NEW_VIP','NEW_FREE','RESULT','UPDATE','NEW_MESSAGE'

So the moment I checked the table and realized it, I proceeded to update thus:

ALTER TABLE notifications MODIFY COLUMN type ENUM('NEW_VIP','NEW_FREE','RESULT','UPDATE','NEW_MESSAGE', 'RESULT_BASE'); where RESULT_BASE is the new field i wanted to include and everything worked fine hence

Answer 2

I was facing the same issue, while importing csv files into mysql xampp. I have used LINES TERMINATED BY '\r' instead of LINES TERMINATED BY '\n' and LINES TERMINATED BY '\r\n'.

Answer 3

I had the same problem, my mistake was that I was trying to load a value that I hadn't defined previously to an ENUM().

For example, ENUM('sell','lend') and I was trying to load the value return to that column. I needed to load it, so I added it to the ENUM and load it again.

Answer 4

The reason is that mysql expecting end of the row symbol in the text file after last specified column, and this symbol is char(10) or '\n'. Depends on operation system where text file created or if you created your text file yourself, it can be other combination (Windows uses '\r\n' (chr(13)+chr(10)) as rows separator). Thus, if you use Windows generated text file, add following suffix to your LOAD command: “ LINES TERMINATED BY '\r\n' ”. Otherwise, check how rows are separated in your text file. On default mysql expecting char(10) as rows separator.

Answer 5

I have seen the same warning when my data has extra space, tabs, newlines or other characters in my column which is decimal(10,2) to solve that, I had to remove those characters from value.

here is how I handled it.

LOAD DATA LOCAL INFILE 'c:/Users/Hitesh/Downloads/InventoryMasterReportHitesh.csv' 
INTO TABLE stores_inventory_tmp 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@col1, @col2, @col3, @col4, @col5)
SET sku = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(@col1,'\t',''), '$',''), '\r', ''), '\n', ''))
, product_name = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(@col2,'\t',''), '$',''), '\r', ''), '\n', ''))
, department_number = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(@col3,'\t',''), '$',''), '\r', ''), '\n', ''))
, department_name = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(@col4,'\t',''), '$',''), '\r', ''), '\n', ''))
, price = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(@col5,'\t',''), '$',''), '\r', ''), '\n', ''))
;

I've got that hint from this answer

Answer 6

I had same problem. I wanted to edit ENUM values in table structure. Problem was because of rows that was saved before and new ENUM values doesn't contain saved values.

Solution was updating old saved rows in MySql table.

Answer 7

You're missing FIELDS TERMINATED BY ',' and it's assuming you're delimiting by tabs by default.

Answer 8

I had this issue when trying to convert an existing varchar column to enum. For me the issue was that there were existing values for that column that were not part of the enum's list of accepted values. So if your enum will only allow values, say ('dog', 'cat') but there is a row with bird in your table, the MODIFY COLUMN will fail with this error.

Answer 9

I have met this problem with a column that has ENUM values('0','1').
When I was trying to save a new record, I was assigning value 0 for the ENUM variable.

For the solution: I have changed ENUM variable value from 0 to 1, and 1 to 2.

Answer 10

This error can also be the result of not having the line,

FIELDS SPECIFIED BY ','

(if you're using commas to separate the fields) in your MySQL syntax, as described in this page of the MySQL docs.

Answer 11

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.