Jump to content


Photo

Reading large excel files into MySQL with PHPExcel


  • Please log in to reply
No replies to this topic

#1 johndean

johndean

    Newbie

  • New Members
  • Pip
  • 1 posts

Posted 27 April 2013 - 09:11 PM

I would appreciate some assistance with formulating the syntax for PHPExcel to enable the reading of large excel spreadsheet values into a database.

 

I have the code to enter the values into the database which works well for smaller spreadsheets but I do not understand how to use setReadFilter/chunksize that I have seen mentioned to read larger spreadsheets in smaller 'chunks'.

 

The code that I'm currently using is -

$objPHPExcel = PHPExcel_IOFactory::load($path);

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;
    for ($row = 2; $row <= $highestRow; ++ $row) {
		unset($val);
        for ($col = 0; $col < $highestColumnIndex; ++ $col) {
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            $val[] = $cell->getValue();
        }
		$query="insert into `#__stock_list_staging` (`id`, `part_no`, `stock_no`, `description`, `condition`, `quantity`) values ('','".$val[0]."', '".$val[1]."', '".$val[2]."', '".$val[3]."', '".$val[4]."')";
        // execute query here
    }
}

If anybody can help me to get the setReadFilter code and syntax in place to work with the above code I would be very grateful.

 

Regards, John






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com