[Omaha.pm] More 10m hackery - date format conversion in flat files for DB load

[Jay Hannah]

Problem:

You have a database unload file like this:

 > cat in
qweq|qwedqd|12/03/2003|12321|12/01/2002|XXX|
qweq|qwedqd|12/03/2003|12321|12/01/2002|XXX|
...etc...

But you need those pesky date fields to look like this so MS-SQL will 
load it (bcp):

qweq|qwedqd|2003-12-03 00:00:00|12321|2002-12-01 00:00:00|XXX
qweq|qwedqd|2003-12-03 00:00:00|12321|2002-12-01 00:00:00|XXX
...etc...


Solutions:

A hard coded solution to convert the first date only:
=======================================================
#!/usr/bin/perl

open (IN, "tndns_dly_arr.unl");
while (<IN>) {
    chomp;
    @l = split /\|/;
    @d = split /\//, $l[2];
    $l[2] = sprintf("%04d-%02d-%02d 00:00:00", @d[2,0,1]);
    print join "|", @l;
    print "|\n";
}
close IN;
=======================================================


An argument based version that accepts an arbitrary array indicating 
which fields should be converted:
=======================================================
 > cat j.pl

while (<STDIN>) {
    chomp;
    $line = $_;
    foreach (@ARGV) {
       $line = conv_date($line, $_);
    }
    print "$line\n";
}

sub conv_date {
    my ($line, $pos) = @_;
    @l = split /\|/, $line;
    @d = split /\//, $l[$pos];
    $l[$pos] = sprintf("%04d-%02d-%02d 00:00:00", @d[2,0,1]);
    my $ret = join "|", @l;
    return $ret;
}
=======================================================

You'd use the param version like this:

 > cat in | perl j.pl 2 4
qweq|qwedqd|2003-12-03 00:00:00|12321|2002-12-01 00:00:00|XXX
qweq|qwedqd|2003-12-03 00:00:00|12321|2002-12-01 00:00:00|XXX
 >


(
   Then load your converted data into MS-SQL w/ bcp:
   bcp dss.dbo.tndns_dly_arr in t.unl -U sa -c -t "|" -r "|\n" -m 1000000
)

j