Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Add leading zeros to days/months in dates while parsing CSV

by BigRedEO (Acolyte)
on Jun 14, 2016 at 13:59 UTC ( [id://1165581]=perlquestion: print w/replies, xml ) Need Help??

BigRedEO has asked for the wisdom of the Perl Monks concerning the following question:

I have a Perl script to scrub data in CSV files to then be loaded into MySQL Tables. Several of the fields are Date fields that were originally m/d/yyyy - with no leading zeros. I formatted those fields for MySQL using -

$_ = join '-', (split /\//)[2,0,1] for @fields[10,14,24,26];

which worked just fine to load the MySQL tables. But now I'm being told these same CSV files may be used in other depts for things like SQL or other programs and they will need leading zeros. I tried using sprintf, but I'm not quite sure how to use it while parsing nor with a scalar variable. This is what I attempted in place of my original line of code, but get "unitialized value" -

$_ = sprintf '%04d-%02d-%02d', split m:/: for @fields[10,14,24,26];

What am I doing wrong here? (sorry, parsing and scalars get me confused)

Replies are listed 'Best First'.
Re: Formatting dates while parsing CSV
by choroba (Cardinal) on Jun 14, 2016 at 14:27 UTC
    You were almost there. Why did you remove the reordering of the date parts?
    $_ = sprintf '%04d-%02d-%02d', (split m:/:)[2, 0, 1] for @fields[10, 1 +4, 24, 26];

    ($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord }map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,
      I thought the formatting would do the re-ordering for me. However, having just tried this line, it's now telling me "Missing argument in sprintf..."?

        I can't replicate your problem with the above source code and Perl 5.14.

        If you get this problem with a different program, or different input, you will have to show us that program and that input.

        For example, I can provoke that error message by removing an index from the slice after split:

        #!/usr/bin/perl use warnings; use strict; use feature qw{ say }; my @fields = 'a' .. 'z'; @fields[ 10, 14, 24, 26] = ('1/2/2016') x 4; # My change is below $_ = sprintf '%04d-%02d-%02d', (split m:/:)[ 0, 1 ] for @fields[ 10, 1 +4, 24, 26 ]; say join ',', @fields; __END__ Missing argument in sprintf at tmp.pl line 8. Missing argument in sprintf at tmp.pl line 8. Missing argument in sprintf at tmp.pl line 8. Missing argument in sprintf at tmp.pl line 8. a,b,c,d,e,f,g,h,i,j,0001-02-00,l,m,n,0001-02-00,p,q,r,s,t,u,v,w,x,0001 +-02-00,z,0001-02-00

        So, a likely issue is that you did not use the exact code that choroba posted.

Re: Add leading zeros to days/months in dates while parsing CSV
by hippo (Bishop) on Jun 14, 2016 at 14:33 UTC

    It's probably something in that part of the code which you haven't shown us (or in your data). This works fine for me:

    #!/usr/bin/env perl use strict; use warnings; use Test::More tests => 1; my @have = ('1/2/2003', '4/5/2006', '10/11/2012'); my @want = ('2003-01-02', '2006-04-05', '2012-10-11'); $_ = sprintf '%04d-%02d-%02d', (split m:/:)[2,0,1] for @have; is_deeply (\@have, \@want);
Re: Add leading zeros to days/months in dates while parsing CSV
by runrig (Abbot) on Jun 14, 2016 at 16:12 UTC
    I like to validate the format as long as I'm re-formatting (note this does not actually completely validate the dates, see what happens to some invalid end of month dates below):
    use Time::Piece; my @have = ('1/2/2003', '2/31/2014', '4/31/2015', '4/5/2006', '10/11/2 +012'); for my $dt (@have) { my $d = eval { Time::Piece->strptime($dt, '%m/%d/%Y') } or warn "Inv +alid date $dt\n"; next unless $d; my $ymd = $d->ymd(); print "$dt => $ymd\n"; }
    DateTime will validate the date more completely:
    use strict; use warnings; use DateTime::Format::Strptime; my $f = DateTime::Format::Strptime->new( pattern => '%m/%d/%Y', ); my @have = ('1/2/2003', '2/32/2013', '4/31/2014', '4/5/2006', '10/11/2 +012'); for my $dt (@have) { my $d = $f->parse_datetime($dt) or warn "Invalid date $dt\n"; next unless $d; my $ymd = $d->ymd(); print "$dt => $ymd\n"; }

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1165581]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others sharing their wisdom with the Monastery: (6)
As of 2024-04-25 11:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found