[SOLVED] select and process columns of text csv

Issue

I have a csv text file like

2008-01-14T13:38:37.000,10.36,92.7,43.9,C200801141338s,20080114T133837M583Z044
2008-01-16T11:54:44.100,32.35,85.29,12.0,C200801161154d,20080116T115444M589Z012
...

It's easy to output the first (datetime) and last two columns with awk '{print $1,$5,$6}, but I want to reformat the datetimes, like 2008-01-14T13:38:37.000 to 20080114_133837.x. How to make it? Thanks.

Solution

With your shown samples only, could you please try following.

awk '
BEGIN{
  FS=OFS=","
}
{
  gsub(/-|:/,"",$1)
  sub(/T/,"_",$1)
  sub(/\.[0-9]+$/,".x",$1)
  print $1,$5,$6
}
' Input_file

Explanation: Adding detailed explanation for above.

awk '                        ##Starting awk program from here.
BEGIN{                       ##Starting BEGIN section of this program from here.
  FS=OFS=","                 ##Setting FS, OFS as comma here.
}
{
  gsub(/-|:/,"",$1)          ##Globally substituting - OR : with NULL in $1.
  sub(/T/,"_",$1)            ##Substituting T with _ here in $1.
  sub(/\.[0-9]+$/,".x",$1)   ##Substituting .[0-9]+$ at last of 1st field with .x
  print $1,$5,$6             ##Printing 1st, 5th and 6th fields here.
}
' Input_file                 ##Mentioning Input_file name here.

Answered By - RavinderSingh13

Answer Checked By - Robin (WPSolving Admin)