in reply to Re^10: Sybase BCP date formatter
in thread Sybase BCP date formatter

This website has automatic code wrapping. It seems you didn't use the "download" link to download the code but copy'n'pasted the wrapped code. If you will look at the original node where you pasted the code from, you will note a red + sign, which is the site's way of marking a continued line. Just use the "download" link instead of copy'n'paste.

Replies are listed 'Best First'.
Re^12: Sybase BCP date formatter
by LangeNet (Initiate) on Dec 17, 2008 at 14:55 UTC
    Ah... that's it. It was the wrap. Thank you so much. I changed the delimiter to ',' instead of '|' and it appears the result is correct. However, it converts the time "Jan 28 1997 9:53:22:610AM" to "1997-01-28 09:53:22.610". Is there a way to change the date this to "1 28 1997 9:53AM"? 
    #!/usr/bin/perl

use strict;
use warnings;

{   my %mons = qw( Jan 1 Feb 2 Mar 3 Apr 4 May 5 Jun 6 Jul 7 Aug 8 Sep
+ 9 Oct 10 Nov 11 Dec 12 );
    my $mon_str = join '|', keys %mons;
    my $mon_re = qr/$mon_str/;

    sub fix_bcp_file {
        my ( $file ) = @_;
        local ( $^I, @ARGV ) = ( '', $file );
        while ( <> ) {
            s/(^|\,)($mon_re)\s{1,2}(\d{1,2})\s(\d{4})\s\s?(\d\d?):(\d
+\d):(\d\d):(\d{3})([AP])M(\,|$)/
              $1 .
              sprintf( '%04d-%02d-%02d %02d:%02d:%02d.%03d',
                $4,
                $mons{ $2 },
                $3,
                ( $9 eq 'P' && $5 < 12) ? $5 + 12 : ( $9 eq 'A' && $5 
+== 12 ) ? 0 : $5,
                $6,
                $7,
                $8 ) .
              $10
            /eg;
            print;  # UPDATED thanks runrig
        }
    }
}
for my $file (@ARGV) {
   fix_bcp_file($file);
}

    [download]
    I have around 50 million lines to convert... Thanks so much for your patience and support guys! Robert
[reply]
[d/l]

      I'm sure there is. What have you tried so far?

[reply]
        I'm sure the change is made somewhere in the following part. I've tried this 
              s/(^|\,)($mon_re)\s{1,2}(\d{1,2})\s(\d{4})\s\s?(\d\d?):(\d\d):(\
+d\d):(\d{3})([AP])M(\,|$)/
              $1 .
              sprintf( '%04d %02d %02d %02d:%02d%03d',

        [download]
        I can't figure out where the variable for (A/P)M get get loaded. In my mod the output shows the date as: ,1997 01 28 09:53022, I'm looking for ,1 28 1997 09:53AM,
[reply]
[d/l]

In Section Cool Uses for Perl