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"