Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 40 bytes) on line 1683 - Problems when processing large files

[GoogleCodeExporter]

What steps will reproduce the problem?
1.If you give as an input a very big Excel file.

Original issue reported on code.google.com by anv...@gmail.com on 4 Nov 2010 at 4:12

[GoogleCodeExporter]

Did anybody find the solution 
I have the same problème 
couldwe take ligne by ligne ?
thanks by advance

Original comment by imadqo...@gmail.com on 8 Nov 2010 at 4:26

[GoogleCodeExporter]

are you trying to pass formatting?

if so, place a false statement next to your file name to prevent passing css 
data for parsing. the css data are objects and not data streams. as a result, 
the data becomes 4-8x larger. this may also solve some of the encoding problems 
that you run into.
$data = new Spreadsheet_Excel_Reader(your_file_name,false);

if this still does not assist you... review your excel spreadsheet... i've 
found that by simply removing wrap text on your excel spreadsheet... you can 
reduce the file size about 2.5-3x. this is pretty significant when we're 
talking about 25 - 40 meg files.

if you want to pass some of the css and not all... this will have to be trial 
and error for requesting formatting information to the web page or to simply 
set the function to return a false value within the class. might be better to 
request only certain css information as true and the rest to false. ;)
http://code.google.com/p/php-excel-reader/wiki/Documentation
pay attention to cell info and formatting details... and you can get an idea of 
how to make the calls from some of the examples

if none of this helps... post here again and i'll try and assist, but the 
developers are probably the best resource. :)

Original comment by eoc92...@hotmail.com on 27 Jan 2011 at 10:37

[GoogleCodeExporter]

Hi,  I am still having problems with this, even with formatting off.  I do not 
have access to the original xml to adjust the formatting.  The spreadsheet is 
about 15 megs.  

I have formatting turned off, and all I want to do is save it as a csv.

I have used your info $data = new 
Spreadsheet_Excel_Reader(your_file_name,false);
but I am still running out of memory on my hosting account.

Original comment by marswe...@gmail.com on 7 Feb 2011 at 8:01

[GoogleCodeExporter]

Got the same error. Format off (false)

And 

atal error: Allowed memory size of 157286400 bytes exhausted (tried to allocate 
32 bytes) 

Max memory from the shared server I'm in is 150M.

The file has 5k lines and less than 1mb.

:(

Original comment by sauloben...@gmail.com on 16 Feb 2011 at 5:01

[GoogleCodeExporter]

@ comment 3:
are you trying to format an xml document? or excel document?

this script does not and will not work with .xlsx extensions.

in any regards, normal troubleshooting... have you reviewed your php.ini file? 
is there enough memory allocated for uploads and the processing of a script? 
also, check the amount of time that a script can run.

what i have outlined does not output as a csv. that is a completely separate 
method that i am not processing. you will require to look at the documentation 
page... there is a function for csv that can assist you and search for csv... 
http://code.google.com/p/php-excel-reader/wiki/Documentation

if you have an issue utilizing a function... google how to use a function... do 
not post here on how to call a function... using the function should work; 
though, i have not personally had a need for it or tested it.

@ comment 4:
again... please review your php.ini and follow my above directions.

@comment 3 & 4:
review my previous comment #2 and my below comments.

i have successfully upload files with my posted method that are 25-30 megs. 
i've reduced the file size to 7-10 megs by ensuring there is no wrap text 
selected in the formatting... ensure that your excel document is not locked... 
ensure that all your dates are converted to the proper date formatting as you 
are expecting to output within the excel document (this is big!)... also, 
setting the css output parameter to false... and finally, save as an xls. do 
not just save the document... microsoft has a nasty habit of placing their junk 
in files/headers. ;)

if all else fails... please review your client side computer for the parsing 
and if possible, try another computer with more memory and/or ensure that your 
personal computer is no the source of a rogue memory eater.

again... please post here if there are issues. if the issue persists... give me 
some more info and i can assist better such as, excel documents, logs and ini 
information pertaining to the script.

Original comment by eoc92...@hotmail.com on 16 Feb 2011 at 7:14

[GoogleCodeExporter]

@comment 5. Thank you very much for the help/reply. 

All my PHP.ini settings are ok. 

memory_limit 150M

150M it'd the max used by Dreamhost (my server), the default it's 90M, I 
changed it to 150M and no luck.

My file it's one Microsoft XLS 5.0/95 format, 4.616KB
I already saved as one 2003 format, 3.484KB 

It's the same content, 26,257 rows. Just text, no formula, no functions. I'm 
reading the file line by line and implementing on one MYSQL DB. Got the code 
here at the forum.

I'm using the 'false' command. 

Thanks.

Original comment by sauloben...@gmail.com on 17 Feb 2011 at 4:53

[GoogleCodeExporter]

have you checked if you've set up your database correctly? is the mysql type 
set to text? is the excel information in english?

for the process of inserting data into mysql... i used pdo instead of mysql 
specific commands. pdo allows for portability to other databases as needed 
without a lot of rework.

is there still an issue or was the issue resolved?

if not... can you post your code? and sample excel information?

Original comment by eoc92...@hotmail.com on 17 Feb 2011 at 6:55

[GoogleCodeExporter]

Everything works on my Local server, I think the problem it's the Dreamhost 
server.

Everything is ok on the database, if it's one small file it works fine, but 
when I go to a bigger file it crashes on the "Allowed memory" error.

My code:

<?php

ini_set("display_errors",1);    // be a BIT verbose ON ANY ERRORS FOR testing
error_reporting(E_ALL ^ E_NOTICE);
require_once 'excel_reader/excel_reader2.php';
$xls = NEW Spreadsheet_Excel_Reader($local_data_file,FALSE);   // opent he .xls 
FILE specified ON the command line

FOR ($ROW=1;$ROW<=$xls->rowcount();$ROW++) {          // go through EACH ROW
$SQL = "INSERT INTO xls_table VALUES(";                     // START AND SQL 
statement TO INSERT INTO mytable

FOR ($col=1;$col<=11;$col++) {        //  run through EACH COLUMN
$SQL .= "\"" . addslashes($xls->val($ROW,$col)) . "\"";     // append the cell 
VALUE INTO the INSERT statement

IF ($col < 11) $SQL .= ",";                    // stick commas IN BETWEEN the 
VALUES
}
$SQL .= ");";                                                       // finish 
off the SQL statement

//echo $SQL . "<br/>";     // AND WRITE it TO screen FOR debug (you would 
EXECUTE it here obv).

mysql_query($SQL);      
}

?>

Original comment by sauloben...@gmail.com on 17 Feb 2011 at 7:13

[GoogleCodeExporter]

my suggestion would be to try and utilize a terminal window and vi the the 
php.ini file or view the ini settings through one of their graphical displays. 
and probably the sure fire bet... after you've exhausted the other methods... 
especially if you can upload and parse the data locally... open a trouble 
ticket with your host.

Original comment by eoc92...@hotmail.com on 18 Feb 2011 at 2:20

[GoogleCodeExporter]

Does anyone know what the corrected code for the _GetInt4d function to allow 
for the bit shift on 64bit processors. I'm still getting the error above and 
have tried as much as I know how to. Somebody suggests this solution here:
http://ykyuen.wordpress.com/2009/09/28/cakephp-php-excel-reader/

Original comment by johnhall...@gmail.com on 26 Apr 2011 at 10:19

[GoogleCodeExporter]

I had this same issue and just found the problem. I had the file open that I 
was trying to upload and read via the excel reader. 

Original comment by john...@gmail.com on 2 Nov 2012 at 8:45