Huge memory usage

Mar 23, 2010 at 5:45 PM
Edited Mar 23, 2010 at 6:01 PM

Hi,

 

What am i facing with:

 

CSV file 65536 rows, F (6) columns

Script run time approx. 75 sec, memory used 550MB

 

XLSX file 96000+ rows G (7) columns

Script run time approx. 130+ sec memory used 1.2GB+

 

If i upload same XLSX file, memory consumption takes all of 1.5GB available memory and 1GB swap space,

In most cases second upload kills apache/system (hangout).

 

What i want to do is read CSV/EXCEL and put it into database.

 

Here is code, mostly from manual.

 

		
$objReader = PHPExcel_IOFactory::createReader("CSV");
$objReader->setDelimiter(';');
$objPHPExcel = $objReader->load("{$filename}");


$Row = $objPHPExcel->getActiveSheet(0)->getHighestRow();
$Column = $objPHPExcel->getActiveSheet(0)->getHighestColumn();

for ($i=1;$i <= $Row; $i++){
    $part = $objPHPExcel->getActiveSheet(0)->getCellByColumnAndRow(0, $i)->getValue();
$what = $objPHPExcel->getActiveSheet(0)->getCellByColumnAndRow(1, $i)->getValue();
$price = $objPHPExcel->getActiveSheet(0)->getCellByColumnAndRow(2, $i)->getValue();
$scurrency = $objPHPExcel->getActiveSheet(0)->getCellByColumnAndRow(3, $i)->getValue();
$ddeliver = $objPHPExcel->getActiveSheet(0)->getCellByColumnAndRow(4, $i)->getValue();
$dprice = $objPHPExcel->getActiveSheet(0)->getCellByColumnAndRow(5, $i)->getValue();
$ves = $objPHPExcel->getActiveSheet(0)->getCellByColumnAndRow(6, $i)->getValue();

}

In this loop i put values in database.
Code not included.


And for XLSX file

Just the beginning

$objReader = PHPExcel_IOFactory::createReader("Excel2007");
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("{$filename}");

Loop code is the same.

Am i reading the manual from the wrong side?

Using 1.7.2 PHPExcel, PHP 5.2.6, Apache 2.2.9 Debian Lenny.

Thanks.

Coordinator
Mar 24, 2010 at 9:53 AM
Edited Mar 24, 2010 at 10:02 AM

If you search the threads here, you'll find that we're very aware of the memory usage of PHPExcel, and are working hard on reducing that memory footprint with every release (while still trying to add the new functionality that users are asking for); and in the longer term, we're looking at cacheing the worksheet data so that it isn't all resident in memory. It's already possible to read in selected worksheets rather than the entire workbook using the setLoadSheetsOnly() method of the reader, and to apply a filter using the setReadFilter() method of the reader to restrict the data to a range of cells. Furthermore, with the setReadDataOnly(True) method in the reader, you can instruct PHPExcel to only read in the data for cells rather than all the formatting as well: it's often this formatting information that increases the memory requirements of reading a workbook.

The following thread outlines some of the latest working

http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=203384