How to import CSV to phpMyAdmin?

Question

I'm trying to import a 300 000 line CSV file to MySQL with phpMyAdmin 2.8.0.1. File size is 8 MB after gzipping.

I manage to import about 800 - 2000 lines and after that phpmyadmin throws an "invalid field count in csv" error.

If I delete these lines from the beginning of csv and try import, phpmyadmin manages to import that same line which an error was thrown before.

Maybe this is related to php settings? Which values should i change?

score 1 · Answer 1 · answered Oct 23 '13 at 12:12

1

Use the LOAD INFILE function instead of phpMyAdmin - it manages also bigger CSV files. (Have made it with about 350.000 lines without problems.)

PHP Example:

$sql = "LOAD DATA LOCAL
INFILE 'test.csv'
INTO TABLE my_table
FIELDS
       TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '\"';";
$insert = mysql_query($sql);

answered Oct 23 '13 at 12:12

Petra

565
1
7
20

For me LOAD INFILE gets only the first line. I set all parameters according to my CSV. – resu1 Oct 24 '13 at 05:56
By the way, phpMyAdmin also supports LOAD DATA (it's one of the dropdown choices). – Isaac Bennetch Oct 25 '13 at 20:30
An example doing so from the command-line directly: http://stackoverflow.com/questions/11430223/import-large-csv-file-using-phpmyadmin/11430498#11430498 – Fabien Snauwaert Sep 11 '14 at 06:17

score 1 · Answer 2 · answered Oct 23 '13 at 12:58

1

it may possible that your php.ini have default settings like this

post_max_size = 8M
upload_max_filesize = 2M

change them to

post_max_size = 20M
upload_max_filesize = 20M

and restart your services.

answered Oct 23 '13 at 12:58

Ram Sharma

8,676
7
43
56

these are already at 20M. However phpmyadmin says max: 10,240 KB – resu1 Oct 23 '13 at 13:24
I feel, you are in a shared hosting and do not have root access or access to your php.ini configuration file, then the other way is to create a .htaccess file in your application root and add the following line php_value upload_max_filesize 20M php_value post_max_size 20M – Ram Sharma Oct 23 '13 at 13:30

score 0 · Answer 3 · answered Oct 25 '13 at 20:33

0

First thing I'd suggest trying is uploading the uncompressed CSV file and see how that goes. You may indeed be hitting a PHP resource limit and not needing to uncompress the file first might help.

answered Oct 25 '13 at 20:33

Isaac Bennetch

11,830
2
32
43

How to import CSV to phpMyAdmin?

3 Answers3