Help read big file

Topics: Developer Forum
Jul 11, 2012 at 1:41 PM
Edited Jul 11, 2012 at 1:43 PM

Hello, i'm trying to read a big excel file with around 20.000 rows around 22 cells each one

I put in my code this to test the memory:

echo " Peak memory usage: " . (number_format(memory_get_peak_usage(true))) . " B\r\n";

And the result is: Peak memory usage: 303,300,608 B

 

My code is this:

 

require_once 'Excel/PHPExcel/IOFactory.php';
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

$inputFileType = 'Excel5';

$sheetname = 'Signes';

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadSheetsOnly($sheetname);
$objPHPExcel = $objReader->load($arquivo);

//$objReader->setReadDataOnly(true);
$locale = 'pt_br';
$validLocale = PHPExcel_Settings::setLocale($locale);
if (!$validLocale) {
    echo 'Unable to set locale to '.$locale." - reverting to en_us
\n";
}

$excel = array();
$excel = $objPHPExcel->getActiveSheet()->toArray(null,false,true,false);
unset($excel[0]);
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);




After this array $excel is done i do many querys into my database and then i save the array in a table
But after 1000 rows inserted i receive the error php timeout, then the browser show the php page to download.
How can i read this file more efficiently?
Thanks
Jul 11, 2012 at 4:46 PM

I've written a website tool to read a spreadsheet file and display the contents of the data. It is written for efficiency in memory and has specific settings that can be changed to adjust the memory usage. If you are interested I can zip the whole website code up for you. Just email me at schir1964@gmail.com and I'll send you the instructions for download.

- Christopher Mullins