Import CSV using phpmyadmin

Posted on 2011-10-22
Medium Priority
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 84

Accepted Solution

Dave Baldwin earned 2000 total points
ID: 37011852
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.

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

864 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