PERL Beginners - Spreadsheet::ParseExcel

Author

Spreadsheet::ParseExcel - Out of memory error

Craig Moynes

2005-04-27, 3:56 am

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

Jay Savage

2005-04-27, 3:56 pm

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

Luke Bakken

2005-04-27, 3:56 pm

> 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";
}

Craig Moynes

2005-04-27, 3:56 pm

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
>

Craig Moynes

2005-04-27, 8:56 pm

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:
];[color=darkred]
;[color=darkred]
>=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.

David Van Ginneken

2005-04-27, 8:56 pm

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:
];[color=darkred]
;[color=darkred]
>=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
>

Sponsored Links