[FIXED] Import large csv file using phpMyAdmin

Issue

I have a csv file, a big one, 30 000 rows. I've tried to import it using LOAD file etc.. from the terminal, as I found on google, but it didn't work. It was making the import but my table got to 30 000 rows of NULL cells.

After that I tried phpMyAdmin and there I found out that my csv was too big. I've split it in 5 using CSV Splitter. I've made the import for the first file. Everything went great. Than I tried to import the second one, but I got thos error:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 35 bytes) in C:\xampp\phpMyAdmin\libraries\import\csv.php on line 370

or 1064 error sometimes.

Do you know why and how can I solve it? Thank you.

Solution

Increase your php's memory limit and script time, that's beacause your executing the mysql instruction through the php server.

Check your php.ini file for this vars:

memory_limit
max_execution_time

But anyway I would do it through the mysql client (terminal), check mysql doc

LOAD DATA LOCAL INFILE '/path/to/your/csv/file/csv_file.csv' INTO TABLE database_name.table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Mysql Documentation - Load Data Infile Syntax PHP Documentation - Ini core settings

Answered By - Packet Tracer