Use first column as header for csv

Question

I have an input file as follows

hello: hello12
foo: bar14
test3: pppp
more: stuff
test14: bla

Is there a way to use the first column as header and separate it by comma's as follows:

hello,foo,test3,more,test14
hello12,bar14,pppp,stuff,bla

I've tried a few things at no success.

Are there more columns or just 2? Do the values contain , ? What are the few things you tried? — pLumo
– pLumo, Commented Jul 29, 2019 at 10:58
There are more but the idea is the same I think: I tried: cut -d: -f1 file | tr '\n' ',' but this will get first column onlu — holasz
– holasz, Commented Jul 29, 2019 at 11:08
From your example we cannot see how the next field will be separated. — pLumo
– pLumo, Commented Jul 29, 2019 at 11:17
@pLumo The first column which is in this example: hello, foo, test3 should be the header. Let me update the question. THe first column is seperated by a colon : and it doesn't matter how long the first column is, it should be used as the header. — holasz
– holasz, Commented Jul 29, 2019 at 11:21
When your real data contains more than 1 of anything, make sure your example contains more than 1 of that thing so it adequately demonstrates your problem and we have something we can test a potential solution against. In this case you're telling us in a comment that your real input has more than one data (non-header column) but your example only has one and consequently the one answer you have so far will only work for 1. Add at least 1 more column to your input and associated row to your output. — Ed Morton
– Ed Morton, Commented Jul 29, 2019 at 12:27

pLumo · Accepted Answer · 2019-07-29 11:40:13Z

5

You can use a combination of cut and paste:

paste -sd, <(cut -d: -f1 file) <(cut -d' ' -f2- file)

or use awk:

awk -F': ' 'NR==1{h=$1;v=$2}
            NR>1{h=h","$1;v=v","$2}
            END{print h;print v}
' file

answered Jul 29, 2019 at 11:40

pLumo

23.2k2 gold badges43 silver badges70 bronze badges

1 Answer 1