Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
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 (Bishop) on Oct 08, 2012 at 09:02 UTC
Re: XLSX to CSV conversion
by Generoso (Parson) 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 (Parson) 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 exploiting the Monastery: (15)
As of 2015-05-29 14:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    In my home, the TV remote control is ...









    Results (587 votes), past polls