0

I am trying to create a csv file with AWK. I am using output file separator to add commas for columns. But there is one column whose name is separated by space and Its also getting processed in the output with comma.

cat test.txt | head -n 3
Vulnerability ID        Package                                     Severity          Fix                     Vulnerability URL
CVE-2017-1000408        libc-bin-2.24-11+deb9u3                     High              2.24-11+deb9u4          https://security-tracker.debian.org/tracker/CVE-2017-1000408
CVE-2017-1000408        libc-dev-bin-2.24-11+deb9u3                 High              2.24-11+deb9u4          https://security-tracker.debian.org/tracker/CVE-2017-1000408

Command:

cat test.txt | awk -F' ' 'BEGIN{OFS=",";} {print $1,$2,$3,$4,$5;}' > file.csv

Output: 

Vulnerability,ID,Package,Severity,Fix
CVE-2017-1000408,libc-bin-2.24-11+deb9u3,High,2.24-11+deb9u4,https://security-tracker.debian.org/tracker/CVE-2017-1000408
CVE-2017-1000408,libc-dev-bin-2.24-11+deb9u3,High,2.24-11+deb9u4,https://security-tracker.debian.org/tracker/CVE-2017-1000408

Expected Output:

Vulnerability ID,Package,Severity,Fix,Vulnerability URL
CVE-2017-1000408,libc-bin-2.24-11+deb9u3,High,2.24-11+deb9u4,https://security-tracker.debian.org/tracker/CVE-2017-1000408
CVE-2017-1000408,libc-dev-bin-2.24-11+deb9u3,High,2.24-11+deb9u4,https://security-tracker.debian.org/tracker/CVE-2017-1000408
Improve this question
6
  • There are two headers that contain spaces, and you explicitly set the input field separator to a space. Is the input fields separated by multiple spaces (not tabs)?
    – Kusalananda ♦
    Commented May 16, 2019 at 8:17
  • Yes, There are two headers. I am not sure it is the output of a program. Is there any way by which I can confirm.
    – mohit
    Commented May 16, 2019 at 8:29
  • 2
    Note that a single space as input field separator is special. It means "separate fields on any sequences of spaces, tabs and newlines". To avoid splitting your space-containing headers, if your fields are separated by a single TAB you may just use -F "\t"; for multiple (one or more) tabs, -F "\t+"; for a single space, -F [ ]; for two or more spaces, -F [ ]{2,}. And so on...
    – fra-san
    Commented May 16, 2019 at 8:49
  • Better use csvformat. awk would not correctly hands fields that include comma.
    – pLumo
    Commented May 16, 2019 at 9:06
  • (Of course, in my previous comment I forgot some quotes: -F '[ ]', -F '[ ]{2,}').
    – fra-san
    Commented May 16, 2019 at 10:06

1 Answer 1

Reset to default
0

My suggestion would be to elaborate the header line differently than the rest of the file. In this particular case:

awk 'NR==1 {$0=gensub(/(Vulnerability) (ID|URL)/, "\\1_\\2", "g"); print gensub(/(Vulnerability)_(ID|URL)/, "\\1 \\2", "g", $1","$2","$3","$4","$5)} NR>1 {print $1","$2","$3","$4","$5}' test.txt > file.csv

Or:

sed -r '1s/(Vulnerability) (ID|URL)/\1_\2/g' test.txt | awk '{print $1","$2","$3","$4","$5}' | sed -r '1s/(Vulnerability)_(ID|URL)/\1 \2/g' > file.csv

You've received some very appropriate suggestions in the comments, particularly the one about splitting on multiple space characters and the one suggesting you use other tools to manipulate CSV files. Moreover, if your issue lies solely in the header line, I'd fix it manually if I were you. And...do you really need to have spaces in the headers (as opposed to Vulnerability_URL)? It all depends on your actual use case.

Splitting on more than one space seems the easiest to me:

sed 's/   */,/g' test.txt > file.csv
Improve this answer

You must log in to answer this question.

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