Convert columns to rows with awk

Question

I have an output file in the following format. How can I convert this into csv with first column as header row and second column as data row?

Input:

"Node name","SLAVE_DB"
"Node ID","2"
"PostgreSQL version","10.17"
"Total data size","1 GB"
"Conninfo","host=192.168.0.1 port=5432 user=user dbname=postgres"
"Role","standby"
"WAL archiving","disabled (on standbys "archive_mode" must be set to "always" to be effective)"

Desired Output:

"Node name","Node ID","PostgreSQL version","Total data size","Conninfo","Role","WAL archiving"
"SLAVE_DB","2","10.17","1 GB","host=192.168.0.1 port=5432 user=user dbname=postgres","standby","disabled (on standbys ""archive_mode"" must be set to ""always"" to be effective)"

[Edit] I read through similar questions and I tried awk -F "," '{ for (i=1;i<=NF;i++ ) printf $i ", " }'
but it does not produce the desired output. I also want to preserve the quoted fields with in the data.

The quoted input strings will not contain commas or newlines, but will contain unescaped quotes — curious_ghost, Feb 07 '22 at 16:45

Ed Morton · Accepted Answer · 2022-02-07T17:52:35.107

If we assume that there's never quotes in your first field (the tag/name/header), then using any awk in any shell on every Unix box:

$ cat tst.awk
BEGIN { FS=OFS="," }
{
    tags[NR] = $1
    vals[NR] = $2
}
END {
    for ( colNr=1; colNr<=NR; colNr++ ) {
        tag = tags[colNr]
        printf "%s%s", tag, (colNr<NR ? OFS : ORS)
    }
    for ( colNr=1; colNr<=NR; colNr++ ) {
        val = vals[colNr]
        gsub(/^"|"$/,"",val)
        gsub(/"/,"\"\"",val)
        printf "\"%s\"%s", val, (colNr<NR ? OFS : ORS)
    }
}

$ awk -f tst.awk file
"Node name","Node ID","PostgreSQL version","Total data size","Conninfo","Role","WAL archiving"
"SLAVE_DB","2","10.17","1 GB","host=192.168.0.1 port=5432 user=user dbname=postgres","standby","disabled (on standbys ""archive_mode"" must be set to ""always"" to be effective)"

If you need more than that then see What's the most robust way to efficiently parse CSV using awk?.

Your assumptions is correct that it is always an unescaped quote. I made a mistake in the input. Thanks a lot for the solution! — curious_ghost, Feb 07 '22 at 17:45

Convert columns to rows with awk

1 Answers1