2 # txt2xls turns text files into excel workbooks, and is released
3 # under the terms of the GPL version 2, or any later version, at your
4 # option. See the file README and COPYING for more information.
5 # Copyright 2008 by Don Armstrong <don@donarmstrong.com>.
6 # $Id: perl_script 1153 2008-04-08 00:04:20Z don $
17 txt2xls - Turns a (set of) text file(s) into an excel workbook
24 --tsv, -t tab separated value mode (Default)
25 --ssv, -s space separated value mode
26 --csv, -c comma separated value mode
27 --r-mode, -r R mode (Default)
28 --debug, -d debugging level (Default 0)
29 --help, -h display this help
30 --man, -m display manual
38 Debug verbosity. (Default 0)
42 Display brief useage information.
59 use Spreadsheet::WriteExcel;
61 my %options = (debug => 0,
73 'remove_name|remove-name=s@',
74 'debug|d+','help|h|?','man|m');
76 pod2usage() if $options{help};
77 pod2usage({verbose=>2}) if $options{man};
79 $DEBUG = $options{debug};
82 if (0 == grep {exists $options{$_}} qw(tsv ssv csv)) {
85 if (1 < grep {exists $options{$_}} qw(tsv ssv csv)) {
86 push @USAGE_ERRORS,"You can only pass one of --tsv, --ssv, or --csv";
89 pod2usage(join("\n",@USAGE_ERRORS)) if @USAGE_ERRORS;
91 my @columns = ('A'..'Z','AA'..'ZZ');
94 # we'll use this as a special indicator to read stdin
102 elsif ($options{ssv}) {
106 my $csv = Text::CSV->new({sep_char=>$sep_char});
107 my $wb = Spreadsheet::WriteExcel->new(\*STDOUT);
108 for my $file (@ARGV) {
110 if (not defined $file) {
115 $fh = IO::File->new($file,'r') or
116 die "Unable to open $file for reading: $!";
119 foreach my $remove (@{$options{remove_name}}) {
120 $ws_name =~ s{\Q$remove\E}{}g;
122 $ws_name =~ s{\.[^\.]+$}{}g;
123 $ws_name =~ s/_+/ /g;
124 $ws_name =~ s{[\]:*?\/\] ]+}{ }g;
125 $ws_name =~ s{(?:^\s+|\s+$)}{}g;
126 $ws_name =~ s{^(.{0,31}).*$}{$1};
127 my $ws = $wb->add_worksheet($ws_name) or
128 die "Unable to add worksheet to workbook";
134 die "Unable to parse line $. of $file" unless $csv->parse($_);
138 push @row,$csv->fields();
139 for my $i (0..$#row) {
140 $ws->write($columns[$i].$row,$row[$i]);