MySQL: Row N was truncated; a solution

by

You’re importing some data into MySQL and using LOAD DATA INFILE so you can do a SHOW WARNINGS afterwards. Every line of your import file has a warning:

| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns
| Warning | 1262 | Row 2 was truncated; it contained more data than there were input columns
…etc.

The fix is easy, and you’d think of it eventually, but you thought you’d try google instead. So I’m posting this so that when you do there is something to find. There wasn’t much when I looked.

It’s the line endings. MySQL isn’t getting what it expects, so specify the format of the file using LINES TERMINATED BY ‘\r\n’ or whatever is appropriate for you:

‘\r\n’ for files that came from Windows systems
‘\r’ for files from VMS
‘\n’ for every other source.

Hope that helps.

31 thoughts on “MySQL: Row N was truncated; a solution”

  1. I spent too much time trying to find out why MySQL didn’t recognize ‘\n’ as a new line.
    So, I tried your suggestion ‘\r\n’ and it worked.
    Thank you so much!

    huges,
    Carla.

    Reply

  4. you cannot believe the stress i was going through trying to figure out what the problem was, dude you my friend are awesome

    Reply

  9. I am using \r\n also ..but still getting an error

    LOAD DATA LOCAL INFILE ‘C:/abc.csv’ IGNORE
    INTO TABLE abc
    FIELDS TERMINATED BY ‘,’
    lines terminated by ‘\r\n’
    IGNORE 1 LINES;

    311347 row(s) affected,64 warning(s): 1262 row 26 was truncated;it contained more data than there were input columns..
    Can anyone help?

    Reply

    • Hi Manisha,

      It sounds like you have an un-escaped delimiter in your data. Look for a comma in one of the fields. If it is escaped or in quotes you can use the ENCLOSED BY and ESCAPED BY terms to say how.

      Good luck.

      Reply

  13. Hey

    Great post – short and sweet. Right on target.

    This relates to data exported from Salesforce using the DataLoader – and ‘\r\n’ was the format that worked.

    Thanks for your help.

    Tim Bushell

    Reply
  14. Pingback: How to import .txt or .csv file data into MySQL? | rafiq7s
  21. Pingback: How to import .txt or .csv file data into MySQL? | rafiq7s

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.