MySQL: Row N was truncated; a solution

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.

This entry was posted in Geekery and tagged . Bookmark the permalink.

26 Responses to MySQL: Row N was truncated; a solution

  1. Carla says:

    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.

  2. pierig says:

    Also spent long time for this stupid error. Thanks a lot for posting and explaining so easy.

    Stefan

  3. Wow, I can’t believe how simple that was — here I was beginning to think it was a problem with my source CSV. I love you :P!

  4. obyelect says:

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

  5. Aritra says:

    o thanks a LOHT!!!!

  6. Manu says:

    thanks a lot!!

  7. francois says:

    THANK YOU !
    (and google too)
    πŸ˜‰

  8. TimK says:

    Thank you for this solution I have been trying to fix my CSV’s

  9. Manisha says:

    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?

    • Alan says:

      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.

  10. Chris says:

    thanks πŸ™‚

  11. Yes! This is what I was looking for. The /r/n or /n coming from various clients on various machines. Ugh

  12. Just chiming in to say thanks for posting this! I can now stop hitting my head against the wall.

  13. Tim Bushell says:

    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

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

  15. David says:

    Amazing! Thank you very much! You saved me a lot of time!!!

  16. Tony says:

    Thanks this really helped!

  17. Limaes007 says:

    Thanks !! useful πŸ™‚

  18. Prem says:

    Thanks a lot..It worked… πŸ™‚

  19. Jay says:

    WOW. Thanks!

  20. Salil Athalye says:

    Thanks for explaining the reason for the 1262 error.

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

  22. S FAROOQ says:

    Thanks a lot..It worked… πŸ™‚

  23. Derek says:

    First search result, solution worked. You’re awesome!

  24. Niko says:

    Very usefull even 7 years later, than you very much!!!

Leave a Reply

Your email address will not be published. Required fields are marked *