Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Re: Out of memory and While replacements with excel XLSX application

by Weinstar (Initiate)
on Oct 08, 2012 at 22:57 UTC ( #997892=note: print w/ replies, xml ) Need Help??


in reply to Out of memory and While replacements with excel XLSX application

Hello. Thank you to all of you for your reponses. There seems to be a lot to go on and a general sentiment, that this is doable, and with a lot of optimization, so that is great. It will proberbly take me some time to work through it all, but I will post the final update, as soon as i am done, for future reference. Again I thank you for taking your time to look over my problem.


Comment on Re: Out of memory and While replacements with excel XLSX application
Re^2: Out of memory and While replacements with excel XLSX application
by Anonymous Monk on Jul 10, 2014 at 11:08 UTC

    Hi, I'm using Spreadsheet::XLSX to parse the xlsx file. for around 20000 rows , the memory it takes around 800 MB, though size of file is 5 MB. Can anybody helps me to optimize it? following is the code:

    my ($self, $in_file, $out_file) = @_; $count = @_; $count -= 1; if(2 != $count) { util->logit("[$PARENT_PROC_NAME]: xlsx_to_csv:invalid args [@_ +] [$count]"); return $FAILURE; } my $excel = Spreadsheet::XLSX -> new ($in_file); my $line; my $out_csv_name = $out_file; my $orig_out_file = $out_csv_name; #binmode CSV; binmode CSV, ":utf8"; my $Csv = Text::CSV_XS->new({ 'quote_char' => '"', 'escape_char' => '"', 'sep_char' => ',', 'binary' => 1, }); my $i = 1; foreach my $sheet (@{$excel -> {Worksheet}}) { $i++; $out_csv_name = "$orig_out_file$underscore$sheet->{Name}.csv"; #$out_csv_name =~ s/\s+//g; @ret_array[$i] = $out_csv_name; open CSV, "> $out_csv_name" || die "Cannot create csv file: $! +" ; $sheet -> {MaxRow} ||= $sheet -> {MinRow}; foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) { my @Row; $sheet -> {MaxCol} ||= $sheet -> {MinCol}; foreach my $col ($sheet -> {MinCol} .. $sheet -> {MaxCol} +) { my $cell = $sheet -> {Cells} [$row] [$col]; my $Value = ""; if ($cell) { #$Value = $cell->Value; #rounds off #$Value = $cell->{_Value}; #rounds off #$Value = $cell->Value(); #rounds off #$Value = $cell->{Val}; $Value = $cell->unformatted(); #my $avl = $sheet->{Cells}[$row][$col]{Val}; #print "avl:[$avl]\n"; #$Value = $cell->value(); if ($Value eq 'GENERAL') { $Value = $cell->{Val}; } } last if $col == $sheet->{MinCol} and !$Value; push(@Row, $Value); } next unless @Row; my $Status = $Csv->combine(@Row); if (!defined $Status) { my $Error = $Csv->error_input(); warn "ERROR FOUND!: $Error"; } if (defined $Status) { my $Line = $Csv->string(); print CSV "$Line\n"; }

    I don't know how to do it...I'm new to perl. Kindly guide me.

      Can't help you right now (sorry), but as a quick suggestion -- it's best to post your question as a new top-level node in Seekers of Perl Wisdom.
Reaped: Re^2: Out of memory and While replacements with excel XLSX application
by NodeReaper (Curate) on Jul 10, 2014 at 11:20 UTC

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://997892]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (15)
As of 2015-07-02 18:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (44 votes), past polls