I have a CSV where the first column has a string that represents datetime. I would like to add an extra column with that datetime converted to epoch.
I tried the following:

awk -F "," 'BEGIN{ OFS="," } {$14=$(date -jf "%m/%d/%Y %H:%M:%S %p" $1 "+%s"); print}’ mycsv.csv > test  

I get:

awk: illegal field $(0%m/%d/%Y %H:%M:%S %p"1/30/2017 11:14:55 AM"+%s), name "(null)"
 input record number 1, file mycsv.csv
 source line number 1  

I know that the date conversion for the field works so I am doing something wrong with the syntax.
How can I do what I want?

  • I retagged your question to include awk and text processing. You had both Linux and OSX in there; I arbitrarily removed OS X -- edit it back in if I am wrong, please. – Jeff Schaller Feb 1 '17 at 11:32
  • 1
    @JeffSchaller I think it ought to be tagged osx as he's using macOS specific date. I'm swapping the linux tag out, or he'll get GNU date answers. – Kusalananda Feb 1 '17 at 11:46
  • Good catch on the date command, Kusalananda - thank you! – Jeff Schaller Feb 1 '17 at 12:25

I have GNU date and hence the command line options are different for me. But you problem seems to be awk's syntax: You can not use the shell subprocess construct $(...) inside awk scripts. You need to you the system() function, which gets passed a string. Therefore you need to construct a valid shell command into an awk string that you can pass to system inside the awk script.

Something like this:

awk -F "," 'BEGIN{ OFS="," } {$14=system("date -jf \"%m/%d/%Y %H:%M:%S %p\" \""$1"\" \"+%s\""); print}' mycsv.csv > test

or for better readablility

awk -F "," '
  BEGIN{ OFS="," }
    $14 = system("date -jf \"%m/%d/%Y %H:%M:%S %p\" \"" $1 "\" \"+%s\"");
  }' \
  mycsv.csv > test
  • There is something wrong with the quotes. Now I get: Failed conversion of ``11/24/2016'' using format ``%m/%d/%Y %H:%M:%S %p'' date: illegal time format usage: date [-jnu] [-d dst] [-r seconds] [-t west] [-v[+|-]val[ymwdHMS]] ... [-f fmt date | [[[mm]dd]HH]MM[[cc]yy][.ss]] [+format] – Jim Feb 1 '17 at 12:45
  • Sorry I can not reproduce this or see the problem right now. Try to paste the awk script into a seperate file (call it foo.awk) and see if an editor with syntax highlighting can help you spot the problem. – Lucas Feb 1 '17 at 13:25
  • Which editor in OSX would that be? In vim nothing is indicated – Jim Feb 1 '17 at 14:16
  • What is the system? If I do man system I get: SYSTEM(3) BSD Library Functions Manual SYSTEM(3) NAME system -- pass a command to the shell – Jim Feb 1 '17 at 17:26
  • This works: awk -F "," 'BEGIN{ OFS="," } {$14=system("date -jf \"%m/%d/%Y %H:%M:%S %p\" \"1/27/2017 10:48:20 AM\" \"+%s\""); print}' but if I replace with $1 it does not. I think the problem is that the $1 already has double quotes. How can I strip them? – Jim Feb 1 '17 at 18:21

If you have GNU awk (which should be available on OSX via brew I think) then you could use the internal mktime and strftime instead of relying on system date.

Unfortunately your input format isn't in the datespec format expected by mktime, so some splitting and rearranging of the time string is required. For example, given

$ cat file.csv 
09/23/2016 11:12:19 AM,field2,field3


gawk -F, '
    split($1,a,/[/: ]/);
    ts = sprintf("%4d %02d %02d %2d %2d %2d", a[3], a[2], a[1], a[7] ~ /^[Pp]/ ? a[4]+12 : a[4], a[5], a[6]); 
    $0 = strftime("%s", mktime(ts)) FS $0
  } 1' file.csv
1510243939,09/23/2016 11:12:19 AM,field2,field3

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.