| New file |
|---|
| 0,0 → 1,203 |
|---|
| #!/usr/bin/env perl |
| |
| # csv2xls: Convert csv to xls |
| # (m)'11 [06 Oct 2011] Copyright H.M.Brand 2007-2013 |
| |
| use strict; |
| use warnings; |
| |
| our $VERSION = "1.71"; |
| |
| sub usage |
| { |
| my $err = shift and select STDERR; |
| print <<EOU; |
| usage: csv2xls [-s <sep>] [-q <quot>] [-w <width>] [-d <dtfmt>] |
| [-o <xls>] [file.csv] |
| -s <sep> use <sep> as seperator char. Auto-detect, default = ',' |
| The string "tab" is allowed. |
| -e <esc> use <sep> as seperator char. Auto-detect, default = ',' |
| The string "undef" is allowed. |
| -q <quot> use <quot> as quotation char. Default = '"' |
| The string "undef" will disable quotation. |
| -w <width> use <width> as default minimum column width (4) |
| -o <xls> write output to file named <xls>, defaults |
| to input file name with .csv replaced with .xls |
| if from standard input, defaults to csv2xls.xls |
| -F allow formula's. Otherwise fields starting with |
| an equal sign are forced to string |
| -f force usage of <xls> if already exists (unlink before use) |
| -d <dtfmt> use <dtfmt> as date formats. Default = 'dd-mm-yyyy' |
| -D cols only convert dates in columns <cols>. Default is everywhere. |
| -u CSV is UTF8 |
| -v [<lvl>] verbosity (default = 1) |
| EOU |
| exit $err; |
| } # usage |
| |
| use Getopt::Long qw(:config bundling nopermute passthrough); |
| my $sep; # Set after reading first line in a flurry attempt to auto-detect |
| my $quo = '"'; |
| my $esc = '"'; |
| my $wdt = 4; # Default minimal column width |
| my $xls; # Excel out file name |
| my $frc = 0; # Force use of file |
| my $utf = 0; # Data is encoded in Unicode |
| my $frm = 0; # Allow formula's |
| my $dtf = "dd-mm-yyyy"; # Date format |
| my $opt_v = 1; |
| my $dtc; |
| |
| GetOptions ( |
| "help|?" => sub { usage (0); }, |
| |
| "c|s=s" => \$sep, |
| "q=s" => \$quo, |
| "e=s" => \$esc, |
| "w=i" => \$wdt, |
| "o|x=s" => \$xls, |
| "d=s" => \$dtf, |
| "D=s" => \$dtc, |
| "f" => \$frc, |
| "F" => \$frm, |
| "u" => \$utf, |
| "v:1" => \$opt_v, |
| ) or usage (1); |
| |
| my $title = @ARGV && -f $ARGV[0] ? $ARGV[0] : "csv2xls"; |
| ($xls ||= $title) =~ s/(?:\.csv)?$/.xls/i; |
| |
| -s $xls && $frc and unlink $xls; |
| if (-s $xls) { |
| print STDERR "File '$xls' already exists. Overwrite? [y/N] > N\b"; |
| scalar <STDIN> =~ m/^[yj](es|a)?$/i or exit; |
| } |
| |
| # Don't split ourselves when modules do it _much_ better, and follow the standards |
| use Text::CSV_XS; |
| use Date::Calc qw( Delta_Days Days_in_Month ); |
| use Spreadsheet::WriteExcel; |
| use Encode qw( from_to ); |
| |
| my $csv; |
| |
| my $wbk = Spreadsheet::WriteExcel->new ($xls); |
| my $wks = $wbk->add_worksheet (); |
| $dtf =~ s/j/y/g; |
| my %fmt = ( |
| date => $wbk->add_format ( |
| num_format => $dtf, |
| align => "center", |
| ), |
| |
| rest => $wbk->add_format ( |
| align => "left", |
| ), |
| ); |
| |
| my ($h, $w, @w) = (0, 1); # data height, -width, and default column widths |
| my $row; |
| my $firstline; |
| unless ($sep) { # No sep char passed, try to auto-detect; |
| while (<>) { |
| m/\S/ or next; # Skip empty leading blank lines |
| $sep = # start auto-detect with quoted strings |
| m/["\d];["\d;]/ ? ";" : |
| m/["\d],["\d,]/ ? "," : |
| m/["\d]\t["\d,]/ ? "\t" : |
| # If neither, then for unquoted strings |
| m/\w;[\w;]/ ? ";" : |
| m/\w,[\w,]/ ? "," : |
| m/\w\t[\w,]/ ? "\t" : |
| ";" ; |
| # Yeah I know it should be a ',' (hence Csv), but the majority |
| # of the csv files to be shown comes from fucking Micky$hit, |
| # that uses semiColon ';' instead. |
| $firstline = $_; |
| last; |
| } |
| } |
| $csv = Text::CSV_XS-> new ({ |
| sep_char => $sep eq "tab" ? "\t" : $sep, |
| quote_char => $quo eq "undef" ? undef : $quo, |
| escape_char => $esc eq "undef" ? undef : $esc, |
| binary => 1, |
| keep_meta_info => 1, |
| auto_diag => 1, |
| }); |
| if ($firstline) { |
| $csv->parse ($firstline) or die $csv->error_diag (); |
| $row = [ $csv->fields ]; |
| } |
| if ($opt_v > 3) { |
| foreach my $k (qw( sep_char quote_char escape_char )) { |
| my $c = $csv->$k () || "undef"; |
| $c =~ s/\t/\\t/g; |
| $c =~ s/\r/\\r/g; |
| $c =~ s/\n/\\n/g; |
| $c =~ s/\0/\\0/g; |
| $c =~ s/([\x00-\x1f\x7f-\xff])/sprintf"\\x{%02x}",ord$1/ge; |
| printf STDERR "%-11s = %s\n", $k, $c; |
| } |
| } |
| |
| if (my $rows = $dtc) { |
| $rows =~ s/-$/-999/; # 3,6- |
| $rows =~ s/-/../g; |
| eval "\$dtc = { map { \$_ => 1 } $rows }"; |
| } |
| |
| while ($row && @$row or $row = $csv->getline (*ARGV)) { |
| my @row = @$row; |
| @row > $w and push @w, ($wdt) x (($w = @row) - @w); |
| foreach my $c (0 .. $#row) { |
| my $val = $row[$c] || ""; |
| my $l = length $val; |
| $l > $w[$c] and $w[$c] = $l; |
| |
| if ($utf and $csv->is_binary ($c)) { |
| from_to ($val, "utf-8", "ucs2"); |
| $wks->write_unicode ($h, $c, $val); |
| next; |
| } |
| |
| if ($csv->is_quoted ($c)) { |
| if ($utf) { |
| from_to ($val, "utf-8", "ucs2"); |
| $wks->write_unicode ($h, $c, $val); |
| } |
| else { |
| $wks->write_string ($h, $c, $val); |
| } |
| next; |
| } |
| |
| if (!$dtc or $dtc->{$c + 1}) { |
| my @d = (0, 0, 0); # Y, M, D |
| $val =~ m/^(\d{4})(\d{2})(\d{2})$/ and @d = ($1, $2, $3); |
| $val =~ m/^(\d{2})-(\d{2})-(\d{4})$/ and @d = ($3, $2, $1); |
| if ( $d[2] >= 1 && $d[2] <= 31 && |
| $d[1] >= 1 && $d[1] <= 12 && |
| $d[0] >= 1900 && $d[0] <= 2199) { |
| my $dm = Days_in_Month (@d[0,1]); |
| $d[2] < 1 and $d[2] = 1; |
| $d[2] > $dm and $d[2] = $dm; |
| my $dt = 2 + Delta_Days (1900, 1, 1, @d); |
| $wks->write ($h, $c, $dt, $fmt{date}); |
| next; |
| } |
| } |
| |
| if (!$frm && $val =~ m/^=/) { |
| $wks->write_string ($h, $c, $val); |
| } |
| else { |
| $wks->write ($h, $c, $val); |
| } |
| } |
| ++$h % 100 or $opt_v && printf STDERR "%6d x %6d\r", $w, $h; |
| } continue { $row = undef } |
| $opt_v && printf STDERR "%6d x %6d\n", $w, $h; |
| |
| $wks->set_column ($_, $_, $w[$_]) for 0 .. $#w; |
| $wbk->close (); |
| Property changes: |
|---|
| Added: svn:executable |
|---|
| +* |
|---|
| \ No newline at end of property |
|---|