Import CSV using phpmyadmin

Posted on 2011-10-22
Last Modified: 2012-05-12
I'm trying to import a csv file into a mysql database using the import functionality of phpmyadmin. I've attached the csv file and a screenshot of the structure of the table. entities table structure load-entities.csv

I always select "," for "Fields terminated by:" and blank for "Fields enclosed by:". I leave "Lines terminated by:" as "auto" but I'm not sure if this is correct since the most I can get is 1 line to be imported.

If I use "CSV" as the "Format of imported file" option, I get an error message:
"Invalid field count in CSV input on line 1."

If I use "CSV using Load Data" as the "Format of imported file" option, I get a message that the import is successful, but only 1 line of data is imported and the fields are screwed up.

I also tried removing the field (column) names from the import file and putting them in the "Column names" box in the import screen, but that didn't work either.

Any suggestions on how I can get this to work would be much appreciated.
Question by:opike
    1 Comment
    LVL 82

    Accepted Solution

    Your file has a BOM for Unicode/UTF-8 as the first two bytes of the file which may throw the import function off it is not recognized.  Select UTF-8 as the character set.  ',' for "Fields terminated by:" is good but you should probably specify double quotes as for "Fields enclosed by:" because that is what you will get from something like Excel.  

    If 'country_id' is a numeric column in your database, you will probably have to eliminate the header row because the word 'country_id' is not a number.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now