CODEWITHSUNDEEP
linuxbashawk
Jorge Fernandes
Jorge Fernandes

Reputation: 23

Edit field with date in .csv

I have .csv file with lines like this:

xxxx,xxxxxx,Sep  1, 2015 21:32:15.xxxxxxxxx WEST
xxxx,xxxxxx,Sep 28, 2015 23:46:16.xxxxxxxxx WEST
xxxx,xxxxxx,Sep 30, 2015 21:23:17.xxxxxxxxx WEST

and i need to change all date and time to this:

xxxx,xxxxxx,2015-09-01,21:32:15
xxxx,xxxxxx,2015-09-28,23:46:16
xxxx,xxxxxx,2015-09-30,21:23:17

I think it's possible using awk and date but until now nothing worked.

Upvotes: 0

Views: 84

Answers (2)

Ed Morton
Ed Morton

Reputation: 203368

$ cat tst.awk
BEGIN{ FS=OFS=","; mths="JanFebMarAprMayJunJulAugSepOctNovDec" }
{
    split($3,mthDay,/ +/)
    split($4,yrTime,/[ .]/)
    $3 = sprintf("%04d-%02d-%02d", yrTime[2], (match(mths,mthDay[1])+2)/3, mthDay[2])
    $4 = yrTime[3]
    print
}

$ awk -f tst.awk file
xxxx,xxxxxx,2015-09-01,21:32:15
xxxx,xxxxxx,2015-09-28,23:46:16
xxxx,xxxxxx,2015-09-30,21:23:17

Upvotes: 1

glenn jackman
glenn jackman

Reputation: 246799

Some perl:

perl -MTime::Piece -F, -lape '
  ($d2, $d1) = (pop(@F), pop(@F));
  $d2 =~ s/\..*//;
  push @F, Time::Piece->strptime($d1.$d2, "%b %e %Y %T")->strftime("%Y-%m-%d,%T");
  $_ = join ",", @F;
' file

That removes the last 2 comma-separated fields from each line, deletes the fractional time and timezone, reformats the time and prints.

Upvotes: 0

Related Questions