Debian Codegraph @

scripts/xlsx2csv

raw
1 #!/usr/bin/perl
2
3 # xls2csv: Convert supported spreadsheet formats to CSV
4 # (m)'24 [2024-12-16] Copyright H.M.Brand 2008-2026
5
6 require 5.008001;
7
8 use strict;
9 use warnings;
10
11 our $VERSION = "3.11";
12 (my $cmd = $0) =~ s{.*/}{};
13
14 use Text::CSV_XS;
15 use List::Util qw( first );
16 use Spreadsheet::Read qw( ReadData row );
17
18 sub usage {
19 my $err = shift and select STDERR;
20 print "usage: $cmd [-A [-N | -J c] | -o file.csv] [-s sep] [-f] [-i] file.xls\n";
21 print " $cmd --help | --man | --info\n";
22 print " --list List supported spreadsheet formats and exit\n";
23 print " -A --all Export all sheets (filename-sheetname.csv)\n";
24 print " -N --no-pfx No filename prefix on -A (sheetname.csv)\n";
25 print " -Z --zip Convert sheets to CSV's in ZIP\n";
26 print " -J s --join=s Use s to join filename-sheetname (-)\n";
27 print " -o f --out=f Set output filename\n";
28 print " -i f --in=f Set input filename\n";
29 print " -f --force Force overwrite output if exists\n";
30 print " -s s --sep=s Set CSV separator character\n";
31 print "Unless -A is used, all other options are passed on to xlscat\n";
32 @_ and print join "\n", @_, "";
33 exit $err;
34 } # usage
35
36 use Getopt::Long qw( :config bundling noignorecase passthrough );
37 GetOptions (
38 "help|?" => sub { usage 0; },
39 "V|version" => sub { print "$cmd [$VERSION]\n"; exit 0; },
40 "man" => sub { pod_nroff (); },
41 "info" => sub { pod_text (); },
42 "list" => sub { list_parsers (); },
43
44 "o|c|out=s" => \ my $csv,
45 "i|x|in=s" => \ my $xls,
46 "f|force!" => \ my $opt_f,
47
48 "s|sep=s" => \ my $opt_s,
49
50 "A|all!" => \ my $opt_A,
51 "N|no-pfx!" => \ my $opt_N,
52 "Z|zip!" => \ my $opt_Z,
53 "J|join=s" => \(my $opt_J = "-"),
54
55 "v|verbose:1" => \(my $opt_v = 0),
56 ) or usage 1;
57
58 sub list_parsers {
59 print "Ext Parser module Req Has Def\n";
60 print "----- ----------------------- ----- ----- ---\n";
61 for (Spreadsheet::Read::parsers ()) {
62 printf "%-5s %-23s %5s %5s %s\n",
63 $_->{ext}, $_->{mod}, $_->{min}, $_->{vsn}, $_->{def} ? "<==" : "";
64 }
65 exit 0;
66 } # list_parsers
67
68 sub pod_text {
69 require Pod::Text::Color;
70 my $m = $ENV{NO_COLOR} ? "Pod::Text" : "Pod::Text::Color";
71 my $p = $m->new ();
72 open my $fh, ">", \my $out;
73 $p->parse_from_file ($0, $fh);
74 close $fh;
75 print $out;
76 exit 0;
77 } # pod_text
78
79 sub pod_nroff {
80 first { -x "$_/nroff" } grep { -d } split m/:+/ => $ENV{PATH} or pod_text ();
81
82 require Pod::Man;
83 my $p = Pod::Man->new ();
84 open my $fh, "|-", "nroff", "-man";
85 $p->parse_from_file ($0, $fh);
86 close $fh;
87 exit 0;
88 } # pod_nroff
89
90 unless ($xls) {
91 foreach my $i (reverse 0 .. $#ARGV) {
92 -f $ARGV[$i] or next;
93 $xls = splice @ARGV, $i, 1;
94 last;
95 }
96 }
97
98 $xls or usage 1, "No input file";
99 -r $xls or usage 1, "Input file unreadable";
100 -s $xls or usage 1, "Input file empty";
101
102 my @known_ext = Spreadsheet::Read::parses (0);
103 @known_ext or @known_ext = qw( csv ods sc sxc xls xlsx );
104 my $valid_ext = do { local $" = "|"; qr{ \. (?: @known_ext )}xi };
105
106 if ($opt_Z) {
107 require Archive::Zip;
108 $@ and die "--zip requires Archive::Zip, which could not be loaded\n";
109 }
110
111 my %e = (n => "\n", t => "\t", e => "\e", r => "\r");
112 $opt_s and $opt_s =~ s/\\+([nter])/$e{$1}/g;
113
114 if ($opt_A || $opt_Z) {
115 $opt_v and warn "Reading $xls ...\n";
116 my $ss = ReadData ($xls) or die "Cannot read/parse $xls\n";
117 my $az = $opt_Z ? Archive::Zip->new : undef;
118 $csv and $xls = $csv;
119 $xls =~ s/$valid_ext $//ix;
120 $csv = Text::CSV_XS->new ({ binary => 1, auto_diag => 1, eol => "\r\n" });
121 $opt_s and $csv->sep_char ($opt_s);
122 foreach my $si (1 .. $ss->[0]{sheets}) {
123 my $s = $ss->[$si] or next;
124 $s->{maxcol} or next;
125 my $mr = $s->{maxrow} or next;
126 my $sn = $s->{label} || "sheet-$si";
127 $sn =~ s/\s+$//;
128 $sn =~ s/^\s+//;
129 $sn =~ s/[^-\w.]+/_/g; # remove any special chars from worksheet name
130 if ($opt_Z) {
131 open my $fh, ">:encoding(utf-8)", \my $data;
132 $csv->print ($fh, [ row ($s, $_) ]) for 1 .. $mr;
133 close $fh;
134 $az->addString ($data, "$sn.csv");
135 next;
136 }
137 my $fn = $opt_N ? "$sn.csv" : "$xls$opt_J$sn.csv";
138 -f $fn && !$opt_f and die "$fn already exists\n";
139 warn "Saving sheet to $fn ...\n";
140 open my $fh, ">:encoding(utf-8)", $fn or die "$fn: $!\n";
141 $csv->print ($fh, [ row ($s, $_) ]) for 1 .. $mr;
142 close $fh;
143 }
144 if ($opt_Z) {
145 my $fn = "$xls.zip";
146 -f $fn && $opt_f and unlink $fn;
147 -f $fn and die "$fn already exists\n";
148 $az->writeToFileNamed ($fn) == 0 or die "$fn: $!\n";
149 }
150 exit;
151 }
152
153 $csv or ($csv = $xls) =~ s/$valid_ext $/.csv/xi;
154 if (-f $csv) {
155 $opt_f or die "$csv already exists\n";
156 unlink $csv;
157 }
158
159 warn "Converting $xls to $csv ...\n";
160 open STDOUT, ">", $csv or die "$csv: $!\n";
161 $^O eq "MSWin32" and $xls = qq{"$xls"};
162 $opt_s and unshift @ARGV => "--sep=$opt_s";
163 $opt_v and unshift @ARGV => "--verbose=$opt_v";
164 exec { "xlscat" } "xlscat", "-c", @ARGV, $xls;
165
166 __END__
167
168 =pod
169
170 =head1 NAME
171
172 xlsx2csv - convert spreadsheet to CSV(s)
173
174 =head1 SYNOPSIS
175
176 xlsx2csv [ -A [ -N || -J str ] | -o file.csv ] [-f] [-i] file.xls
177 xlsx2csv --help | --man | --info
178
179 =head1 DESCRIPTION
180
181 Convert a spreadsheet (all formats supported by L<Spreadsheet::Read>) to CSV
182 (using L<Text::CSV_XS>).
183
184 =head1 OPTIONS
185
186 =over
187
188 =item -?
189
190 =item --help
191
192 Print short usage and exit.
193
194 =item --man
195
196 Print this help using nroff and exit.
197
198 =item --info
199
200 Print this help and exit.
201
202 =item -V
203
204 =item --version
205
206 Print the version and exit.
207
208 =item -f
209
210 =item --force
211
212 Overwrite existing output file.
213
214 =item -A
215
216 =item --all
217
218 Output data from all sheets.
219
220 Each sheet will go to extra file with name built from output CSV-file and
221 sheet name.
222
223 =item -N
224
225 =item --no-pfx
226
227 If specified, dump all will not use output CSV-file but sheet name only.
228
229 =item -J str
230
231 =item --join=str
232
233 If specified, output file names under C<-A> will join on C<str> instead of
234 the default C<->.
235
236 =item -o CSV-file
237
238 =item -c CSV-file
239
240 =item --out=CSV-file
241
242 Output file name (used only with C<< --all >> and without C<< --no-pfx >>).
243
244 Default value is derived from XLS-file.
245
246 =item -i XLS-file
247
248 =item -s XLS-file
249
250 =item --in=XLS-file
251
252 Allows to specify input xls file.
253
254 Default: Last ARGUMENT file that exists.
255
256 =back
257
258 =head1 SEE ALSO
259
260 L<Spreadsheet::Read>, L<Text::CSV_XS>, L<xlscat>
261
262 =head1 AUTHOR
263
264 H.Merijn Brand <[email protected]>
265
266 =head1 COPYRIGHT AND LICENSE
267
268 Copyright (C) 2008-2026 H.Merijn Brand
269
270 This library is free software; you can redistribute it and/or modify
271 it under the same terms as Perl itself.
272
273 =cut