0

I am trying to append a column to a csv file that has 13 columns.
I am executing the following command:

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

The result is that at the end of 13 field the following is appended ^M,1 (or ^M,0 and the new column's value which is supposed to be the $14 is added to a second line.
So the csv breaks.
What is the problem here?

Example input:

"1/30/2017 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","jim@gmail.com","A","6.0.12","A","Now"  

Expected output:

"1/30/2017 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","jim@gmail.com","A","6.0.12","A","Now", 1485771295  

UPDATE:
This:

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

Does not have the 0 or 1 from the exit status of system but has the ^M and new line characted and hence the $14 is printed in a new line

UPDATE 2:

$ cat mycsv.csv 
"1/30/2017 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","jim@gmail.com","A","6.0.12","A","Now"  
"1/30/2017 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","jim@gmail.com","A","6.0.12","A","Now"  
"1/30/2017 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","jim@gmail.com","A","6.0.12","A","Now"    

Doing:

$ awk -F"," '{printf; printf ","; gsub(/"/, "", $1);system("date -jf \"%m/%d/%Y %H:%M:%S %p\" \""$1"\" \"+%s\"")}' mycsv.csv > test  
Failed conversion of ``1/30/2017 11:14:55 AM'' 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]    

And result file:
When I do cat I see:

$ cat test
,1485771295 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","jim@gmail.com","A","6.0.12","A","Now"  
,148577129511:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","jim@gmail.com","A","6.0.12","A","Now"    

When I open with vim I see a ^M after "Now"

UPDATE 3:
Something strange, I can't print the full line. The following gives garbage:

$awk -F',' '{ printf $0 }' mycsv.csv   
","M","k6.0.1","E","jim","JimK","JIM","jim@gmail.com","A","6.0.12","A","Now""  

But this works (but I don't want the new line that print adds):

$ awk -F',' '{ print $0; }' mycsv.csv   
"1/30/2017 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","jim@gmail.com","A","6.0.12","A","Now"  
"1/30/2017 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","jim@gmail.com","A","6.0.12","A","Now"  
"1/30/2017 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","jim@gmail.com","A","6.0.12","A","Now"  
2

From awk(1) we read that

   system(cmd)
          executes cmd and returns its exit status

So the 0 is the exit status returned by the result of the system command (a normal exit). In this case you probably need to print the existing columns, a comma, then do the date so the standard out of that gets put in the right place, as a simplified example shows:

(echo a,b,c; echo e,f,g) \
| awk -F, '{printf "%s", $0;printf ",";system("echo more")}'

Appending a new column with input from the first column is not very different: print the data, append a comma, alter $1 for the subsequent system, make the system call.

$ cat input 
"1/30/2017 11:14:55 AM","I","M"
$ awk -F, '{printf "%s", $0;printf ",";gsub(/"/, "", $1);system("date -jf \"%m/%d/%Y %H:%M:%S %p\" \""$1"\" \"+%s\"")}' input
"1/30/2017 11:14:55 AM","I","M",1485774895
  • In my case I need to modify the first field of the csv row with system. I am not sure how I can reformat it based on your example – Jim Feb 1 '17 at 20:50
  • Reforming and appending are two very different things. Do you have sample input and expected output? – thrig Feb 1 '17 at 20:54
  • I mean you are doing system("echo more"). I am doing system(date(...$1)) – Jim Feb 1 '17 at 20:55
  • I tried this and does not work: for i in $(cat mycsv.csv); do echo $i | awk -F "," '{printf; printf ","; gsub(/"/, "", $1);system("date -jf \"%m/%d/%Y %H:%M:%S %p\" \""$1"\" \"+%s\"");}';done – Jim Feb 1 '17 at 20:58
  • What does "not work" mean? Do you have sample input and expected output? – thrig Feb 1 '17 at 21:03

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.