How to upload to php a xlsx worksheet with 240000+ rows?

Question

Right now I have an error on upload a xlsx worksheet with 240000 rows.

The problem is the code can't read correctly the file. This is what i got from result of my php code.

Warning: simplexml_load_string(): Memory allocation failed : building attribute
in C:\xampp\htdocs\excelamysql\Classes\PHPExcel\Reader\Excel2007.php on line 625

Warning: simplexml_load_string(): Memory allocation failed in C:\xampp\htdocs\ex
celamysql\Classes\PHPExcel\Reader\Excel2007.php on line 625

Warning: simplexml_load_string(): Entity: line 2: parser error : Memory allocati
on failed : xmlSAX2AttributeNs in C:\xampp\htdocs\excelamysql\Classes\PHPExcel\R
eader\Excel2007.php on line 625

Warning: simplexml_load_string(): >48161</v></c><c r="B27695" s="10" t="s"><v>49
</v></c><c r="C27695" s="11" t="s" in C:\xampp\htdocs\excelamysql\Classes\PHPExc
el\Reader\Excel2007.php on line 625

Warning: simplexml_load_string():
                                 ^ in C:\xampp\htdocs\excelamysql\Classes\PHPExc
el\Reader\Excel2007.php on line 625

Warning: simplexml_load_string(): Entity: line 2: parser error : Extra content a
t the end of the document in C:\xampp\htdocs\excelamysql\Classes\PHPExcel\Reader
\Excel2007.php on line 625

Warning: simplexml_load_string(): >48161</v></c><c r="B27695" s="10" t="s"><v>49
</v></c><c r="C27695" s="11" t="s" in C:\xampp\htdocs\excelamysql\Classes\PHPExc
el\Reader\Excel2007.php on line 625

Warning: simplexml_load_string():
                                 ^ in C:\xampp\htdocs\excelamysql\Classes\PHPExc
el\Reader\Excel2007.php on line 625

And this is a piece of php code:

$objReader->setLoadSheetsOnly($sheetname);

$objPHPExcel = $objReader->load('C:\xampp\htdocs\excelamysql\Maestra.xlsx');
$worksheet = $objPHPExcel->setActiveSheetIndex(0);   

foreach($worksheet->getRowIterator() as $row)
{
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(TRUE);
 foreach($cellIterator as $cell)
 {
  $val = $cell->getValue();

  $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
 }
}

for ($row = 2; $row <= $highestRow; ++ $row) 
{ 
  $val=array(); 
  $cell = $worksheet->getCellByColumnAndRow(12, $row);
  $val[] = $cell->getValue();
  $sql="insert ignore into Estado (Id_est,nom_est )values('".'auto'."','" . $val[0] . "')"; 
  $link= mysql("pago_coberturas", $sql); 
  $sql2="ALTER TABLE Estado AUTO_INCREMENT = 1";
  $link2= mysql("pago_coberturas",$sql2);

possible duplicate of [How to read large worksheets from large Excel files (27MB+) with PHPExcel?](http://stackoverflow.com/questions/4666746/how-to-read-large-worksheets-from-large-excel-files-27mb-with-phpexcel) — OneOfOne, Sep 30 '13 at 13:29
Have you tried using SQLyog and importing the XLSX straight into the table from the local file? — DevlshOne, Sep 30 '13 at 13:29
Have you tried enabling PHPExcel's cell caching to reduce memory usage? Have you tried loading the file in "chunks"? — Mark Baker, Sep 30 '13 at 13:33

score 0 · Answer 1 · edited May 23 '17 at 11:57

Don't bother with lifting the memory_limit, unless you want your whole sheet to end up in there two times: as a string, and as simplexml representation. PHPExcel is perfect for smaller sheets, but by default it sucks at handling large data sets (primary concern is that it's slow, secondary is that it consumes a bunch of memory as you already noticed);

You have a few options:

If you really really want to use PHPExcel, of course you could figure out some solution involving it, there is one available in here: How to read large worksheets from large Excel files (27MB+) with PHPExcel? - be cautious as there are a few caveats to that
You could save your XLS file as CSV, and use fgetcsv built-in function - it is lightning fast, and only consume as much memory, as one row can take
If you are stuck with xlsx, then hey, it's just a zip archive. You could unpack it with ZipArchive class, and then read the sheet's data with XMLReader

The CSV way will be the most robust one

score 0 · Answer 2 · answered Jan 27 '15 at 19:02

As Adam said, PHPExcel is not designed to handle large worksheets. They implemented different caching strategies as well as some options to optimize the amount of memory used, but this affects the performance badly.

If you have to stick with XLSX, check out Spout. It's an open-source library that can read and write XLSX files and was optimized for scale.

Reading the 240000+ rows will take about 1-2 minutes and require less than 10MB of memory!

How to upload to php a xlsx worksheet with 240000+ rows?

2 Answers2