shragi
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:
on execution I got the error:
Can't call method "worksheet" on an undefined value at convertexceltocsv.pl line 10
code 2:
Error:
Can't locate Spreadsheet/XLSX.pm in @INC (@INC contains: C:/Dwimperl/perl/site/l
ib C:/Dwimperl/perl/vendor/li b 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,
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;
}
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 = '';
}
}
Error:
Can't locate Spreadsheet/XLSX.pm in @INC (@INC contains: C:/Dwimperl/perl/site/l
ib C:/Dwimperl/perl/vendor/li
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,
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.