Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Re: (code) text2xls.pl (was csv2xls.pl)

by Anonymous Monk
on Nov 12, 2002 at 10:44 UTC ( #212240=note: print w/ replies, xml ) Need Help??


in reply to (code) text2xls.pl (was csv2xls.pl)

Hi,

I found your script very useful. I'm using it now in our
production system. In fact I added some formatting code to
fit my needs. Why I write is one problem I had with the
originally code, that was your use of split. I cannot say
what is less useful to split a comma separated list as
split, I changed that and use the Module Text::ParseWords
and the quotewords() function now.

while (<CSVFILE>) { chomp; my @field = quotewords("$option{delim}", 0, $_); my $column = 0; ...

This does a perfect job for me, so even if your tokens are
including commas it gets parsed correctly.

Regards,

Gerrit
--
...forgotten my perlmonks login...


Comment on Re: (code) text2xls.pl (was csv2xls.pl)
Download Code
Re: Re: (code) text2xls.pl (was csv2xls.pl)
by Siebenschlaefer (Initiate) on Apr 08, 2003 at 11:58 UTC
    Found it now again, I'm perlmonk 'Siebenschlaefer'. Ciao, Gerrit
      This work very good, but not have support encoding for input text. I made some changes:
      #open (CSVFILE, $option{csvin}) or die "Error opening $option{csvin}
      open CSVFILE, '<:encoding(cp1251)', $option{csvin} or die "Error opening $option{csvin} +: $!";
      May be Siebenschlaefer can add this feature as swith "-e encoding" ?

        i just update it to provide the encoding command line parameter.

        #!/usr/bin/perl -w # text2xls.pl # pod at tail use strict; use Spreadsheet::WriteExcel; use Getopt::Long; # Crank it up print("\nStarting $0\n"); # Get command line switches and arguments my %option = ('defaultdelim' => ',', 'defaultencoding' => 'utf16-le', ); GetOptions( 'infile=s' => \$option{csvin}, 'outfile=s' => \$option{xlsout}, 'delim=s' => \$option{delim}, 'header!' => \$option{header}, 'encoding=s' => \$option{csvenc}, ); 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{csvenc}) { $option{csvenc} = $option{defaultencoding}; } # Do the dirty work open CSVFILE, "<:encoding(".$option{csvenc}.")", $option{csvin} or die + "Error opening $option{csvin} +: $!"; my $workbook = Spreadsheet::WriteExcel -> new($option{xlsout}); my $worksheet = $workbook -> addworksheet(); my $row = 0; while (<CSVFILE>) { chomp; my @field = split("$option{delim}", $_); my $column = 0; foreach my $token (@field) { $worksheet -> write($row, $column, $token); $column++; } $row++; } # 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 ',' -e or --encoding encoding_format default delimiter is 'cp1252' -h or --header Accepts no arguments. Bolds and background colors contents of topmost row. Example: csv2xls.pl -i file1.csv -o file2.xls -d * -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 ',' -e or --encoding encoding_format default delimiter is 'cp1252' -h or --header Accepts no arguments. Bolds and background colors contents of topmost row. Example: text2xls.pl -i file1.csv -o file2.xls -d * -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 2010-05-15 18:56 Added support of encoding (FVC) 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
Node Status?
node history
Node Type: note [id://212240]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (8)
As of 2015-07-05 20:18 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 (68 votes), past polls