PERL Beginners - large files

Author

large files

Bill Peters

2006-01-26, 6:57 pm

Hello,
I hope this question is appropriate to this list:
I am running ActiveState Perl 5.8 on Windows XP with Spreadsheet::ParseExcel
and Spreadsheet::WriteExcel::Big. I have 2 programs I've written using these
modules. One program reads from a SQL DB and just writes the data to a
spreadsheet, about a 20m file, that program works great. The other program
reads from the spreadsheet created by the other program and does some
processing, and writes out to another new spreadsheet, slightly smaller, by
maybe 2m. At the end of this program, I close the spreadsheet
($outBook->close();) then print some data to the screen for the user and
prompt them to hit any key to close the window. Once you hit any key, Perl
grabs a lots of CPU time and slows my PC down to a crawl and continues
running for about half an hour, much longer than it even takes to process
all the data.

Does anyone know how I can prevent this? It doesn't seem like it has to do
with Spreadsheet::WriteExcel::Big because the first program writes the data
to it the same way. The only real difference is that the program with the
problem, runs through the data creating some multidimensional arrays and
comparing some data and replacing some fields and dropping some records.

Thanks in advance for any help
-Bill

Wagner, David --- Senior Programmer Analyst --- WG

2006-01-26, 6:57 pm

Bill Peters wrote:
> Hello,
> I hope this question is appropriate to this list:
> I am running ActiveState Perl 5.8 on Windows XP with
> Spreadsheet::ParseExcel and Spreadsheet::WriteExcel::Big. I have 2
> programs I've written using these modules. One program reads from a
> SQL DB and just writes the data to a spreadsheet, about a 20m file,
> that program works great. The other program reads from the
> spreadsheet created by the other program and does some processing,
> and writes out to another new spreadsheet, slightly smaller, by
> maybe 2m. At the end of this program, I close the spreadsheet
> ($outBook->close();) then print some data to the screen for the user
> and prompt them to hit any key to close the window. Once you hit any
> key, Perl grabs a lots of CPU time and slows my PC down to a crawl
> and continues running for about half an hour, much longer than it
> even takes to process all the data.=20
>=20
> Does anyone know how I can prevent this? It doesn't seem like it has
> to do with Spreadsheet::WriteExcel::Big because the first program
Correct. It is not Big, but the ParseExcel. I had 3 huge excel spreadsheet=
s coming across and I needed the first line only to determine what the file=
was ( the filenames are a random mixture of letters and numbers, but the u=
ser watned a std naming convention). It would take forever to get the first=
line. Until someone on the list gave me some code which had a cell handler=
which aborted out as soon as I got the first line of processing which is a=
ll I needed. It took me 8 seconds to open and read one line from 3 files. S=
ounds like a long time, but without the handler, we are talking minutes and=
not seconds.

If you need to process all the data within, then you may want to rethink h=
ow you do it via ParseExcel and handle external to Excel processing before =
rebuilding the data.=20

Wags ;)

> writes the data to it the same way. The only real difference is that
> the program with the problem, runs through the data creating some
> multidimensional arrays and comparing some data and replacing some
> fields and dropping some records.=20
>=20
> Thanks in advance for any help
> -Bill

****************************************
***************
This message contains information that is confidential
and proprietary to FedEx Freight or its affiliates.
It is intended only for the recipient named and for
the express purpose(s) described therein.
Any other use is prohibited.
****************************************
***************

jmcnamara@cpan.org

2006-01-27, 3:55 am

> Once you hit any key, Perl grabs a lots of CPU time and slows
> my PC down to a crawl and continues running for about half
> an hour, much longer than it even takes to process
> all the data.

Hi,

As pointed out above this is due to Spreadsheet::ParseExcel. See the
following for an explanation and workaround:

Reducing the memory usage of Spreadsheet::ParseExcel:

http://www.perlmonks.org/index.pl?node_id=379743

John.
--

Sponsored Links