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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to csv