APM: Perl, Win32 OLE, and Excel

[Eric Ellington]

I used to do this a bunch. You mention 133k rows. Excel used to max
out around something like 65k rows. Maybe I am out of date but how is
so much data crammed into a single worksheet?

What packages are you using?

Thanks,

Eric

On Thu, Jul 23, 2009 at 12:20 PM, John Warner<jwarner at texas.net> wrote:
> All,
>
> I have a project where I am trying to filter through a large amount of data
> from an Excel spreadsheet.  Since I don't have access to the databases where
> the data actually resides, I have to use a spreadsheet that was given to me.
> The spreadsheet contains 79 columns and approximately 113k rows.  The data
> are customer satisfaction survey results along with a plethora of other
> garbage I don't need.  I am only interested in a few columns.
>
> My code goes like this...
>
> Create an Excel Object
> Use Object to open Source and Destination spreadsheets
> Find the column and row boundaries of where data is within the source.
>
> my @ArrayOfNames = ('Bill', 'Bob', 'Jane', 'Tom', 'Dick', 'Harry');
>
> #Columns
> #       Source          Destination             Description
> #       Column          Column
> #               28                      3                       Responsible
> Tech
> #               55                      5                       Tech Sat
> Score
> #               57                      6                       Overall Sat
> Score
> #
> foreach my $row (2..$LastRow) #skip header row on row 1
> {
>        #check the responsible tech
>        foreach my $t (@ArrayOfNames)
>        {
>                my $cellObj = $srcSheet->Cells($row,28);
>                print "Current:  $t  \t Incident tech:  $cellObj->{Value} ";
>
>                if ($t =~ m/$srcSheet->Cells($row,28)->{Value}/)
>                {
>                        print "found a match!\n";
>                        if ($srcSheet->Cells($row,55)->{Value} < 7 ||
> $srcSheet->Cells($row,57)->{Value} < 7)
>                        {
>                                #copy data from source to destination
>                        }
>                }else{
>                        #print "not a match \n";
>                        next;
>                }
>        }
> }
>
> My question:  With 113k rows to go through, Perl runs out of memory and the
> processing takes quite a while.  How can I be more efficient?
>
>
> John Warner
> jwarner at texas.net
> H:  512.251.1270
> C:  512.426.3813
>
>
>
>
> _______________________________________________
> Austin mailing list
> Austin at pm.org
> http://mail.pm.org/mailman/listinfo/austin
>



-- 
Eric Ellington
e.ellington at gmail.com