#!/pro/bin/perl
# xls2csv: Convert Microsoft Excel spreadsheet to CSV
# (m)'17 [07-06-2017] Copyright H.M.Brand 2008-2017
use strict;
use warnings;
our $VERSION = "3.3";
(my $cmd = $0) =~ s{.*/}{};
use Text::CSV_XS;
use Spreadsheet::Read qw( ReadData row );
sub usage {
my $err = shift and select STDERR;
print "usage: $cmd [ --all [ --no-pfx ] | -o file.csv ] file.xls\n";
@_ and print join "\n", @_, "";
exit $err;
} # usage
use Getopt::Long qw( :config bundling noignorecase passthrough );
GetOptions (
"help|?" => sub { usage 0; },
"V|version" => sub { print "$cmd [$VERSION]\n"; exit 0; },
"o|c|out=s" => \my $csv,
"f|force!" => \my $opt_f,
"A|all!" => \my $opt_a,
"N|no-pfx!" => \my $opt_N,
) or usage 1;
my $xls;
foreach my $i (reverse 0 .. $#ARGV) {
-f $ARGV[$i] or next;
$xls = splice @ARGV, $i, 1;
last;
}
$xls or usage 1, "No input file";
-r $xls or usage 1, "Input file unreadable";
-s $xls or usage 1, "Input file empty";
if ($opt_a) {
my $ss = ReadData ($xls) or die "Cannot read/parse $xls\n";
$csv and $xls = $csv;
$xls =~ s/\.(csv|xlsx?)$//i;
$csv = Text::CSV_XS->new ({ binary => 1, auto_diag => 1, eol => "\r\n" });
foreach my $si (1 .. $ss->[0]{sheets}) {
my $s = $ss->[$si] or next;
my $mc = $s->{maxcol} or next;
my $mr = $s->{maxrow} or next;
my $sn = $s->{label} || "sheet-$si";
$sn =~ s/\s+$//;
$sn =~ s/^\s+//;
$sn =~ s/[^-\w.]+/_/g; # remove any special chars from worksheet name
my $fn = $opt_N ? "$sn.csv" : "$xls-$sn.csv";
-f $fn && !$opt_f and die "$fn already exists\n";
warn "Saving sheet to $fn ...\n";
open my $fh, ">:encoding(utf-8)", $fn or die "$fn: $!\n";
$csv->print ($fh, [ row ($s, $_) ]) for 1 .. $mr;
close $fh;
}
exit;
}
$csv or ($csv = $xls) =~ s/\.xlsx?$/.csv/i;
if (-f $csv) {
$opt_f or die "$csv already exists\n";
unlink $csv;
}
warn "Converting $xls to $csv ...\n";
open STDOUT, ">", $csv or die "$csv: $!\n";
exec "xlscat", "-c", @ARGV, $xls;