awk command to convert date format in a file

Question

Given below is the file content and the awk command used:

Input file:in_t.txt

1,ABC,SSS,20-OCT-16,4,1,0,5,0,0,0,0
2,DEF,AAA,20-JUL-16,4,1,0,5,0,0,0,0

Expected outfile:

SSS|2016-10-20,5
AAA|2016-07-20,5

I tried the below command:

awk -F , '{print $3"|"$(date -d 4)","$8}' in_t.txt

Got the outfile as:

SSS|20-OCT-16,5
AAA|20-JUL-16,5

Only thing I want to know is on how to format the date with the same awk command. Tried with

awk -F , '{print $3"|"$(date -d 4)","$8 +%Y-%m-%d}' in_t.txt

Getting syntax error. Can I please get some help on this?

Answer 1

Better to do this in shell itself and use date -d to convert the date format:

#!/bin/bash

while IFS=',' read -ra arr; do
   printf "%s|%s,%s\n" "${arr[2]}" $(date -d "${arr[3]}" '+%Y-%m-%d') "${arr[7]}"
done < file

SSS|2016-10-20,5
AAA|2016-07-20,5

Answer 2

From Unix.com

Just tweaked it a little to suit your needs

awk -v var="20-OCT-16" '
BEGIN{
  split("JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC", month, " ")
  for (i=1; i<=12; i++) mdigit[month[i]]=i
  m=toupper(substr(var,4,3))
  dat="20"substr(var,8,2)"-"sprintf("%02d",mdigit[m])"-"substr(var,1,2) 
  print dat
}'


2016-10-20

Explanation:

Prefix 20 {20}
Substring from 8th position to 2 positions {16}
Print - {-}
Check for the month literal (converting into uppercase) and assign numbers (mdigit) {10}
Print - {-}
Substring from 1st position to 2 positions {20}

Answer 3

What's your definition of a single command? A call to awk is a single shell command. This may be what you want:

$ awk -F'[,-]' '{ printf "%s|20%02d-%02d-%02d,%s\n", $3, $6, (match("JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC",$5)+2)/3, $4, $10 }' file
SSS|2016-10-20,5
AAA|2016-07-20,5

BTW it's important to remember that awk is not shell. You can't call shell tools (e.g. date) directly from awk any more than you could from C. When you wrote $(date -d 4) awk saw an unset variable named date (numeric value 0) from which you extracted the value of an unset variable named d (also 0) to get the numeric result 0 which you then concatenated with the number 4 to get 04 and then applied the $ operator to to get the contents of field $04 (=$4). The output has nothing to do with the shell command date.

Answer 4

This may work for you also.

awk -F , 'BEGIN {months = "  JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC"}
    { num = index(months, substr($4,4,3)) / 3
    if (length(num) == 1) {num = "0" num}
    date = "20" substr($4,8,2) "-" num "-" substr($4,1,2)
    print $3"|" date "," $8}' in_t.txt