phpphpexcelphp-5.6phpexcelreader

Reading large .xls file with PHP


At the moment I am doing a mass interface of files/data and some files are in XLS format, which I need to normalize them into csv (so basically, convert XLS to CSV files)

The problem is that PHPExcel (and similar libraries) load the entire sheet data at once thus exhausting memory.

So far I tried various libraries (in the meantime negotiating to have the data in csv though no luck so far)

I am running my tests on various large file sizes, my memory allocation is set properly before and after my script runs using ini_set etc.

Is there a way that I can read an xls line by line or in chunks (like fgetcsv or fread) please?

I am programming this so it can work with any filesize (even if it takes ages to run) as this is a fully automated system.

PS: I checked this post and various others already Reading an Excel file in PHP


Solution

  • Possible ways...

    1. Get help from other languages. e.g. find a Python excel library and use it. Then call Python from PHP.
    2. Modify the source code of those Excel readers
    3. Use a command line tool to convert excel to csv, e.g. Pandoc maybe, and use the csv in PHP
    4. Since xls file is nothing but a zip file, maybe it can be unzipped and found the values
    5. First decompose one xls into many small xls files via non-PHP solution, e.g. VBA in excel, then read each of them.