Hi,
Here is an example of a program and a perl module that parses a .xls file
and eats the whole memory.
I have tried it under Linux and Windows and it has the same problem under
both OSs, so it has big bugs.
I have tried using that destructor class in the perl module but I think what
I have done is not correct.
Thank you for telling me how to solve this issue.
The program executes a method from the module but it doesn't need any result
of that method, because it just parse a .xls file, get some results and
inserts them into MySQL, then the program does the same with the next file,
and so on.
The program:
use lib ".";
use strict;
use Parse;
my $f = Parse->new;
opendir(DIR, ".");
my @files = readdir DIR;
closedir DIR;
foreach my $file(@files) {
next unless $file =~ /\.xls$/i;
$f->parse($file);
}
The module:
package Parse;
use strict;
use Spreadsheet::ParseExcel;
use DBI;
my $dbh = DBI->connect("DBI:mysql:database=bvb;host=127.0.0.1", "root",
undef, {RaiseError=>1, PrintError=>0});
sub new {
my $init = shift;
my $class = ref($init) || $init;
my $self = { @_ };
return bless($self, $class);
}
sub parse {
my $self = shift;
my $file = shift;
my $book = Spreadsheet::ParseExcel::Workbook->Parse($file);
my $sheet = ${$book->{Worksheet}}[0];
my %f;
for(my $i = 0; $i <= 1000; $i++) {
my ($cod, $name);
my $market = $sheet->{Cells}[$i][3]->{Val};
if ($market =~ /total|regular|deal|odd lot/i and
$sheet->{Cells}[$i][6]->{Val} =~ /^[\d,.]+$/) {
if ($sheet->{Cells}[$i][1]->{Val} =~ /^\w{3,4}[\*\+]?$/) {
$cod = $sheet->{Cells}[$i][1]->{Val};
$cod =~ s/[\*\+]$//;
$name = $sheet->{Cells}[$i][2]->{Val};
}
elsif ($sheet->{Cells}[$i-1][1]->{Val} =~ /^\w{3,4}[\*\+]?$/) {
$cod = $sheet->{Cells}[$i-1][1]->{Val};
$cod =~ s/[\*\+]$//;
$name = $sheet->{Cells}[$i-1][2]->{Val};
}
elsif ($sheet->{Cells}[$i-2][1]->{Val} =~ /^\w{3,4}[\*\+]?$/) {
$cod = $sheet->{Cells}[$i-2][1]->{Val};
$cod =~ s/[\*\+]$//;
$name = $sheet->{Cells}[$i-2][2]->{Val};
}
elsif ($sheet->{Cells}[$i-3][1]->{Val} =~ /^\w{3,4}[\*\+]?$/) {
$cod = $sheet->{Cells}[$i-3][1]->{Val};
$cod =~ s/[\*\+]$//;
$name = $sheet->{Cells}[$i-3][2]->{Val};
}
$f{$cod}{$market}{name} = $name;
$f{$cod}{$market}{vn} = $sheet->{Cells}[$i][4]->{Val};
$f{$cod}{$market}{per} = $sheet->{Cells}[$i][5]->{Val};
$f{$cod}{$market}{close} = $sheet->{Cells}[$i][6]->{Val};
$f{$cod}{$market}{change} = $sheet->{Cells}[$i][7]->{Val};
$f{$cod}{$market}{open} = $sheet->{Cells}[$i][8]->{Val};
$f{$cod}{$market}{max} = $sheet->{Cells}[$i][9]->{Val};
$f{$cod}{$market}{min} = $sheet->{Cells}[$i][10]->{Val};
$f{$cod}{$market}{average} = $sheet->{Cells}[$i][11]->{Val};
$f{$cod}{$market}{volume} = $sheet->{Cells}[$i][12]->{Val};
$f{$cod}{$market}{value} = $sheet->{Cells}[$i][13]->{Val};
$f{$cod}{$market}{trades} = $sheet->{Cells}[$i][14]->{Val};
$f{$cod}{$market}{max52} = $sheet->{Cells}[$i][15]->{Val};
$f{$cod}{$market}{min52} = $sheet->{Cells}[$i][16]->{Val};
$f{$cod}{$market}{van} = $sheet->{Cells}[$i][17]->{Val};
}
}
my ($year, $mon, $mday) = $file =~ /trades(\d\d\d\d)(\d\d)(\d\d)\.xls$/gi;
my $date = "$mon/$mday/$year";
#Create the table if it is not there
my $rapoarte_t = $dbh->do("create table if not exists temp_rapoarte(
symbol varchar(10) not null,
name varchar(255) not null,
market varchar(10) not null,
date date not null,
open varchar(20) not null,
high varchar(20),
low varchar(20),
close varchar(20) not null,
volume varchar(20) not null,
vn varchar(20),
per varchar(20),
change_ varchar(20),
average_ varchar(20),
value_ varchar(30) not null,
trades varchar(5),
min52 varchar(20),
max52 varchar(20),
van varchar(20),
primary key(symbol, date, market)
)");
#Insert into database
my $rapoarte_i = $dbh->prepare("insert ignore into temp_rapoarte values(?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
foreach my $cod(sort keys %f) {
foreach my $market(keys %{$f{$cod}}) {
$rapoarte_i->execute($cod, $f{$cod}{$market}{name}, $market,
"$year-$mon-$mday", $f{$cod}{$market}{open}, $f{$cod}{$market}{max},
$f{$cod}{$market}{min}, $f{$cod}{$market}{close}, $f{$cod}{$market}{volume},
$f{$cod}{$market}{vn}, $f{$cod}{$market}{per}, $f{$cod}{$market}{change},
$f{$cod}{$market}{average}, $f{$cod}{$market}{value},
$f{$cod}{$market}{trades}, $f{$cod}{$market}{min52},
$f{$cod}{$market}{max52}, $f{$cod}{$market}{van});
}
}
}
sub DESTROY {
my $self = shift;
undef $self;
#I don't know if this sub helps, or if it is correctly written
}
1;
I can send you a .xls file if you think it will help, but I guess the errors
of my program or module are obvious.
Thank you.
Teddy
----- Original Message -----
From: "Jay" <[EMAIL PROTECTED]>
To: "Perl Beginners List" <beginners@perl.org>
Sent: Wednesday, January 12, 2005 12:22 AM
Subject: Re: Memory full
> On Mon, 10 Jan 2005 12:53:50 +0200, Octavian Rasnita <[EMAIL PROTECTED]>
wrote:
> > Hi,
> >
> > I have made a perl module object oriented which is something like this:
> >
>
> > Then I access the parse() method in another program that feeds the
module
> > with more Excel files (around 500 files).
> >
> > After more files parsed (around 300), the memory is full.
> >
> > Please tell me what can I do to free the memory after each file parsed
by
> > the module.
> >
> > I run this program manually or by a cron job, but I would like to know
> > better how to free the memory because I might want to use it under
mod_perl.
> >
> > Thank you very much for any tip.
> >
> > Teddy
> >
>
> Teddy,
>
> This is one of the tricky parts of objects and pass-by-reference
> applications: a refence never goes out of scope, so it's memory is
> never reclaimed. Think about what would happen, for example, if you
> created 500 hundred references to DBD::mysql handles, or similar, and
> never called $handle->disconnect() on any of them.
>
> What you need to do is write a method that, when called, will make
> sure that all that everything associated with the object it's passed
> is dereferenced. In the main program, you can call it explicitly when
> you're done with each object when you're done with it.
>
> When you call it, the code should look something like this:
>
> foreach (@files) {
> my $obj = yourmod->parse($_) ;
>
> # do something to $obj
>
> $obj->destroy() or warn "can't dereference" ;
> }
>
> HTH
>
> --jay
>
> --
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> <http://learn.perl.org/> <http://learn.perl.org/first-response>
>
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
