1

I have a huge file that I would like to read so I can fill my mysql database. I tried to use the PHPExcel library but I get an error when I want to load my file :

Fatal error: Allowed memory size of 1610612736 bytes exhausted (tried to allocate 22 bytes) in C:\Wamp\www\Classes\PHPExcel\Worksheet.php on line 964

I have already increased the value of the memory_limit in the php.ini file but it's still not enough. My Excel file is more than 60 MB (5 109 719 cells).

Anybody have an idea how to solve this problem ?

Lahiru Jayaratne
  • 1,684
  • 4
  • 31
  • 35
Arun sankar
  • 94
  • 1
  • 2
  • 17
  • Try to set memory limit **ini_set('memory_limit','2048M'); ini_set('max_execution_time', 300); //300 seconds = 5 minutes** inside the function where you reads the excel. `function reader(){ini_set('memory_limit','2048M');ini_set('max_execution_time', 300); //write your logic here }` – Ashu Jha Aug 25 '16 at 10:51
  • Have you used any of the documented methods for reducing memory usage for large files? Cell caching? Chunked loading? – Mark Baker Aug 25 '16 at 12:06

2 Answers2

0

Reading 5 million cells using PHPExcel is a tricky problem. You can try using the cell caching or chunked loading techniques that Mark mentioned. Take a look at the documentation here: https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/04-Configuration-Settings.md

I also tried reading millions of cells with PHPExcel, using these techniques but it then becomes a performance problem (instead of a memory problem). Reading so many cells take forever. So alternatively, you can try using Spout: https://github.com/box/spout. Reading 5 millions cells should only require a few MB of memory and take 20 to 40 minutes.

Adrien
  • 1,929
  • 1
  • 13
  • 23
0

If your don't have some special functions its much better to export your file as CSV and import it directly to MySQL. 5 Million cells are a lot and that would take too long time with PHP.

Importing a csv into mysql via command line

I'm not a big Java fan but for that you get a good lib.

https://poi.apache.org/

POI perform good on big files and is working better than PHP. If you can't convert your file to CSV and read it. You should take a look at POI.

René Höhle
  • 26,716
  • 22
  • 73
  • 82