Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
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

In reply to Re^2: csv2xls.pl by Anonymous Monk
in thread (code) text2xls.pl (was csv2xls.pl) by ybiC

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others wandering the Monastery: (19)
    As of 2015-07-02 16:37 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 (44 votes), past polls