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
  • 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 May 16 '19 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 Kumar May 16 '19 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 May 16 '19 at 8:49
  • Better use csvformat. awk would not correctly hands fields that include comma. – pLumo May 16 '19 at 9:06
  • (Of course, in my previous comment I forgot some quotes: -F '[ ]', -F '[ ]{2,}'). – fra-san May 16 '19 at 10:06
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 | |

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.