I am using Codeigniter for just reading only 1 sheet from .xlsx file and storing it into database using batch
Error occur only if i insert more than 30k rows i.e 31k will throw error and till 30k it is absolutely fine
A PHP Error was encountered
Severity: Warning
Message: XMLReader::XML(): Memory allocation failed : growing buffer
Message: XMLReader::XML(): Unable to load source data
Message: XMLReader::setParserProperty(): Invalid parser property
Message: XMLReader::read(): Load Data before trying to read
Filename: Reader/Excel2007.php
Line Number: 203
Function: XML, listWorksheetInfo, setParserProperty
//(I combined the messages as it will be very long)
The Solutions i have tried is
PHPExcel runs out of 256, 512 and also 1024MB of RAM
How to read large worksheets from large Excel files (27MB+) with PHPExcel?
I need to Insert minimum 65k Records with 53columns of data to the database
- I have Changed the chunk Size from 20 to 1000
- I have already loaded the ChunkRead filter and excel Library and also used the Cache method and the unset method of PHPExcel but seems of no use
I have used batch_insert in the model to insert data
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp; $cacheSettings = array( ' memoryCacheSize ' => '8MB'); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); $objPHPExcel->disconnectWorksheets (); unset ( $objPHPExcel );
MY Example Code:
public function report() {
$path = "files/test_31k_rows";
$inputFileType = 'Excel2007';
$inputFileName = $path;
//Create a new Reader of the type defined in $inputFileType
$objReader = PHPExcel_IOFactory::createReader ( $inputFileType );
// Define how many rows we want to read for each "chunk"
$chunkSize = 100;
//Create a new Instance of our Read Filter
$chunkFilter = new ReadFilter ();
// Tell the Reader that we want to use the Read Filter that we've Instantiated
$objReader->setReadFilter ( $chunkFilter );
// Loop to read our worksheet in "chunk size" blocks $startRow is set to 2 initially because we always read the headings in row #1
$startRow = 2;
$chunkFilter->setRows ( $startRow, $chunkSize );
$worksheetData = $objReader->listWorksheetInfo($inputFileName);
foreach ($worksheetData as $worksheet) {
$highestRow=$worksheet['totalRows'];
}
for($startRow = 2; $startRow <= $highestRow; $startRow += $chunkSize) {
$batch = array();
//Tell the Read Filter, the limits on which rows we want to read this iteration
$chunkFilter->setRows ( $startRow, $chunkSize );
// Load only the rows that match our filter from $inputFileName to a PHPExcel Object
$objPHPExcel = $objReader->load ( $inputFileName );
foreach ( $objPHPExcel->getWorksheetIterator () as $worksheet ) {
$worksheetTitle = $worksheet->getTitle ();
$highestRow = $worksheet->getHighestRow (); // e.g. 10
$highestColumn = $worksheet->getHighestColumn (); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString ( $highestColumn );
$nrColumns = ord ( $highestColumn ) - 64;
$loopForRows = ($highestRow > $chunkSize) ? ($startRow + $chunkSize) : ($highestRow + 1);
for($row = $startRow; $row < $loopForRows; ++ $row) {
$val = array ();
for($col = 0; $col < $highestColumnIndex; ++ $col) {
$cell = $worksheet->getCellByColumnAndRow ( $col, $row );
if(PHPExcel_Shared_Date::isDateTime($cell)) {
$InvDate= $cell->getValue();
$InvDate = date($format = "Y-m-d H:i:s", PHPExcel_Shared_Date::ExcelToPHP($InvDate));
$val [] = $InvDate;
} else {
if($col == 52) {
$val [] = $worksheet->getCellByColumnAndRow($col, $row)->getFormattedValue($cell->getValue());
} else {
$val [] = $cell->getValue ();
}
}
}
$data = array (
"val0" => $val [0],
"val1" => $val [1],
"val2" => $val [2],
"val3" => $val [3],
"val4" => $val [4],
"val5" => $val [5],
"val6" => $val [6],
"val7" => $val [7],
"val8" => $val [8],
"val9" => $val [9],
"val11" => $val [10],
"val11" => $val [11],
"val12" => $val [12],
"val13" => $val [13],
"val14" => $val [14],
"val15" => $val [15],
"val16" => $val [16],
"val17" => $val [17],
"val18" => $val [18],
"val19" => $val [19],
"val20" => $val [20],
"val21" => $val [21],
"val22" => $val [22],
"val23" => $val [23],
"val24" => $val [24],
"val25" => $val [25],
"val26" => $val [26],
"val27" => $val [27],
"val28" => $val [28],
"val29" => $val [29],
"val30" => $val [30],
"val31" => $val [31],
"val32" => $val [32],
"val33" => $val [33],
"day_1" => $val [34],
"day_2" => $val [35],
"day_3" => $val [36],
"day_4" => $val [37],
"day_5" => $val [38],
"day_6" => $val [39],
"day_7" => $val [40],
"day_8" => $val [41],
"day_9" => $val [42],
"day_10" => $val [43],
"day_11" => $val [44],
"day_12" => $val [45],
"day_13" => $val [46],
"day_14" => $val [47],
"day_15" => $val [48],
"val49" => $val [49],
"val50" => $val [50],
"val51" => $val [51],
"val52" => $val [52]
);
$batch[] = $data;
}
}
//$objPHPExcel->disconnectWorksheets ();
//unset ( $objPHPExcel );
$this->Upload_model->upload_insert($batch);
}
}
Any Help will be appreciated.
