Spreadsheet::ParseExcel - Out of memory error

Hi All,
I am using the spreadsheet::parseexcel module to open up a series (31)
spreadsheets and grab the row counts.

Here is an excerpt with the ParseExcel Code.

my $oBook;
my $oWks;
foreach $hashEntry ( @LOGS )
{

my ( $localfile)        = $hashEntry->{name};
my ( $err_msg )         = "";           # error message variable


my $cmd                 = "";

#
# Get row count of each file, to generate results file
#
$oBook = new Spreadsheet::ParseExcel::Workbook->Parse($localfile);
my ($iR, $iC, $oWkS, $oWkC);
$oWkS = ${$oBook->{Worksheet}}[0];

print "------ SHEET: ".$oWkS->{Name}. "\n";
print "Row: ".$oWkS->{MinRow}." v ".$oWkS->{MaxRow}."\n";
$resultMessage.=basename($localfile).",".$oWkS->{MaxRow}."\n";
}

The problem I am running into is after 10 files (in testing all 31 files
are the same source file with different names), and then I get an out of
memory error.  Anyone have any idea how I can clean out the memory.  I
have a feeling it might be some autocaching or something not getting
cleaned up within ParseExcel.

Cheers,
Craig

On 4/26/05, Craig Moynes <retinaburn@rogers.com> wrote:
> Hi All,
> I am using the spreadsheet::parseexcel module to open up a series (31)
> spreadsheets and grab the row counts.
>=20
> Here is an excerpt with the ParseExcel Code.
>=20
> my $oBook;
> my $oWks;
> foreach $hashEntry ( @LOGS )
> {
>=20
>          my ( $localfile)        =3D $hashEntry->{name};
>          my ( $err_msg )         =3D "";           # error message variab=
le
>=20
>          my $cmd                 =3D "";
>=20
>          #
>          # Get row count of each file, to generate results file
>          #
>          $oBook =3D new Spreadsheet::ParseExcel::Workbook->Parse($localfi=
le);
>          my ($iR, $iC, $oWkS, $oWkC);
>          $oWkS =3D ${$oBook->{Worksheet}}[0];
>=20
>          print "------ SHEET: ".$oWkS->{Name}. "\n";
>          print "Row: ".$oWkS->{MinRow}." v ".$oWkS->{MaxRow}."\n";
>          $resultMessage.=3Dbasename($localfile).",".$oWkS->{MaxRow}."\n";
> }
>=20
> The problem I am running into is after 10 files (in testing all 31 files
> are the same source file with different names), and then I get an out of
> memory error.  Anyone have any idea how I can clean out the memory.  I
> have a feeling it might be some autocaching or something not getting
> cleaned up within ParseExcel.
>=20
> Cheers,
>     Craig


$ResultMessage contains a reference to the ParseExcel object, so the
object never goes out of scope, and each iteration through the block
places a new object in memory.  See Persistent Private Variables in
perldoc perlsub, as well as perlboot, etc.  You need to completel
finish with the object before the block exits.  Try,

$resultMessage.=3D sprintf("$s,%s\n", basename($localfile), $oWkS->{Max=
Row}) ;

or something similar.

HTH,

Jay

> my $oBook;
> my $oWks;
> foreach $hashEntry ( @LOGS )
> {
>=20
>          my ( $localfile)        =3D $hashEntry->{name};
>          my ( $err_msg )         =3D "";           # error message
> variable=20
>=20
>=20
>          my $cmd                 =3D "";
>=20
>          #
>          # Get row count of each file, to generate results file
>          #
>          $oBook =3D new
>          Spreadsheet::ParseExcel::Workbook->Parse($localfile); my
>          ($iR, $iC, $oWkS, $oWkC); $oWkS =3D =
${$oBook->{Worksheet}}[0];
>=20
>          print "------ SHEET: ".$oWkS->{Name}. "\n";
>          print "Row: ".$oWkS->{MinRow}." v ".$oWkS->{MaxRow}."\n";
>        =20
> $resultMessage.=3Dbasename($localfile).",".$oWkS->{MaxRow}."\n"; }
>=20
> The problem I am running into is after 10 files (in testing all 31
> files are the same source file with different names), and then I get
> an out of memory error.  Anyone have any idea how I can clean out the
> memory.  I have a feeling it might be some autocaching or something
> not getting cleaned up within ParseExcel.

