1

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","[email protected]","A","6.0.12","A","Now"  

Expected output:

"1/30/2017 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","[email protected]","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","[email protected]","A","6.0.12","A","Now"  
"1/30/2017 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","[email protected]","A","6.0.12","A","Now"  
"1/30/2017 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","[email protected]","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","[email protected]","A","6.0.12","A","Now"  
,148577129511:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","[email protected]","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","[email protected]","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","[email protected]","A","6.0.12","A","Now"  
"1/30/2017 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","[email protected]","A","6.0.12","A","Now"  
"1/30/2017 11:14:55 AM","I","M","k6.0.1","E","jim","JimK","JIM","[email protected]","A","6.0.12","A","Now"  

1 Answer 1

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
10
  • 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, 2017 at 20:50
  • Reforming and appending are two very different things. Do you have sample input and expected output?
    – thrig
    Feb 1, 2017 at 20:54
  • I mean you are doing system("echo more"). I am doing system(date(...$1))
    – Jim
    Feb 1, 2017 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, 2017 at 20:58
  • What does "not work" mean? Do you have sample input and expected output?
    – thrig
    Feb 1, 2017 at 21:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .