33

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

JoSSte
  • 2,953
  • 6
  • 34
  • 54
Andrey
  • 1,629
  • 13
  • 37
  • 65

10 Answers10

39

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.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • 1
    But it cannot be larger than int because max int is 10 digits number i use only 7 digits number. Table is really big more than 128000 records – Andrey Feb 08 '13 at 03:57
  • @AndreyIvanov -- double check your first row (and last). As I mentioned, you should be able to import into another table changing that field to a varchar and see what happens. I suspect another error, but different int column. Let me know. – sgeddes Feb 08 '13 at 03:59
  • Ok will try tomorrow morning and will leth you know. – Andrey Feb 08 '13 at 04:04
  • Change all fields to varchar(50). Throwing error 1261.Row 1 doesn't contain data for all columns.which is does not make sense for me! – Andrey Feb 08 '13 at 05:33
  • @AndreyIvanov -- is your first row blank or null? Look into the parameters of using LOAD DATA -- perhaps adding IGNORE 1 LINES will help if it's always the first line. Here's some more information http://dev.mysql.com/doc/refman/5.0/en/load-data.html – sgeddes Feb 08 '13 at 05:41
  • Problem was because i did not spesify FIELDS TERMINATED BY ','. But right now i haveother problem it doez not want to read my date format. Which format is correct for mysql – Andrey Feb 08 '13 at 06:07
  • Take a look here: http://stackoverflow.com/a/2239127/1073631 -- you need to specify the columns and then format the date column. Best of luck. – sgeddes Feb 08 '13 at 06:11
  • Aktualy problem still exist with correct data types ,howewerif all columns varchar it loads perfectly – Andrey Feb 08 '13 at 06:36
  • Then now you should be able to figure out which columns are of your incorrect data types -- try casting or using regex to see which are invalid. – sgeddes Feb 08 '13 at 06:39
  • I had the same error for MySQL `enum` data type and I didn't found the answer here. So for those who have this error for `enum`, you must be giving your column a value that's probably not poured to the `enum`. – TheCleverIdiot Apr 03 '18 at 04:53
14

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

Nae
  • 14,209
  • 7
  • 52
  • 79
Bluebaron
  • 2,289
  • 2
  • 27
  • 37
8

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.

نرم افزار حضور و غیاب
  • 1,293
  • 2
  • 16
  • 28
7

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.

SergiyTs
  • 71
  • 1
  • 3
  • This was the same issue I had. I was getting truncation "Warning" from MariaDB/HEIDISQL but data looked fine after import. Changing lines terminated to \r\n solved the warning. – f.thorpe Nov 19 '21 at 18:48
  • Bam! You saved me from self-inflicted death while the balance of my mind was disturbed. I was having exactly this issue with a column in which the values were mostly 1's and 0's. Thank you! – naugiedoggie Feb 17 '22 at 02:41
6

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.

Liam Mayfair
  • 493
  • 1
  • 8
  • 8
  • it is post from 2013 :) but thanx :) i think problem was in data format :) but i dont remember – Andrey Oct 02 '18 at 19:31
  • 1
    @Andrey Sorry about the necropost but I thought people might find this useful anyway, as both the question and the answers are still valid and helpful! – Liam Mayfair Oct 02 '18 at 19:33
  • no problem , thanks :) i understand that it can still be useful :) – Andrey Oct 02 '18 at 20:00
4

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.

Celik
  • 2,311
  • 2
  • 32
  • 54
2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Daniel Cruz
  • 21
  • 2
1

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

hriziya
  • 1,116
  • 1
  • 23
  • 41
1

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'.

srivatsan
  • 11
  • 3
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 29 '22 at 00:58
0

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.

Max
  • 2,036
  • 2
  • 18
  • 27