Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
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/s +ite_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/Spre +adsheet/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.

Replies are listed 'Best First'.
Re: XLSX to CSV conversion
by Corion (Patriarch) 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 (Cardinal) on Oct 08, 2012 at 09:02 UTC
Re: XLSX to CSV conversion
by Tux (Canon) on Oct 08, 2012 at 08:55 UTC
    1. Format your code with <code> tags
    2. Have a look at Spreadsheel::Read, xls2csv and xlsx2csv.

    edit 2020-05-27: corrected links to github and added xlsx2csv.


    Enjoy, Have FUN! H.Merijn

      thanks much! will check and get back to you

Re: XLSX to CSV conversion
by Generoso (Prior) 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.

      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.
Re: XLSX to CSV conversion
by Generoso (Prior) 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.

      I run this code on Linux platform and redirect to next parser script , etc...

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
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 contemplating the Monastery: (2)
As of 2022-05-18 00:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (68 votes). Check out past polls.

    Notices?