perl to convert excel to csv

Hi - I got the below two types of code on quick google search and they seems to solve my problem but each has one issue.

code 1:
#!/usr/bin/perl

use 5.010;
use strict;
use warnings;

use Spreadsheet::ParseExcel;
my $xlsparser = Spreadsheet::ParseExcel->new();
my $xlsbook = $xlsparser->parse('C:\\temp\\testfile.xlsx');
my $xls = $xlsbook->worksheet(0);
my ( $row_first, $row_last ) = $xls->row_range();
my ( $col_first, $col_last ) = $xls->col_range();
my $csv = '/home/Admin/Downloads/ram.csv';
for my $row ( $row_first .. $row_last ) {        # Step through each row
    for my $col ( $col_first .. $col_last ) {    # Step through each column
        my $cell = $xls->get_cell( $row, $col ); # Get the current cell
        next unless $cell;
        $csv .= $cell->unformatted(); # Get the cell's raw data -- no border 
                                      # colors or anything like that
        if ($col == $col_last) {
            $csv .= "\n"; 
        } else {
            $csv .= ","; 
        }
    }
}
open(my $FH ,'>',"$csv") or die "oops!";

while (my $line = <$FH>){
    print $FH $line;
}

Open in new window


on execution I got the error:
Can't call method "worksheet" on an undefined value at convertexceltocsv.pl line 10

code 2:
#!/usr/bin/perl

use 5.010;
use strict;
use warnings;
use Spreadsheet::XLSX;
 
my $excel = Spreadsheet::XLSX -> new ('C:\\temp\\testfile.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 = '';
    }
}

Open in new window


Error:
Can't locate Spreadsheet/XLSX.pm in @INC (@INC contains: C:/Dwimperl/perl/site/l
ib C:/Dwimperl/perl/vendor/lib C:/Dwimperl/perl/lib .) at convertexceltocsv1.pl line
6.
BEGIN failed--compilation aborted at convertXL2Csv.pl line 6.

so I installed
install Spreadsheet::WriteExcel
but the error did not go away.

so is there any way I can convert excel to csv ?
I have one sheet excel not multi sheet.

Thanks,
shragiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FishMongerCommented:
The first error message is telling you that $xlsbook is undefined and that's because line 9 failed to parse the file and since you didn't have any error handling on that statement, you were not informed of that problem when it occurred.  The reason it failed is because the Spreadsheet::ParseExcel module isn't designed to parse xlsx files.  You need to use Spreadsheet::XLSX.

The second error message is telling you that you need to install the Spreadsheet::XLSX module.

The Spreadsheet::WriteExcel module is used to create spreadsheet files, not parse an existing file.
0
FishMongerCommented:
If you need to do this conversion for only a couple files, then I'd just do it directly in Excel.  If you need to do this with a bunch of files, then use Spreadsheet::XLSX to read/parse the spreadsheet and Text::CSV or Text::CSV_XS to create the csv files.
0
wilcoxonCommented:
As Fishmonger said, you need to install Spreadsheet::XLSX (not Spreadsheet::WriteExcel).

Here are some changes to the script you posted (should make it work more correctly)...
#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::XLSX;

my $escape = 'whatever_string_Microsoft_uses_to_escape_double_quote_that_I_can_never_remember';
my $excel = Spreadsheet::XLSX -> new ('C:\\temp\\testfile.xlsx');
foreach my $sheet (@{$excel -> {Worksheet}}) {
    printf("Sheet: %s\n", $sheet->{Name});
    $sheet -> {MaxRow} ||= $sheet -> {MinRow};
    foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {
        my $line = '';
        $sheet -> {MaxCol} ||= $sheet -> {MinCol};
        foreach my $col ($sheet -> {MinCol} ..  $sheet -> {MaxCol}) {
            my $cell = $sheet -> {Cells} [$row] [$col];
            if (defined $cell) {
                chomp $cell;
                $cell->{Val} =~ s{"}{$escape}g;
                $line .= ',"' . $cell -> {Val} . '"';
            } else {
                $line .= ',';
            }
        }
        $line =~ s{^,}{};
        print "$line\n";
    }
}

Open in new window


You just need to change the value of $escape to whatever is correct.  If you don't specifically want a Microsoft CSV file and the cell values can't contain commas, they it's simpler - just remove the $escape define and delete the escape substitution line and get rid of the double quotes on the line assigning to $line.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.