1

i want to add a timestamp with awk to a CSV-file. To generate the timestamp works, but the result CSV-file have some extra comma at several places. How can I fix the problem?

origin-CSV (1st line):

time,latitude,longitude,depth,mag,magType,nst,gap,dmin,rms,net,id,updated,place,type 2015-03-13T07:53:53.950Z,38.838501,-122.8300018,3.01,0.18,md,9,93,0.004285,0.02,nc,nc72410280,2015-03-13T08:08:04.639Z,"9km W of Cobb, California",earthquake

awk:

awk -F"," 'BEGIN { OFS = "," ; print "time","latitude","longitude","depth","mag","magType","nst","gap","dmin","rms","net","id","updated","place","type","timestamp"} NR>$0{"date +%s -d"$1""| getline dte;$17=dte; print}' origin.csv > result.csv

I was surprised that it worked only with $17 instead of $16, although the timestamp-column is the 16th.

result for the 5th line:

time,latitude,longitude,depth,mag,magType,nst,gap,dmin,rms,net,id,updated,place,type,timestamp 2015-03-13T06:46:45.450Z,-55.1707,-128.8193,10,4.9,mb,,116,31.469,0.59,us,us10001m1z,2015-03-13T07:36:31.510Z,"Pacific-Antarctic Ridge",earthquake,,1426229205

here are two commas instead of one.

  • just a tough, there is a comma in "9km W of Cobb, California". – Archemar Mar 13 '15 at 9:39
  • oh man, so stupid. That solved the problem. Deleted row "type" and "place" (fortunately I don't need them) and now it works ... thanks so much for taking a look – Basti Mar 13 '15 at 9:51
  • @Basti please change your Comment to an Answer and mark it as the solution. – roaima Mar 13 '15 at 10:40
  • it says that I can accept my answer in two days. I will do that, but if Archemar would change his his comment to an answer I will tick it as an answer and delete my own. – Basti Mar 13 '15 at 10:52
0

@Archemar
oh man, so stupid. That solved the problem. Deleted row "type" and "place" (fortunately I don't need them) and now it works ... thanks so much for taking a look

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.