Some ideas<div>a) throw the techs into a hash and use -- if (exists $techhash{$cellObj->{Value}}</div><div>b) dump the excel file into a text file if possible</div><div>c) after b) run the text file thru the unix utility "cut" to grab just the columns you need</div>
<div><br></div><div>The amount of data you are parsing isn't overly large so I suspect that the perl interface to excel you are using is the memory hog.</div><div><br></div><div><br></div><div>--Pat</div><div>On Thu, Jul 23, 2009 at 1:16 PM, John Warner <span dir="ltr"><<a href="mailto:jwarner@texas.net">jwarner@texas.net</a>></span> wrote:</div>
<div><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">In my actual code, I am copying from source file to destination file and the rows are independent of each other. One thought that occurred to me was to not use an array of names using something like the code below. This should reduce this process from an order n^2 operation to an order n operation.<br>
<br>
my @index;<br>
<div class="im"><br>
foreach my $row (2..$LastRow) #skip header row on row 1<br>
{<br>
</div><div class="im"> my $cellObj = $srcSheet->Cells($row,28);<br>
</div> print "Incident tech: $cellObj->{Value} ";<br>
<br>
if ($cellObj->{Value} =~ m/name1|name2|name3/)<br>
{<br>
push $row, @index;<br>
<div class="im"> }else{<br>
print “not a match \n”;<br>
next;<br>
}<br>
}<br>
<br>
</div>foreach my $i (@index)<br>
{<br>
copy target data from source file to destination file<br>
}<br>
<br>
I'll have to test to see what the difference in CPU and memory utilization are once I get into the office. Time to start my day!<br>
<br>
Thanks for all the suggestions!<br>
<div class="im"><br>
<br>
John Warner<br>
<a href="mailto:jwarner@texas.net">jwarner@texas.net</a><br>
</div><div class="im">H: 512.251.1270<br>
C: 512.426.3813<br>
<br>
<br>
<br>
<br>
</div><div><div></div><div class="h5">-----Original Message-----<br>
From: austin-bounces+jwarner=<a href="http://texas.net" target="_blank">texas.net</a>@<a href="http://pm.org" target="_blank">pm.org</a> [mailto:<a href="mailto:austin-bounces%2Bjwarner">austin-bounces+jwarner</a>=<a href="http://texas.net" target="_blank">texas.net</a>@<a href="http://pm.org" target="_blank">pm.org</a>] On Behalf Of <a href="mailto:jameschoate@austin.rr.com">jameschoate@austin.rr.com</a><br>
Sent: Thursday, July 23, 2009 1:04 PM<br>
To: austin<br>
Subject: Re: APM: Perl, Win32 OLE, and Excel<br>
<br>
Besides only processing each sub-set of each row via an input filter --<br>
<br>
Assuming each row is not dependent on the other rows, why use an output array at all? Just write it to a file. The only reason I can see writing this to an array is to keep it in memory for subsequence processing.<br>
<br>
---- Keith Howanitz <<a href="mailto:howanitz@gmail.com">howanitz@gmail.com</a>> wrote:<br>
> On Thu, Jul 23, 2009 at 12:20 PM, John Warner<<a href="mailto:jwarner@texas.net">jwarner@texas.net</a>> wrote:<br>
> > All,<br>
> ><br>
> > I have a project where I am trying to filter through a large amount of data<br>
> > from an Excel spreadsheet. Since I don't have access to the databases where<br>
> > the data actually resides, I have to use a spreadsheet that was given to me.<br>
> > The spreadsheet contains 79 columns and approximately 113k rows. The data<br>
> > are customer satisfaction survey results along with a plethora of other<br>
> > garbage I don't need. I am only interested in a few columns.<br>
> [SNIP]<br>
><br>
> Have you tried putting a simple output when reading the xls file to<br>
> show you how far you are getting in the file before having problems -<br>
> maybe you are really going beyond 113k records, or it is choking on<br>
> unusual data in one particular record.<br>
><br>
> I wonder if you simply saved the xls file as a csv file and used the<br>
> TEXT::CSV_XS module if you would still have troubles.<br>
><br>
> If you want to read the whole thing into memory, you could always read<br>
> each line, put the 3 important fields in array, and then read the next<br>
> line so that you only end up with an array that is 113k x 3 rather<br>
> than the whole spreadsheet in memory.<br>
> _______________________________________________<br>
> Austin mailing list<br>
> <a href="mailto:Austin@pm.org">Austin@pm.org</a><br>
> <a href="http://mail.pm.org/mailman/listinfo/austin" target="_blank">http://mail.pm.org/mailman/listinfo/austin</a><br>
<br>
--<br>
-- -- -- --<br>
Venimus, Vidimus, Dolavimus<br>
<br>
James Choate<br>
<a href="mailto:jameschoate@austin.rr.com">jameschoate@austin.rr.com</a><br>
<a href="mailto:james.choate@twcable.com">james.choate@twcable.com</a><br>
512-657-1279<br>
<a href="http://www.ssz.com" target="_blank">www.ssz.com</a><br>
<a href="http://www.twine.com/twine/1128gqhxn-dwr/solar-soyuz-zaibatsu" target="_blank">http://www.twine.com/twine/1128gqhxn-dwr/solar-soyuz-zaibatsu</a><br>
<a href="http://www.twine.com/twine/1178v3j0v-76w/confusion-research-center" target="_blank">http://www.twine.com/twine/1178v3j0v-76w/confusion-research-center</a><br>
<br>
Adapt, Adopt, Improvise<br>
-- -- -- --<br>
_______________________________________________<br>
Austin mailing list<br>
<a href="mailto:Austin@pm.org">Austin@pm.org</a><br>
<a href="http://mail.pm.org/mailman/listinfo/austin" target="_blank">http://mail.pm.org/mailman/listinfo/austin</a><br>
<br>
<br>
_______________________________________________<br>
Austin mailing list<br>
<a href="mailto:Austin@pm.org">Austin@pm.org</a><br>
<a href="http://mail.pm.org/mailman/listinfo/austin" target="_blank">http://mail.pm.org/mailman/listinfo/austin</a></div></div></blockquote></div><br><br clear="all"><br>-- <br>Pat Ludwig <<a href="mailto:havoc@boldo.com">havoc@boldo.com</a>><br>
AIM: HaVoCPaT <a href="mailto:MSN%3Apludwigtx@hotmail.com">MSN:pludwigtx@hotmail.com</a> <a href="mailto:GTalk%3Ahavoclad@gmail.com">GTalk:havoclad@gmail.com</a> YiM:havoclad<br><br>"Having a public that actually knows something is our best defense against ever again electing a president who knows nothing." -- Bill Maher 5/8/2009<br>
</div>