1

Im trying to implement Converting single sheet in an XLS file to CSV with PHPExcel - Memory exhausted but im still running into memory problems.

Think @BT643 code - which I used - includes most of the golden rules explained by @Mark Baker in various posts compiled in his answer (phpexcel Allowed memory size of 134217728 bytes exhausted).

My excel file has 61 columns and about 8000 lines, so some half a million cells. Size 5561KB. I would not expect it require reading "chunks".

What am I missing?

Here's the code I'm using:

include 'Classes/PHPExcel.php';
include 'Classes/PHPExcel/IOFactory.php';

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '2GB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

$reader = PHPExcel_IOFactory::createReader('Excel5');
$reader->setReadDataOnly(true);
$reader->setLoadSheetsOnly('mytab');

$filename = 'C:/xampp/htdocs/mycode/myfile.xls';

$excel = $reader->load($filename);

$filename_fixed='myfile.csv';
$writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
$writer->save($filename_fixed);
Community
  • 1
  • 1
  • 1
    You're only allowing 128MB of RAM for this document. You're probably going to need much more. – Brad Dec 16 '13 at 23:30
  • @Brad. `ini_set("memory_limit","256M");` + `ini_set('max_execution_time', 300);` solved the problem! tks a bunch! –  Dec 17 '13 at 08:37

1 Answers1

0

Setting memoryCacheSize to 2GB is not a good idea. This means you are telling PHPExcel to use up to 2GB of server memory (probably above the limit of permitted php memory) before it should consider using phptemp disk for caching data. If you had 2GB of memory availale, you probably wouldn't need caching: the value of memoryCacheSize should be set to the maximum that PHPExcel should be permitted to use of your available php memory before it should switch to using phptemp disk caching

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • tks! indeed if I comment the three `cashMethod` lines my new code still works. However I still need some 40 sec to run it (`ini_set('max_execution_time', 40);`). I wonder if there's a faster way... –  Dec 17 '13 at 16:03
  • Any use of caching has an adverse affect on speed, and memory/performance ratio will vary depending on your spreadsheets; experiment with some of the other cache methods to see which gives you the best speed while still working within your available memory – Mark Baker Dec 17 '13 at 16:12