general exception
general exception

Reputation: 4342

Convert date format in last column with awk or other

I have data in a file like this...

405,,,405,15.4,0,04/21/12 14:13:29
402,,,402,15.4,0,04/21/12 14:13:37
404,,,404,15.5,0,2012/04/21 14:14:05
404,,,404,15.4,0,2012/04/21 14:14:11
403,,,403,15.4,0,2012/04/21 14:14:17

I want to convert the last column to a date in YYYY/MM/DD hh:mm:ss format.

Some of the lines are already in that format so they should be ignored.

I have tried the following awk command but it seems slow, and sometimes throws errors

awk -F ',' '{ ("date -d \""$7"\" \"+%Y/%m/%d %T\"") | getline $7; print }' MyFile

This doesn't have to be done in awk. I have any utility available at the standard Debian commandline.

Upvotes: 2

Views: 3060

Answers (2)

Dennis Williamson
Dennis Williamson

Reputation: 360425

Since you have tagged your question , I'll assume you have gawk which has some builtin time functions:

awk --re-interval -F, '
    $7 !~ /[[:digit:]]{4}\// {
        split($7, a, "[/ :]");
        strftime("%Y/%m/%d %H:%M:%S", 
            mktime("20" a[3] " " a[1] " " a[2] " " a[4] " " a[5] " " a[6]))}'
    }
    {
        print
    }'

Upvotes: 1

John Zwinck
John Zwinck

Reputation: 249462

I'd use sed for this rather than awk. Something like this:

sed 's@,\(..\)/\(..\)/\(..\) @,20\3/\1/\2 @'

Old answer before question was edited:

You could use tr : / to convert colons to slashes, or maybe you can make awk do the replacement directly. But why you would consider HH/MM/SS a valid format is beyond me.

Upvotes: 5

Related Questions