comment on

I'm writing a script that parses certain information out of a series of Excel files. The logic to read the files and grab the data I need works fine. My problem is that I keep running out of memory. I'm running into what appears to be a memory leak that manifests itself when I open more files. I'm processing the files sequentially so I would expect that any memory allocated when I open a file would be freed when I open the next but that's not the case. I'm losing 1-2 Kb of memory with each new file. This hasn't been a problem with other ParseExcel scripts I've written since I was only working on 1 or 2 files. Now I'm dealing with over 800 files and it definitely is a problem.

Various searches have come up with alternative cell handling techniques to limit memory usage when parsing large files. I tried that just to make sure but it had no effect. The files I'm working with aren't that large, there's just a lot of them.

The pertinent sections of the code are listed below

#
# Subroutines
#
sub get_cell_val() {

    my ($src_sheet, $src_row, $src_col) = @_;

    if (defined $src_sheet->{Cells}[$src_row][$src_col]) {

        $_ = $src_sheet->{Cells}[$src_row][$src_col]->Value;

    } else {

        $_ = '';

    }

}

#
# Main Program
#

my @files = <*.xls>;

for my $file (@files) {

    print "Opening DLR $file\n" if $DEBUG;

    my $dlr_num = $file;
    $dlr_num =~ s/.xls//;
    print "DLR Number is $dlr_num\n" if $DEBUG;

    my $src_book = new Spreadsheet::ParseExcel::Workbook->Parse($file)
+;

    my $count = $src_book->{SheetCount};
    print "Sheet Count = $count\n" if $DEBUG;
    $count--;
    my $src_sheet = $src_book->{Worksheet}[$count];

SHEET: for (my $row = 0 ; $row <= 100 ; $row++) {

        for (my $col = 0 ; $col <= 5 ; $col++) {

            my $cell_val = &get_cell_val($src_sheet,$row,$col);

            # Check for disconnected circuits
            if ($cell_val =~ /disco/i) {
                print "$dlr_num has been disconnected\n" if $DEBUG;
                last SHEET;
            }

            # Start looking for related Order Numbers
            if ($cell_val =~ /Circuit Layout Data/i) {

                print "Entering Circuit Layout Data\n" if $DEBUG;

                while ($row <= 100) {

                    my $cell_val2 = &get_cell_val($src_sheet,$row,2);

                    if ($cell_val2 =~ /DWDM/i) {

                        $cell_val2 =~ s/^.*(DWDM....).*$/$1/i;
                        print "We have a WINNER!!! - DLR # $dlr_num re
+ferences $cell_val2\n\n" if $DEBUG;

                    }

                    $row++;

                }

            }

        }

    }

}
[download]

Does anyone have a suggested work-around or fix for this? Or is there a problem with my code that I'm not seeing?

Thanks,

Jack

In reply to A different kind of Spreadsheet::ParseExcel memory problem. by jcoxen

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


Pathologically Eclectic Rubbish Lister
	PerlMonks