APM: Perl, Win32 OLE, and Excel

John Warner jwarner at texas.net
Thu Jul 23 10:20:19 PDT 2009


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');

#	Source		Destination		Description
#	Column		Column
#		28			3			Responsible
#		55			5			Tech Sat
#		57			6			Overall Sat
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
			#print "not a match \n";

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

More information about the Austin mailing list