Invalid field count in csv input on line 1

July 18, 2009 | 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.

21 comments

This post has 21 comments, incredible!

  1. mh 03/08/2009 at 16:04

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

  2. jport 10/08/2009 at 13:46

    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/08/2009 at 12:52

    ndal….kowe kie mbahas opo tho?

  4. sandalian 03/09/2009 at 17:11

    @mh
    Thanks for your acknowledgement ;)

    @jport
    nice to hear that :)

  5. mobilewap 05/09/2009 at 23:17

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

  6. ngupingers 07/10/2009 at 19:13

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

  7. detnot 17/10/2009 at 12:56

    ember jeng

  8. Ayden 26/11/2009 at 15:23

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

    Thanks a million!

  9. yudhi 27/11/2009 at 07:54

    hehehe, biasanya itu dari mysql lama ke mysql baru.

  10. Gangadhara 01/12/2009 at 15:43

    Nice post, it is working fine.

  11. James Bayley 03/12/2009 at 08:27

    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/01/2010 at 13:51

    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/01/2010 at 15:23

    thank you so much ,helped me a lot . .

  14. Nicola 08/02/2010 at 00:21

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

  15. MK 19/02/2010 at 23:02

    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/04/2010 at 11:17

    this just saved me a whole bunch of time

  17. sunny 19/05/2010 at 22:59

    thanks, it saved my time.

    Sunny.

  18. alay 02/06/2010 at 14:32

    SESAT LOhh

  19. Floris 12/06/2010 at 15:43

    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/06/2010 at 02:06

    Thanks a lot, saved a lot of time

  21. Lindsey 07/07/2010 at 00:58

    Wow, thanks for this… I was going insane :-) x

 

Leave a comment

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