1

I have downloaded this CSV file: https://www.nhsbsa.nhs.uk/sites/default/files/2020-05/Dispensing%20Data%20Jan%2020%20-%20CSV.csv

and am trying to add a column on to the end with a value of "null" for every row.

I have tried using this awk command: awk 'BEGIN{FS=OFS=","}{print $0 OFS "null"}' ogfile.csv > newfile.csv

but it appears to be adding a new row after every row, with the second column having a field of "null"

new rows instead of new column

can anyone help me understand why this is happening?

emily wilson
  • 35
  • 5
  • #echo -e "test,test\ntest,test" | awk -F'\n' '{print $1 ",null"}' test,test,null test,test,null – GaryB Aug 13 '20 at 22:51

1 Answers1

1

Your source file has DOS/Windows line endings. When one sees anomalous output, this is a good first item to check. Two solutions:

  1. Use a utility such as dos2unix to remove the unwanted \r character from your input file. dos2unix is available on most distributions.

or,

  1. Modify your awk command to recognize and remove the offending characters:

    awk 'BEGIN{RS="\r\n"; FS=OFS=","}{print $0 OFS "null"}' ogfile.csv
John1024
  • 109,961
  • 14
  • 137
  • 171
  • thanks very much! that makes sense and works great. n.b. there still appears to be a blank line being printed under each row. i thought removing `\n` might fix it but that doesn't do anything. do you know what could stop that from happening? – emily wilson Aug 13 '20 at 23:49
  • @emilywilson I'm glad it works for you. I don't know what to say about the blank line issue because can't reproduce it. If the issue continues, can you tell me more about what operating system you are using, version of awk, etc? – John1024 Aug 13 '20 at 23:58
  • i'm using a Mac OS, and the version of awk is 20070501 – emily wilson Aug 14 '20 at 00:15
  • @emilywilson Hmm. I don't have access to a Mac so I can't test this code in your environment. (I also have no access to 13-year-old versions of BSD awk.) Do you have access to a hex viewer, such as [one of these](https://stackoverflow.com/questions/827326/whats-a-good-hex-editor-viewer-for-the-mac), that would allow you to see what the input and output files look like? In input, I expect line-endings that I see are `0d 0a` (carriage return, newline). The output from the above command gives me line-endings of simply `0a`. If you see something different, let me know. – John1024 Aug 14 '20 at 04:19