Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Re: XLSX to CSV conversion

by Generoso (Prior)
on Oct 17, 2012 at 15:04 UTC ( #999558=note: print w/replies, xml ) Need Help??


in reply to XLSX to CSV conversion

Maybe this example will help you.

#!/usr/bin/perl use strict; use warnings; use Spreadsheet::XLSX; my $excel = Spreadsheet::XLSX -> new ('d:\\book1.xlsx'); my $line; foreach my $sheet (@{$excel -> {Worksheet}}) { printf("Sheet: %s\n", $sheet->{Name}); $sheet -> {MaxRow} ||= $sheet -> {MinRow}; foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) { $sheet -> {MaxCol} ||= $sheet -> {MinCol}; foreach my $col ($sheet -> {MinCol} .. $sheet -> {MaxCol}) { my $cell = $sheet -> {Cells} [$row] [$col]; if ($cell) { $line .= "\"".$cell -> {Val}."\","; } } chomp($line); print "$line\n"; $line = ''; } }

Replies are listed 'Best First'.
Re^2: XLSX to CSV conversion
by Anonymous Monk on Apr 18, 2013 at 21:53 UTC
    Thank you for this simple yet superbly helpful  example.
    Having finally identified and installed apparently all of the packages dependencies and then combined with this excellent example of yours, I think that it alone has saved me a significant amount of tinkering. This probably has me 50% of the way to the basic conversion task which I had pending.
    
    If I may make an assumption from the nickname above : 
    1) Muito, muito obrigado. Voce nem pode imaginar a enorme ajuda que me acaba de dar aqui.
       or alternatively 
    2) "Muchas gracias" ?
       failing either of the above being applicable 
    3) Thanks a million :)
    
      I tried running the above script but it prints only the first line.
Re^2: XLSX to CSV conversion
by Anonymous Monk on Mar 28, 2019 at 15:06 UTC

    Hi I just spotted something on your code: you are appending an extra comma after processing the last column of each line in the worksheet. This can lead to problems specially if you're using the generated csv file to load data to a database for example. I did the change below to make sure that did not happen. I hope it helps

    #!/usr/bin/perl use strict; use warnings; use Spreadsheet::XLSX; my $excel = Spreadsheet::XLSX -> new ('/tmp/myexcel.xlsx'); my $line; foreach my $sheet (@{$excel -> {Worksheet}}) { printf("Sheet: %s\n", $sheet->{Name}); $sheet -> {MaxRow} ||= $sheet -> {MinRow}; foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) { $sheet -> {MaxCol} ||= $sheet -> {MinCol}; foreach my $col ($sheet -> {MinCol} .. $sheet -> {MaxCol}) { my $cell = $sheet -> {Cells} [$row] [$col]; if ($cell) { $line .= "\"".$cell -> {Val}."\","; $line .= $cell -> {Val}; if ($col != $sheet -> {MaxCol}) #appends the comma onl +y if the column being processed is not the last { $line .= ","; } } } chomp($line); print "$line\n"; $line = ''; } }
      Dear,
      You correction is good, but is a bit incorrect.
      Line below is redundant: $line .= "\"".$cell -> {Val}."\",";
      The value is printed in next line.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (5)
As of 2020-05-31 14:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    If programming languages were movie genres, Perl would be:















    Results (173 votes). Check out past polls.

    Notices?