2

I am processing a CSV file with GAWK that has a field with a timestamp formatted like this "18-APR-22 11:00:00". I would like to format the date like this in the output of awk "2022-04-18 11:00:00". I was doing this with the "date -d" and getline. As in the following example.

awk -v FS="," -v OFS="," '
{
  tmp = "date -d \""$1"\" +\"%F %T\""
  tmp | getline var
  close(tmp)
}
{split(FILENAME, arr, ".")}
{print NR, arr[1], tmp, $4, $7, $8, $9}
' 13003.ARR > test.csv

While this works it is very slow with large files. Is there a better way to take the timestamp field and change its format?

Improve this question
4
  • 4
    This is what you should do: stackoverflow.com/questions/64123961/… you have to modify the months string, lowercase to uppercase, and to print what you want.
    – thanasisp
    Apr 18 at 21:33
  • 1
    Running date every time you want to convert "18-APR-22" to "2022-04-18" is a suboptimal method. Treat it as a simple string manipulation. Split the string on the -s. Then, 18->18 is easy, as is 22->2022. APR->04 is table lookup away
    – waltinator
    Apr 18 at 23:47
  • 1
    To slightly re-cast @thanasisp comment: Read the fine manual; in this case the gawk manual, but as it states, mawk has also incorporated the Time functions.
    – Seamus
    Apr 19 at 0:42
  • 1
    Using time functions is overkill for this problem: the same translation of 22 -> 2022 and APR -> 04 will still be required to call mktime().
    – glenn jackman
    Apr 19 at 2:26

2 Answers 2

Reset to default
2

Thank you Thanasisp. Your comment was spot on. I am using the following now and it is thousands of times faster. A csv with 240,000 records runs in three seconds.

awk -v FS="," -v OFS="," '{
  split(FILENAME, fname, ".")
  split($1, date_time, " ")
  split(date_time[1], date, "-")
  print NR, name[1], "20" date[3] "-" sprintf("%02d", (match("JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC", date[2]) + 2) / 3) "-" date[1] " " date_time[2], $4, $7, $8, $9
}' 13003.ARR > test.csv
Improve this answer
1

Assuming the date field to be rearranged is the first , you can do as shown: also you can change the date field index on the awk command line as shown here.

awk \
  -v dtFldIdx=1 \
  -v century="$(date '+%C')" \
  -v m=";$(LC_ALL=C locale mon)" \
'
BEGIN {
  FS = OFS = ","
  for (i=1; match(m,/;/); i += sub(/;/,"",m))
    a[toupper(substr(m,RSTART+1,3))] = i
  f="%s-%s-%s %%s"; g="%02d"
  fmt = sprintf(f,g g,g,g)
}
{split(FILENAME,arr,".")}
{
  # transform the date field
  split($(dtFldIdx),d,/[-[:blank:]]+/)
  day=d[1]
  mon=a[toupper(d[2])]
  yy=d[3]
  hhmmss=d[4]
  var = sprintf(fmt,century,yy,mon,day,hhmmss)

print NR, arr[1], var, $4, $7, $8, $9}
' file.csv
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.