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

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)

Comment on Add leading zeros to days/months in dates while parsing CSV Select or Download Code

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];` [download] Read more... (604 Bytes) ($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, [download]	[reply] [d/l] [select]
Re^2: Formatting dates while parsing CSV by BigRedEO (Acolyte) on Jun 14, 2016 at 14:35 UTC
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..."?	[reply]
Re^3: Formatting dates while parsing CSV by Corion (Patriarch) on Jun 14, 2016 at 14:44 UTC
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 [download] So, a likely issue is that you did not use the exact code that choroba posted.	[reply] [d/l] [select]
Re^4: Formatting dates while parsing CSV by BigRedEO (Acolyte) on Jun 14, 2016 at 15:16 UTC
Re^5: Formatting dates while parsing CSV by choroba (Cardinal) on Jun 14, 2016 at 15:35 UTC
Some notes below your chosen depth have not been shown here
Re^5: Formatting dates while parsing CSV by choroba (Cardinal) on Jun 14, 2016 at 15:25 UTC
Some notes below your chosen depth have not been shown here
Re: Add leading zeros to days/months in dates while parsing CSV by hippo (Archbishop) 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);` [download]	[reply] [d/l]
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"; }` [download] 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"; }` [download]	[reply] [d/l] [select]