If all else fails, you might have to use XML::Twig and do the XML parsing of the XLSX sheet yourself. This will mean taking much of the extraction code of Spreadsheet::XLSX and remunging it into a callback-based interface (like Spreadsheet::ParseExcel)...

First up thanks a lot for the ideas! So there is no way around rewriting the code to overcome this out-of-memory error? I mean as a quick fix?

Not trying to be lazy; I ask as this is holding Production already - if I could do something to get this piece going in the interim, I could take time out to rewrite, test and promote the rewritten version.

Any ideas??

ulimit -a shows the following -

fdrread@laatdm62:/home/fdrread> ulimit -a
time(seconds)        unlimited
file(blocks)         unlimited
data(kbytes)         unlimited
stack(kbytes)        4194304
memory(kbytes)       unlimited
coredump(blocks)     unlimited
nofiles(descriptors) unlimited
[download]

Again, THANKS A TON!

        $mstr =~ s/<t\/>/<t><\/t>/gsm;  # this handles an empty t tag 
+in the xml <t/>
[download]

Actually such regular expressions on huge strings usually cause out-of-memory.

I can not think of any better advice, other than updating this module and then proposing changes to author.

Regards,
Vadim.

While parsing general XML with regular expressions can be fragile, parsing very specific XML can work well with regular expressions. But as the parser still seems to generate the complete data structure in memory instead of nibbling at columns and calling a callback whenever data is available, an event-based rewrite is still necessary.

I agree.

Let me share my experience on a problem that OP raised.
Usually at work, when I need some transformation, I often write small and quick program that slurps entire file and uses regexp to produce a result.
this works and I use such a program from time to time.
then, this program fed with a file of ten or hundred Mbytes (which is no more rare nowadays) and I go to my initial program and optimize it so it could rpocess such files.

Actually - I think - it is quite common situation.

There's no need to write a very optimal and highly structured program from the very beginning - "dirty hack" suffice as a first step, and then incremental improvements could be made, if needed - the point - they could never be needed :)
Fortunately, perl is powerful enough to allow such an approach.

Ok - I am looking at XLSX to see if I can make changes to remove stuff I dont need - since all i am trying to do in the production code is read the cell value and write it out to csv file

Can I eliminate all that excel style processing?

        my $member_styles = $self -> {zip} -> memberNamed ('xl/styles.
+xml');

        my @styles = ();

    my %style_info = ();

        if ($member_styles) {

                foreach my $t ($member_styles -> contents =~ /xf\ numF
+mtId="([^"]*)"(?!.*\/cellStyleXfs)/gsm) { #"
                       # $t = $converter -> convert ($t) if $converter
+;
                        push @styles, $t;

                }
        my $default = $1 || '';

        foreach my $t1 (@styles){
            $member_styles -> contents =~ /numFmtId="$t1" formatCode="
+([^"]*)/;
            my $formatCode = $1 || '';
            if ($formatCode eq $default || not($formatCode)){
                if ($t1 == 9 || $t1==10){ $formatCode="0.00000%";}
                elsif ($t1 == 14){ $formatCode="m-d-yy";}
                else {
                    $formatCode="";
                }
            }
            $style_info{$t1} = $formatCode;
            $default = $1 || '';
        }

        }
[download]

Also, I am considering reducing the cell object size by removing format and type hash keys So change

                }
                my $cell =Spreadsheet::ParseExcel::Cell->new(

                    Val    => $v,
                    Format => $thisstyle,
                    Type => $type
                    
                );
[download]

TO

                }
                my $cell =Spreadsheet::ParseExcel::Cell->new(

                    Val    => $v
                    
                );
[download]

Please let me know what you think

I think this way.

given that all you need is to get XLSX data - probably to get some kind of DB values - then, just do not use this CPAN module, just extract your data yourself,
be your-application-centric.

But before doing that, drop a letter to CPAN author - it could be that he is responsive and will provide you with a solution soon.
Otherwise - just reuse his code of "unzipping" the content, and then use your own regular expression.

