sandalian.com


Invalid field count in csv input on line 1

18 July 2009 03:21:27 WIB, under category Technical Stuffs

When importing CSV data into my MySQL database using PHPMyAdmin, I found following error: Invalid field count in csv input on line 1.

It’s odd since I’ve manually count field numbers and compared with column on my CSV files, they’re exactly match.

Then I found something strange. PHPMyAdmin is automatically use semicolon ( ; ) as field delimiter instead of comma ( , ). Quick fix for this problem is to change the delimiter symbol manually.

phpMyAdmin screenshot

In the picture above, I replaced semicolon (rounded with blue circle) with comma and everything is alright.


38 Comments so far..

  1. mh
    03 August 2009, 16:04:45 WIB

    it’s not a bug, it follow old excel csv pattern

  2. jport
    10 August 2009, 13:46:02 WIB

    Thanks! This really helped alot. Spent a good while trying to find problem, and its something as small as this.

    Thanks again

  3. escoret
    24 August 2009, 12:52:18 WIB

    ndal….kowe kie mbahas opo tho?

  4. sandalian
    03 September 2009, 17:11:34 WIB

    @mh
    Thanks for your acknowledgement 😉

    @jport
    nice to hear that 🙂

  5. mobilewap
    05 September 2009, 23:17:10 WIB

    WOW thanks for the info, i uploaded a csv files successfully because of your advise. Great job!

  6. ngupingers
    07 October 2009, 19:13:53 WIB

    sopo yo.admine..salam kenal..
    sopo = siapa
    admine = administratornya

  7. detnot
    17 October 2009, 12:56:43 WIB

    ember jeng

  8. Ayden
    26 November 2009, 15:23:47 WIB

    you’ve got no idea how much you just helped me out right here!

    Thanks a million!

  9. yudhi
    27 November 2009, 07:54:21 WIB

    hehehe, biasanya itu dari mysql lama ke mysql baru.

  10. Gangadhara
    01 December 2009, 15:43:16 WIB

    Nice post, it is working fine.

  11. James Bayley
    03 December 2009, 08:27:43 WIB

    Thank you, thank you, thank you!

    I’d been pulling out my hair for two hours until I stumbled upon your site.

    Comma vs. semicolon… phew – I didn’t spot that. Amazing what grief one pixel’s difference can cause.

    phpMyAdmin can go stuff its semicolon right up its last two syllables of the aforementioned punctuation mark.

    Terima kasih banyak

  12. Alan
    11 January 2010, 13:51:49 WIB

    Thank you. I have been looking all day on the web trying to get an answer. My upload worked fine.

    Cheers,

    Alan

  13. ramon damuag
    25 January 2010, 15:23:24 WIB

    thank you so much ,helped me a lot . .

  14. Nicola
    08 February 2010, 00:21:44 WIB

    Thanks, This had been driving me mad for some time!

  15. MK
    19 February 2010, 23:02:43 WIB

    Wow to think PHP used to not do this and does now.

    This is stupid and the sheer number of posts i have seen of users with this problem i can’t believe they did soemthing so stupid just to maintain the old excel standard, clearly CSV stands for COMMA separated value.

  16. Outdoor lanterns
    29 April 2010, 11:17:03 WIB

    this just saved me a whole bunch of time

  17. sunny
    19 May 2010, 22:59:56 WIB

    thanks, it saved my time.

    Sunny.

  18. alay
    02 June 2010, 14:32:07 WIB

    SESAT LOhh

  19. Floris
    12 June 2010, 15:43:25 WIB

    In my CSV file the fields were surrounded by ” (double quotes).
    Please be sure there are no double quotes INSIDE THE FIELDS. Open your CSV file in Excel or scalc (openoffice suite):
    – In scalc go to Edit > Find /Replace
    – In Excel follow this steps: http://support.microsoft.com/kb/288291

    Replace all the ” (double quotes) with ‘ (single quotes).

    Another problem was with the DATETIME fields in MYSQL. In scalc select them all and right-click on them > Format Cells > and put “JJ-MM-DD UU:MM:SS” in the Format code box.

    I’m only allowed to have in MYSQL first the year, then the month and then the day. For example “2009-12-29”.

    After following these steps it worked for me.

    And BTW, if there are still problems, open your CSV with Notepad or another text editor and check if every line has the right count of fields.

  20. Vignesh
    24 June 2010, 02:06:32 WIB

    Thanks a lot, saved a lot of time

  21. Lindsey
    07 July 2010, 00:58:47 WIB

    Wow, thanks for this… I was going insane 🙂 x

  22. Juan
    21 September 2010, 00:50:52 WIB

    Thank you very much, sooo simple!

  23. Vivek
    14 October 2010, 12:49:51 WIB

    Hi,

    its relay Great!

    its helped me alot.

    really Thanks….
    Thanks….
    Thanks….
    Thanks….
    Thanks….
    Thanks….

    vivek
    CHPL pune

  24. Ali
    22 October 2010, 20:10:53 WIB

    i have same problem but still unable to solve it as per your above instructions

  25. M
    22 October 2010, 21:31:14 WIB

    I have funded best solution

    First of all open CSV file in Microsoft Excel and save it again with new name then

    To solve this I had to do the following:

    Import
    Browse for your csv file.
    Select CSV using LOAD DATA (rather than just CSV)
    Change “Fields terminated by” from “;” to “,”
    Make sure “Use LOCAL keyword” is selected.
    Click “Go”

  26. Website Design Sheffield
    17 November 2010, 18:20:50 WIB

    Cheers for this, really saved my ass

  27. Frank
    30 November 2010, 17:07:41 WIB

    The above problem is probably due to the Windows default setting in Regional Settings (Control Panel) for the CSV file separator. It seems the USA and UK use different default values.

  28. Phil
    17 December 2010, 19:01:04 WIB

    Excellent tip, you solved my problem instantly with CSV imports!

  29. Gilbert Midonnet
    11 January 2011, 21:23:56 WIB

    Another useful trick, when you’re having problems importing an Access table, is to open up the file in TextPad (or some other editor) and examine your data. You’ll probably find many errors. For instance if you have ” in your text field you’ll have to change it something else. I find that Access search and replace can be a little tempermental, so simply telling Access to replace the ” with a ^ doesn’t always work as expected.

  30. Maggie
    19 January 2011, 15:46:52 WIB

    Cool Stuff!!!

  31. Adam
    08 March 2011, 18:35:50 WIB

    Great job, I got this when trying to upload a csv database after editing it, I thought I broke it lol.

    Thanks!

  32. BGJ
    26 May 2011, 02:41:35 WIB

    Floris: Thanks!

  33. Scott
    15 July 2011, 08:20:57 WIB

    I’ve tried everything you said but only the first row out of 142 rows gets inserted. I also have this issue. I have 8 columns A thru H.
    In the DB the first field is Product_ID and its set to auto increment. Even only after only one row being inserted the data in the CSV file does not transfer over to the same DB fields as they should (out of sync). Very frustrated.

  34. golavskiydm
    18 August 2011, 17:07:11 WIB

    Thank’s a lot for decision of the problem. It’s work!

  35. meksi
    17 September 2011, 21:15:17 WIB

    Thanks a billion!

  36. Adan
    07 February 2012, 05:52:17 WIB

    Thanks! I also had trouble with uploading a CSV created in Excel, but removing quotation mark in ‘Fields enclosed by’ field I was able to fix it.

    Thanks again!

  37. Mike
    09 February 2012, 05:32:31 WIB

    VERY helpful thanks. I kept getting errors! This worked perfectly.

    Thanks again,
    Mike

  38. Thank
    12 February 2012, 23:17:31 WIB

    oh .. thank you so much ^^” .. i try to solve this problem by myself very long time

RSS for comments TrackBack URL

Leave a comment or two

Allowed tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>