alanjames.org

Things I thought worth sharing.

Geekery

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.

28 Comments

  1. Carla

    August 20, 2009 at 15:58

    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

    May 10, 2010 at 08:46

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

    Stefan

  3. Stan Schwertly

    February 3, 2011 at 22:26

    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

    August 17, 2011 at 16:20

    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

    April 8, 2012 at 19:24

    o thanks a LOHT!!!!

  6. Manu

    April 12, 2012 at 02:38

    thanks a lot!!

  7. francois

    October 10, 2012 at 21:55

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

  8. TimK

    October 17, 2012 at 14:55

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

  9. Manisha

    November 29, 2012 at 14:44

    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?

    1. Alan

      November 29, 2012 at 15:31

      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.

      1. soundarya

        August 14, 2019 at 19:10

        Thank you so much Alan! Saved my life!!

  10. Chris

    April 26, 2013 at 03:50

    thanks πŸ™‚

  11. B. David Miyares

    May 9, 2013 at 21:06

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

  12. Adrian Holovaty

    May 10, 2013 at 23:29

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

  13. Tim Bushell

    May 18, 2013 at 18:43

    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. How to import .txt or .csv file data into MySQL? | rafiq7s

    May 21, 2013 at 06:46

    […] Relevant post: MySQL: Row N was truncated; a solution […]

  15. David

    May 24, 2013 at 08:19

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

  16. Tony

    August 30, 2013 at 20:42

    Thanks this really helped!

  17. Limaes007

    October 31, 2013 at 14:44

    Thanks !! useful πŸ™‚

  18. Prem

    December 11, 2013 at 07:52

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

  19. Salil Athalye

    January 5, 2015 at 17:38

    Thanks for explaining the reason for the 1262 error.

  20. How to import .txt or .csv file data into MySQL? | rafiq7s

    February 10, 2015 at 04:54

    […] Relevant post: MySQL: Row N was truncated; a solution […]

  21. S FAROOQ

    June 9, 2015 at 11:03

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

  22. Derek

    April 28, 2016 at 23:57

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

  23. Niko

    June 11, 2016 at 10:01

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

  24. Zhichen

    May 24, 2019 at 08:06

    Thank you!It really puzzles me

Leave a Reply to obyelect Cancel

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