But better than that - feed you resulting XML string into properly constructed XPATH expression - and feed this XPATH expression to Xml::LibXML - it is very efficient on XPath expressions, but other modules dealing with XPath also will suffice.
(I - personally - have good experience with mentioned one, and TIMTOWTDI)

This would be best way out of this situation - this is how I feel it.

Is 40Mb - a size of ZIPped XLSX, or it is a size after unpacking?

Also, wanted to share - I can see the perl routine process RSS grows to 2GB when the Out of memory is issued -

fdrread@laatdm62:/data/fdr/scripts/irp> ps v 790630
     PID    TTY STAT  TIME PGIN  SIZE   RSS   LIM  TSIZ   TRS %CPU %ME
+M COMMAND
  790630  pts/0 A     0:18    0 1450840 1452752    xx  1052  1912  9.0
+  8.0 perl ./
fdrread@laatdm62:/data/fdr/scripts/irp> ps v 790630
     PID    TTY STAT  TIME PGIN  SIZE   RSS   LIM  TSIZ   TRS %CPU %ME
+M COMMAND
  790630  pts/0 A     0:20    0 1475716 1477628    xx  1052  1912  9.1
+  8.0 perl ./
fdrread@laatdm62:/data/fdr/scripts/irp> ps v 790630
     PID    TTY STAT  TIME PGIN  SIZE   RSS   LIM  TSIZ   TRS %CPU %ME
+M COMMAND
  790630  pts/0 A     0:39    0 2050400 2052312    xx  1052  1912  9.5
+ 11.0 perl ./
fdrread@laatdm62:/data/fdr/scripts/irp> Out of memory!
[download]

I remember John had provided the cell handler function that reduced the size of the cell object). Is there something similar available for Spreadsheet::XLSX?

Patch posted to RT. Cheers! (Now, does anyone know if the author is still around and maintaining the library?).

I was hoping this will help your review in providing a fix. I noticed the module fails trying to read the entire XML worksheet content at the biggest worksheet in my spreadsheet with 1 million rows in it -

<font size="6">
        foreach ($member_sheet -> contents =~ /(\<.*?\/?\>|.*?(?=\<))/
+g) {

</font>
[download]

Can we change this go line-by-line? or in chunks? not sure I totally understand how this works.

I am in pretty bad shape gurus ... This is production being held up at our shop - and I am on the hook for this .. (going with PERL) ... could you please help me figure a way out here? I would truly appreciate it .. I feel like I have hit the roof here with my understanding .. cant figure how I would improve the following code that is trying to cache the whole unzipped worksheet in XML form into an array -

        foreach ($member_sheet -> contents =~ /(\<.*?\/?\>|.*?(?=\<))/
+g) {
[download]

This the point where the program runs out of memory trying to read a 1 million record file Leon

For what it's worth, we hit exactly the same problem yesterday: ie. realising that Spreadsheet::XLSX just isn't fit for purpose with large files.

On looking at the XLSX.pm code, it became clear it's all a bit of a hack (as noted in various posts above): XML is not parsed properly, inefficient use of regexes, apparent problems with extracting special chars ... even if all that worked OK, it then tries to write the entire spreadsheet in-memory into a Spreadsheet::ParseExcel::Workbook object, which itself would slurp up huge amounts of memory, if it managed to get that far.

To be fair to the author, he does describe the module as "quick and dirty", and I guess that's the down-side of CPAN: production-quality is not guaranteed. Lesson we've learnt: when assessing that module originally, we should've tested it on a large file up front. Doh.

So, first we're going to test 'SimpleXlsx' (which is also on CPAN) to see if that's any better: not yet tried, but it does claim to parse the XML properly. And if that's no good, we're going to have to roll our own.

For the record: 'SimpleXlsx' now checked, and no better on big files. So, it seems there is no module on CPAN which can parse a large XLSX file :-(

Will need to write a better version then, unless OP has already found a solution?

Sorry for the poor formatting! Hopefully this helps - please let me know if there is a way out. SOS!!!!!