Skip to content
Advertisement

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.

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement