7

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.

fedorqui
  • 275,237
  • 103
  • 548
  • 598
duracell
  • 99
  • 1
  • 1
  • 6

2 Answers2

26

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

Packet Tracer
  • 3,884
  • 4
  • 26
  • 36
  • Thank you for advice. I've done this but still the same problem. Any other ideas? – duracell Jul 11 '12 at 10:33
  • 1
    From 128 I changed to 512 and than 1024 ... And from 30 to 60. – duracell Jul 11 '12 at 10:54
  • Either I don't know how to do it, or it doesn't work... Anyway, thank you for trying. – duracell Jul 12 '12 at 09:20
  • then install mysqlworkbench and use this instruction http://stackoverflow.com/questions/11429827/how-to-import-a-csv-file-into-mysql-workbench/11429986#11429986 – Packet Tracer Jul 12 '12 at 13:32
  • 2
    For people who are not used to using MySql from the command-line, make sure you log in with a user (instead of merely starting mysql.exe). e.g.: mysql -u root -p yourpassword – Fabien Snauwaert Sep 11 '14 at 06:16
  • 1
    In my case I did it via command line and it was super fast. Thanks @PacketTracer for your suggestion. – spacebiker Sep 27 '16 at 15:54
1

I got this problem when trying to import a large file using phpMyAdmin, and am also unable to use the command in the version which I am using. To solve this I used this CSV editor and split the file into smaller files. Worked fine when I imported each chunk separately.

David
  • 136
  • 2
  • 11