I've modified the file co use Text::CSV_XS in order to make it work even when there are \n characters inside a field. I've added a default parameter --binary so the program works properly in that cases.
Oscar
#!/usr/bin/perl -w
# text2xls.pl
# pod at tail
#
# Modified by Oscar Fernandez Sierra to support LF inside fields
# (in that case, a row requires several lines from .csv file)
# Changed default delimiter from "," to ";"
use strict;
use Spreadsheet::WriteExcel;
use Getopt::Long;
use Text::CSV_XS;
use IO::File;
use Data::Dumper;
# Crank it up
print("\nStarting $0\n");
# Get command line switches and arguments
my %option = ('defaultdelim' => ';', 'defaultbinary' => 0);
GetOptions(
'infile=s' => \$option{csvin},
'outfile=s' => \$option{xlsout},
'delim=s' => \$option{delim},
'header!' => \$option{header},
'binary!' => \$option{binary},
);
unless (defined ($option{csvin} && $option{xlsout})) {
&USAGE();
exit;
}
unless (defined $option{delim}) {
$option{delim} = $option{defaultdelim};
}
unless (defined $option{header}) {
$option{header} = 0;
}
unless (defined $option{binary}) {
$option{binary} = 1;
}
# Do the dirty work
my $fh = new IO::File;
if (! $fh->open("< " . $option{csvin})) {
die "Error opening $option{csvin}";
}
# "binary" is necessary to process
my $csv = Text::CSV_XS->new({"sep_char" => $option{delim}, "binary" =>
+ $option{binary}});
my $workbook = Spreadsheet::WriteExcel -> new($option{xlsout});
my $worksheet = $workbook -> addworksheet();
my $row = 0;
my $refarray_datos;
# $csv->getline($fh) returns a reference to array that results to sepa
+rate
# in fields the row read from file. If any field in the row contains \
+n, and
# the binary option is on, $csv->getline($fh) will read the right numb
+er of
# lines from the .csv file
my $status; # to check errors
my $error_input; # to check errors
while ( ! $fh->eof() )
{
if ($refarray_datos = $csv->getline($fh))
{
#$status = $csv->status();
#$error_input = $csv->error_input();
#print Dumper($refarray_datos, $status, $error_input);
my @field = @{$refarray_datos};
my $column = 0;
foreach my $token (@field)
{
$worksheet -> write($row, $column, $token);
$column++;
}
$row++;
}
else
{
$status = $csv->status();
$error_input = $csv->error_input();
die "Fatal error !!! Incorrect line (delimited by []):\n[$erro
+r_input]\n(If any field contains LF (\\n), check if --binary can solv
+e the error)\n\n";
}
}
# Prettify row as header
if($option{header} == 1)
{
my $header = $workbook -> addformat();
$header -> set_bold();
$header -> set_align('center');
$header -> set_bg_color('tan');
$header -> set_border();
$worksheet -> set_row(0, undef, $header);
}
# Optional, unless doing some external action against xls file
$workbook -> close() or die "Error closing $workbook: $!";
# Go tell it on the mountain
print(" infile = $option{csvin}\n");
print(" outfile = $option{xlsout}\n");
print("Finished $0\n\n");
########################################################
sub USAGE {
print <<EOF
Required switches:
-i or --infile csv_infilename
-o or --output xls_outfilename
Optional switches:
-d or --delim alt_delimiter
default delimiter is ';'
-h or --header
Accepts no arguments.
Bolds and background colors contents of topmost row.
-b or --binary
default mode is binary (permits LF inside a field).
Use --nobinary do disable binary mode
Example:
csv2xls.pl -i file1.csv -o file2.xls -d [--[no]binary]] * -h
EOF
;
}
########################################################
=head1 Name
text2xls.pl
=head1 Description
Convert text file in csv format to Excel binary format
=head1 Usage
Required switches:
-i or --infile csv_infilename
-o or --output xls_outfilename
Optional switches:
-d or --delim alt_delimiter
default delimiter is ','
-h or --header
Accepts no arguments.
Bolds and background colors contents of topmost row.
-b or --binary
default mode is binary (permits LF inside a field).
Use --nobinary do disable binary mode
Example:
csv2xls.pl -i file1.csv -o file2.xls -d [--[no]binary]] * -h
=head1 Tested
Spreadsheet::WriteExcel 0.31
Getopt::Long 2.19
Perl 5.00503
Debian 2.2r2 "Espy"
Excel 9.0.3821 SR-1
97 SR-2
Win32 5.0.2195 SP-1
4.0 SP-6a
=head1 Updated
2001-04-17 10:45 Renamed as text2xls - avoid conflict with existin
+g csv2xls.
Tweaked todos re feedback from jmcnamara (S::WE a
+uthor).
2001-04-16 Initial working code & posted to PerlMonks
=head1 Todos
Use Text::xSV by tilly or Text::CSV_XS for parsing text infile
Review and fix "prettify row header" which somewhat works, but not qu
+ite right.
Support for "freeze pane" anticipated in future S::WE
Test with different delimiters.
Use Pod::Usage to instead of &USAGE(). But appears to only be in Perl
+ 5.6+ 8^(
Use @headerparms and foreach loop in headers section to reduce duplic
+ation.
Accept multiple infiles.
Save as "file.xls" (provided infile is "file") with -r switch.
Add support for "page delimiter" character for multiple worksheets.
Add support for $VERSION.
=head1 Author
ybiC
=head1 Credits
Thanks to jmcnamara, Petruchio and zdog for suggestions,
and to jmcnamara for excellent pod in Spreadsheet::WriteExcel,
and vroom, of course for PM.
=cut
|