Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Re^2: csv2xls.pl

by Anonymous Monk
on Sep 05, 2004 at 00:17 UTC ( #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


Comment on Re^2: csv2xls.pl
Download Code

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (8)
As of 2015-07-31 02:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (274 votes), past polls