Fatal error: Allowed memory size of 67108864 bytes exhausted

Topics: Developer Forum, Project Management Forum, User Forum
Sep 20, 2012 at 9:48 PM
Edited Sep 20, 2012 at 9:51 PM

Hey guys,

           I have been working on some code that that allows me to read a spreadsheet and insert the values into a remote database out on Hostgator. When I run my current script I get the following error message 

Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 91 bytes) in /home/xoxo/public_html/xoxo/source/vendors/PHPExcel/Classes/PHPExcel/Worksheet.php on line 1121

Now when I go to worksheet.php I see the following statement 

 

 

 

$cell = $this->_cellCollection->addCacheData($pCoordinate,new PHPExcel_Cell($aCoordinates[0], $aCoordinates[1], null, PHPExcel_Cell_DataType::TYPE_NULL, $this));
Also I have looked at the config files...the php.ini files and I can't find where I need to go to change the max memory size. Do you think I need to contact hostgator and talk to them about this issue? Any input would be greatly appreciated! Thanks!

 

 

 

With that said here is my script

 

 

public function processSpreadsheet() {
$saveTimeZone = date_default_timezone_get();
date_default_timezone_set('UTC'); // Php's date function uses this value!

require_once '/home/xoxox/public_html/xoxoxo/source/vendors/PHPExcel/Classes/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("spreadsheet.xls");
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;
    echo "
The Worksheet ".$worksheetTitle." has ";
    echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
    echo ' and ' . $highestRow . ' rows.';
    echo '
Data: ';
    for ($row = 1; $row <= $highestRow; ++ $row) {

        $rw1 = $worksheet->getCellByColumnAndRow(1,$row)->getValue();
                    $rw2 = $worksheet->getCellByColumnAndRow(2,$row)->getValue();
                    $rw3 = $worksheet->getCellByColumnAndRow(3,$row)->getValue();
                    $rw4 = $worksheet->getCellByColumnAndRow(4,$row)->getValue();
                    $rw5 = $worksheet->getCellByColumnAndRow(5,$row)->getValue();
                    $rw6 = $worksheet->getCellByColumnAndRow(6,$row)->getValue();
                    $rw7 = $worksheet->getCellByColumnAndRow(7,$row)->getValue();
                    $rw8 = $worksheet->getCellByColumnAndRow(8,$row)->getValue();
                    $rw9 = $worksheet->getCellByColumnAndRow(9,$row)->getValue();
                    $rw10 = $worksheet->getCellByColumnAndRow(10,$row)->getValue();
                    $rw11 = $worksheet->getCellByColumnAndRow(11,$row)->getValue();

        $slic = mysql_connect("xoxoxoxo", "xoxoxoxo", "xoxoxo");
                      if(!$slic) {
                    die('Could not connect' . mysql_error());;

                        }
        mysql_select_db("xoxoxo", $slic); 
        $dbc = "INSERT INTO  Coverages( Name, Comments, Premium, Effective_Date, Expiration_Date, Broker_First_Name , Broker_Middle_Initial, Broker_Last_Name, Broker_Suffix, Broker_License_Number,  DateCreated)
                VALUES ('$rw1','$rw10','$rw2','$rw3', '$rw4', '$rw5','$rw6', '$rw7', '$rw8', '$rw9', '$rw11')";

            for ($col = 0; $col < $highestColumnIndex; ++ $col) {
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            $val = $cell->getFormattedValue();
            $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
            echo '';
        }
         $conn = mysql_query($dbc, $conn); {
            if (!msql_query($dbc, $conn)) {
                die ('Error: ' . mysql_error());
                echo "recorded added";
            }
         }


    }
    
}

date_default_timezone_set($saveTimeZone);
}
Sep 20, 2012 at 10:10 PM

if you can access the php.ini file, you have to increase the memory limit config.

Coordinator
Sep 20, 2012 at 10:40 PM

Look at using cell caching to reduce memory usage

 

Sep 24, 2012 at 9:24 PM

I looked into cell caching and tried a couple of methods and I didn't have any luck. I tried the cache in memory method, the cache in memory serialized, and cache in memory gzip.  Also I read this post. One thing I have not tried to do is read my spreadsheet in chunks. The reason I haven't tried is because my spreadsheet only contains one record. With that said, I am still getting the memory error message. Any thoughts?

Coordinator
Sep 24, 2012 at 10:52 PM

What do you mean by "one record"? Workbooks contain spreadsheets; spreadsheet's contain cells, organised into rows and columns

Sep 24, 2012 at 11:27 PM
Edited Sep 25, 2012 at 4:07 PM

Excuse me. I have a workbook with data in the first row of all of my spreadsheets. I have a workbook containing 5 spreadsheets. With that said, I have added a php.ini file to my project. I changed the memory limit in the file now Im not getting a memory error anymore.