XLSX to CSV conversion

[sasikumar0145]

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.

[Corion]

How did you install Spreadsheet::XLSX? It seems that your installation is broken because Spreadsheet::XLSX lists Spreadsheet::ParseExcel as prerequisite, which in turn lists OLE::Storage_lite. That it is missing means that your installation of it went wrong.

[sasikumar0145]

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

[marto]

"Can't locate OLE/Storage_Lite.pm"

Did you install OLE::Storage_Lite? It should have been installed along with the other modules you're using. A word on post formatting, see How do I post a question effectively?.

[Tux]

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.

[sasikumar0145]

thanks much! will check and get back to you

[Generoso]

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

[Anonymous Monk]

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

[Anonymous Monk]

Dear,
You correction is good, but is a bit incorrect.
Line below is redundant: $line .= "\"".$cell -> {Val}."\",";
The value is printed in next line.

[Anonymous Monk]

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 :)

[Anonymous Monk]

I tried running the above script but it prints only the first line.

[Generoso]

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

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

[marto]

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

[Anonymous Monk]

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