If you move the 'my $oBook' inside the for loop it the object should be
destroyed on each iteration:

for my $hashEntry ( @LOGS )
{

my ( $localfile)        =3D $hashEntry->{name};
my ( $err_msg )         =3D "";           # error message
variable=20


my $cmd                 =3D "";

#
# Get row count of each file, to generate results file
#
my $oBook =3D new
Spreadsheet::ParseExcel::Workbook->Parse($localfile);
my ($iR, $iC, $oWkC);
my $oWkS =3D ${$oBook->{Worksheet}}[0];

print "------ SHEET: ".$oWkS->{Name}. "\n";
print "Row: ".$oWkS->{MinRow}." v ".$oWkS->{MaxRow}."\n";
=20
$resultMessage.=3Dbasename($localfile).",".$oWkS->{MaxRow}."\n";
}

Hi Gents,
I tried both suggestions:

#
# For each log in the array
#       - grab the directory and name of the file.
#       - send via sendmail
#
foreach $hashEntry ( @LOGS )
{

my ( $localfile)        =3D $hashEntry->{name};
my ( $err_msg )         =3D "";           # error message variable


my $cmd                 =3D "";

#
# Get row count of each file, to generate results file
#
my $oBook =3D new Spreadsheet::ParseExcel::Workbook->Parse($localfi=
le);
my $oWkS =3D ${$oBook->{Worksheet}}[0];

print "------ SHEET: ".$oWkS->{Name}. "\n";
print "Row: ".$oWkS->{MinRow}." v ".$oWkS->{MaxRow}."\n";
$resultMessage.=3Dsprintf("%s,%s\n",basename($localfile),$oWkS->{Ma=
xRow});
}

But I still get an out of memory error on the 10th file opened.

Any additional suggestions?

On 4/27/05, Bakken, Luke <Luke.Bakken@getronics.com> wrote: 
>=20
> If you move the 'my $oBook' inside the for loop it the object should be
> destroyed on each iteration:
>=20
> for my $hashEntry ( @LOGS )
> {
>=20
>          my ( $localfile)        =3D $hashEntry->{name};
>          my ( $err_msg )         =3D "";           # error message
> variable
>=20
>          my $cmd                 =3D "";
>=20
>          #
>          # Get row count of each file, to generate results file
>          #
>          my $oBook =3D new
> Spreadsheet::ParseExcel::Workbook->Parse($localfile);
>          my ($iR, $iC, $oWkC);
>          my $oWkS =3D ${$oBook->{Worksheet}}[0];
>=20
>          print "------ SHEET: ".$oWkS->{Name}. "\n";
>          print "Row: ".$oWkS->{MinRow}." v ".$oWkS->{MaxRow}."\n";
>=20
>         $resultMessage.=3Dbasename($localfile).",".$oWkS->{MaxRow}."\n";
> }
>=20
> --
> To unsubscribe, e-mail: beginners-unsubscribe@perl.org
> For additional commands, e-mail: beginners-help@perl.org
> <http://learn.perl.org/> <http://learn.perl.org/first-response>
>=20
>

Ok perlers, It was actually fairly easy. =20

I added a delete just before the end of the for loop.
delete $oBook->{Worksheet};

Not sure how the references work internally in Perl.  I guess that
when oBook is assigned a new Spreadsheet the previous Spreadsheet is
not getting cleaned up.

Cheers,

