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

I have a .csv file I'm parsing through, making several changes including reformatting the dates for the .csv file to be used by a MySQL program later. I'm not having a problem formatting DATE, but am not getting a response formatting DATETIME. Here is the section of code I'm running while reading through each line 
while($line = <$FH>) {

# split the fields, concatenate the first three fields,
# and add it to the beginning of each line in the file
    chomp($line);
    my @fields = split(/,/, $line);
    unshift @fields, join '_', @fields[0..2];
    $_ = join '-', (split /\//)[2,0,1] for $fields[14];
    $_ = join '-', (split /\//)[2,0,1] for $fields[20];
    $_ = join '-', (split /\//)[2,0,1] for $fields[23];
    $_ = Time::Piece->strptime($fields[38],'%m/%d/%Y %H:%M')->strftime
+('%Y-%m-%d %H:%M');
    $_ = Time::Piece->strptime($fields[39],'%m/%d/%Y %H:%M')->strftime
+('%Y-%m-%d %H:%M');
    push @data, \@fields;
}

[download]
The Time::Piece lines are not working. I'm not getting any errors, but they are not changing the Date within the DATETIME variable for each record from MM/DD/YYYY 00:00 to YYYY-MM-DD 00:00 as I need. What am I missing here?

Update

I have found the correct way to this - updated below 
while($line = <$FH>) {

# split the fields, concatenate the first three fields,
# and add it to the beginning of each line in the file
    chomp($line);
    my @fields = split(/,/, $line);
    unshift @fields, join '_', @fields[0..2];
    $_ = join '-', (split /\//)[2,0,1] for $fields[14,20,23];
    $_ = Time::Piece->strptime($_,'%m/%d/%Y %H:%M')->strftime('%Y-%m-%
+d %H:%M') for @fields[38,39];
    push @data, \@fields;
}

[download]
Now I've run into another problem for which I'll have to open a separate question!

Replies are listed 'Best First'.
Re: Formatting date while parsing CSV
by fishmonger (Chaplain) on Apr 12, 2016 at 18:36 UTC

    The starting point would be to dump $fields[38] and $fields[39] to verify they hold what you expect.

    You should also create a short test script that just tests/troubleshoots this problem. For example:

    use strict;
use warnings FATAL => 'all';
use Time::Piece;
use Data::Dumper;

my $in_date = '01/02/2003 01:02:03';
my $t = Time::Piece->strptime($in_date, '%m/%d/%Y %H:%M:%S')->strftime
+('%Y-%m-%d %H:%M');

print Dumper $t;

    [download]
[reply]
[d/l]
[select]
Re: Formatting date while parsing CSV
by GotToBTru (Prior) on Apr 12, 2016 at 18:39 UTC

    $_ does not work in those statments like it does in the earlier ones. Without the for there is no aliasing between $_ and $fields[38].

    But God demonstrates His own love toward us, in that while we were yet sinners, Christ died for us. Romans 5:8 (NASB)
[reply]
[d/l]
[select]
Re: Formatting date while parsing CSV
by fishmonger (Chaplain) on Apr 12, 2016 at 18:59 UTC

    This problem is one of the reasons why Borodin (in your SO cross post) suggested to wrap this in a subroutine which accepts 3 args (a ref to the var, the input format, and output format). Doing that will allow you to have a consistent way to make all of the format adjustments.

[reply]
Re: Formatting date while parsing CSV
by Marshall (Canon) on Apr 12, 2016 at 19:08 UTC
    Can you show some example of the date/time strings that you are attempting to use? Also MySQL does have conversion routines. It is not clear to me that your Perl code is even necessary for entry into the DB.
[reply]
      Sorry - I got my answer almost right away, but could not figure out how to quickly delete this post.
[reply]

        Hi BigRedEO,

        Actually, it'd be best if you kept your original question in the root node and put your solution below it so others can learn from your question and the solution! The node How do I change/delete my post? goes into more detail.

        Regards,
        -- Hauke D

[reply]

Back to Seekers of Perl Wisdom