Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

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

by Anonymous Monk
on Jun 07, 2007 at 11:29 UTC ( #619788=note: print w/ replies, xml ) Need Help??


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

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" ?


Comment on Re^3: (code) text2xls.pl (was csv2xls.pl)
Re^4: (code) text2xls.pl (was csv2xls.pl)
by Anonymous Monk on May 15, 2010 at 12:12 UTC

    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
      I realize this code has been stagnate for a while, but added a few options (zerolead & forcetext) & wanted to share....
      #!/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' => 'cp1252', ); GetOptions( 'infile=s' => \$option{csvin}, 'outfile=s' => \$option{xlsout}, 'delim=s' => \$option{delim}, 'header!' => \$option{header}, 'encoding=s' => \$option{csvenc}, 'zerolead!' => \$option{zerolead}, 'forcetext=s' => \$option{forcetext}, ); 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}; } unless (defined $option{zerolead}) { $option{zerolead} = 0; } # 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(); if($option{zerolead} == 1) { $worksheet -> keep_leading_zeros(); } my @textcols = split ',', $option{forcetext}; # Load the columns into the hash for search later... my %textcols_h; foreach (@textcols) { $textcols_h{$_} = 1; }; my $row = 0; while (<CSVFILE>) { chomp; my @field = split("$option{delim}", $_); my $column = 0; foreach my $token (@field) { if (exists $textcols_h{ $column+1 }) { $worksheet -> write_string($row, $column, $token); } else { $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 encoding is 'cp1252' -h or --header Accepts no arguments. Bolds and background colors contents of topmost row. -z or --zerolead Accepts no arguments. Leading zeros in values are preserved. -f or --forcetext Accepts comma separated list of columns ie: '1,3,7'. Each column listed will be forced to be text. Example: text2xls.pl -i file1.csv -o file2.xls -d * -h -z 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. -z or --zerolead Accepts no arguments. Leading zeros in values are preserved. -f or --forcetext Accepts comma separated list of columns ie: '1,3,7'. Each column listed will be forced to be text. Example: text2xls.pl -i file1.csv -o file2.xls -d * -h -z -f '8' =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 2011-11-09 10:10 Added support for --zerolead & --forcetext Corrected the default encoding Corrected the Usage text 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://619788]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (11)
As of 2014-12-19 10:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (78 votes), past polls