On 4/27/05, Craig Moynes <craig.moynes@gmail.com> wrote:
> Hi Gents,
>    I tried both suggestions:
>=20
> #
> # For each log in the array
> #       - grab the directory and name of the file.
> #       - send via sendmail
> #
> foreach $hashEntry ( @LOGS )
> {
>=20
>         my ( $localfile)        =3D $hashEntry->{name};
>         my ( $err_msg )         =3D "";           # error message variabl=
e
>=20
>         my $cmd                 =3D "";
>=20
>         #
>         # Get row count of each file, to generate results file
>         #
>         my $oBook =3D new Spreadsheet::ParseExcel::Workbook->Parse($local=
file);
>         my $oWkS =3D ${$oBook->{Worksheet}}[0];
>=20
>         print "------ SHEET: ".$oWkS->{Name}. "\n";
>         print "Row: ".$oWkS->{MinRow}." v ".$oWkS->{MaxRow}."\n";
>         $resultMessage.=3Dsprintf("%s,%s\n",basename($localfile),$oWkS->{=
MaxRow});
> }
>=20
> But I still get an out of memory error on the 10th file opened.
>=20
> Any additional suggestions?
>=20
> On 4/27/05, Bakken, Luke <Luke.Bakken@getronics.com> wrote: 
]; 
; 
>=20


--=20
Primer:
Shane Carruth: It's about some engineers that are trying to build a
device that "degrades" gravity. They find out it has unexplainable
properties. Hilarity ensues.

Try something like this.  I just tried it on 60+ files and it seems to
work.  (If you have multiple worksheets you may need to modify the
cell_handler to exclude the ones you don't want)

use Spreadsheet::ParseExcel;
use File::Basename;
use strict;
my $resultMessage =3D '';
my $maxrow =3D 0;
my $oBook;
opendir(DIR, "./files");
my @names =3D readdir(DIR) or die "could not $@";
foreach my $file(@names) {
next unless $file =3D~ /.xls$/;
print "Trying File.. $file\n";
$maxrow =3D 0;
my $excel =3D new Spreadsheet::ParseExcel(CellHandler =3D>
\&cell_handler, NotSetCell  =3D> 1);
$oBook =3D $excel->Parse("./files/$file") or die "$file . $!";
$resultMessage.=3Dsprintf("%s,%s\n",basename($file),$maxrow);
}
print $resultMessage . "\n";

sub cell_handler {
my $row =3D $_[2];
$row++;
$maxrow =3D $row if $row > $maxrow;
}


On 4/27/05, Craig Moynes <craig.moynes@gmail.com> wrote:
> Hi Gents,
>    I tried both suggestions:
>=20
> #
> # For each log in the array
> #       - grab the directory and name of the file.
> #       - send via sendmail
> #
> foreach $hashEntry ( @LOGS )
> {
>=20
>         my ( $localfile)        =3D $hashEntry->{name};
>         my ( $err_msg )         =3D "";           # error message variabl=
e
>=20
>         my $cmd                 =3D "";
>=20
>         #
>         # Get row count of each file, to generate results file
>         #
>         my $oBook =3D new Spreadsheet::ParseExcel::Workbook->Parse($local=
file);
>         my $oWkS =3D ${$oBook->{Worksheet}}[0];
>=20
>         print "------ SHEET: ".$oWkS->{Name}. "\n";
>         print "Row: ".$oWkS->{MinRow}." v ".$oWkS->{MaxRow}."\n";
>         $resultMessage.=3Dsprintf("%s,%s\n",basename($localfile),$oWkS->{=
MaxRow});
> }
>=20
> But I still get an out of memory error on the 10th file opened.
>=20
> Any additional suggestions?
>=20
> On 4/27/05, Bakken, Luke <Luke.Bakken@getronics.com> wrote: 
]; 
; 
>=20
> --
> To unsubscribe, e-mail: beginners-unsubscribe@perl.org
> For additional commands, e-mail: beginners-help@perl.org
> <http://learn.perl.org/> <http://learn.perl.org/first-response>
>=20
>