How to fix Fatal Error: Memory Limit Exhausted while reading XLSX file of 5MB or High

[raorafique]

Hi,

I am facing this problem while reading an XLSX files having 40,000 plus rows and 7 columns having 5MB+ file size.

The error description is given here:

Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 32 bytes) in

Can you please suggest me a solution to this problem?

Thanking you in anticipation.

Best Regards,
Rao Rafique.

[Atli]

Hey.

There are two solutions to this:

1. Increase PHP's memory limit to accommodate the memory required to read in the whole file by setting the memory_limit configuration directive. (And note that the required memory size may well far exceed the size of the input data, depending on what's happening behind the scenes of whatever code is reading it.)
   
   
2. Split the input file into smaller pieces. Depending on the input file, it may be possible to read the file in smaller chunks, thus avoiding having it all in memory at the same time. For instance, by using fopen() plus fgets() instead of file() or file_get_contents(). (Though this, specifically, applies more to normal text files than XLSX files.) - Otherwise you'll need to split the files up outside of PHP.

[adrilo]

I assumed that you used an open-source library like PHPExcel or SimpleXlsx. Those libraries were not designed to scale and therefore can't handle large files without consuming all the available memory or taking forever. So increasing memory_limit usually does not work (at best, that's a temporary fix).
I can suggest you to take a look at Spout: https://github.com/box/spout. It is able to read XLSX files of any size, pretty fast and does not require more than 10mb of memory.

I've used it to read multi millions rows files, and it works like a charm!