Beefy Boxes and Bandwidth Generously Provided by pair Networks kudra
Don't ask to ask, just ask
 
PerlMonks  

XLSX to CSV conversion

by sasikumar0145 (Initiate)
on Oct 08, 2012 at 08:47 UTC ( #997775=perlquestion: print w/ replies, xml ) Need Help??
sasikumar0145 has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I am new to perl. I wish to convert XLSX to CSV format. When i try using the below perl script, i am getting error like, Please someone help me to resolve the issue. (Note: I installed perl module SpreadSheet::XLSX).

perl -MSpreadsheet::XLSX -e ' $\ = "\n"; $, = ";"; my $workbook = Spreadsheet::XLSX->new()->parse($ARGV[0]); my $worksheet = ($workbook->worksheets())[0]; my ($row_min, $row_max) = $worksheet->row_range(); my ($col_min, $col_max) = $worksheet->col_range(); for my $row ($row_min..$row_max) { print map {$worksheet->get_cell($row,$ +_)->value()} ($col_min..$col_max); } ' orangecart.xlsx >filename.csv

Can't locate OLE/Storage_Lite.pm in @INC (@INC contains: /opt/perl_32/lib/5.8.8/PA-RISC1.1-thread-multi /opt/perl_32/lib/5.8.8 /opt/ perl_32/lib/site_perl/5.8.8/PA-RISC1.1-thread-multi /opt/perl_32/lib/site_perl/5.8.8 /opt/perl_32/lib/site_perl /opt/perl_32/lib/ven dor_perl/5.8.8/PA-RISC1.1-thread-multi /opt/perl_32/lib/vendor_perl/5.8.8 /opt/perl_32/lib/vendor_perl .) at /opt/perl_32/lib/site_p erl/5.8.8/Spreadsheet/ParseExcel.pm line 18. BEGIN failed--compilation aborted at /opt/perl_32/lib/site_perl/5.8.8/Spreadsheet/ParseExcel.pm line 18. Compilation failed in require at /opt/perl_32/lib/site_perl/5.8.8/Spreadsheet/XLSX.pm line 14. BEGIN failed--compilation aborted at /opt/perl_32/lib/site_perl/5.8.8/Spreadsheet/XLSX.pm line 14. Compilation failed in require. BEGIN failed--compilation aborted.

Comment on XLSX to CSV conversion
Download Code
Re: XLSX to CSV conversion
by Tux (Monsignor) on Oct 08, 2012 at 08:55 UTC

      thanks much! will check and get back to you

Re: XLSX to CSV conversion
by Corion (Pope) on Oct 08, 2012 at 08:55 UTC

      thanks much. let me check with my installation team and get back to you.

Re: XLSX to CSV conversion
by marto (Chancellor) on Oct 08, 2012 at 09:02 UTC
Re: XLSX to CSV conversion
by Generoso (Priest) on Oct 08, 2012 at 13:05 UTC

    May I ask why are you trying to do this in perl?

    You do know that in excel you can save as CSV.

      It doesn't look as though they're running this on a Microsoft platform. Even if they were, they may not have Excel.

Re: XLSX to CSV conversion
by Generoso (Priest) on Oct 17, 2012 at 15:04 UTC

    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 = ''; } }
      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.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (6)
As of 2014-04-18 03:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (461 votes), past polls