Link to home
Start Free TrialLog in
Avatar of shragi
shragiFlag for India

asked on

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,
Avatar of FishMonger
FishMonger
Flag of United States of America image

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.
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.
ASKER CERTIFIED SOLUTION
Avatar of wilcoxon
wilcoxon
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial