Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Re^2: csv2xls.pl

by Anonymous Monk
on Sep 05, 2004 at 00:17 UTC ( [id://388544]=note: print w/replies, xml ) Need Help??


in reply to Re: csv2xls.pl
in thread (code) text2xls.pl (was csv2xls.pl)

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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://388544]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others avoiding work at the Monastery: (5)
As of 2024-03-19 08:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found