AWK: Converting CSV with Headers to Summary Table

Question

I have a need to document 100+ CSV files as far as the format of those files and including sample data. What I would like to do is take a CSV of the following format:

Name, Phone, State
Fred, 1234567, TX
John, 2345678, NC

and convert it to:

Field | Sample
---   | ----
Name  | Fred
Phone | 1234567
State | TX

Is this possible with AWK? From my example below, you will see I am trying to format as a markdown table. I have it currently transposing the header row with

#!/usr/bin/awk -v RS='\r\n' -f
BEGIN { printf "| Field \t| Critical |\n"}
{
    printf "|---\t|---\t|\n"
    for (i=1; i<=NF; i++) {print "|", toupper($i), "| sample |"}
}
END  {}

But I am not sure now how to use the first row of data, after the header to display the sample data?

can you give two samples in output at least?

BMW
– BMW

2014-03-13 01:30:29 +00:00
Commented Mar 13, 2014 at 1:30 — BMW
– BMW, Commented Mar 13, 2014 at 1:30

jaypal singh · Accepted Answer · 2014-03-13 02:01:15Z

1

awk is the right tool for data parsing. You can try something like:

awk '
BEGIN { FS=", "; OFS=" | " }
NR==1 {
    for(tag = 1; tag <= NF; tag++) {
        hdr[tag] = sprintf ("%-7s", $tag)
    }
    next
}
{
    for(fld = 1; fld <= NF; fld++) {
        data[NR,fld] = $fld
    }
}
END {
    print "Field   | Sample\n------- | -------";
    for(rec = 2; rec <= NR; rec++) {
        for(line = 1; line <= NF; line++) {
            print hdr[line], data[rec,line]
        }
    }
}' file

Output:

Field   | Sample
------- | -------
Name    | Fred
Phone   | 1234567
State   | TX
Name    | John
Phone   | 2345678
State   | NC

edited Mar 13, 2014 at 2:01

answered Mar 13, 2014 at 1:51

jaypal singh

77.6k24 gold badges108 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1519769 Over a year ago

Spot on, thank you so much! This is exactly what I was after. I love the Internet and all who share their knowledge on it! Thanks.

Jotne Over a year ago

Since you are using tag value only one loop, you can replace for(tag = 1; tag <= NF; tag++) with while(tag++<NF)

jaypal singh Over a year ago

@Jotne Indeed, just went with the more explicit version.

Jotne · Accepted Answer · 2014-03-13 06:52:57Z

Here is a more simple way to do it with awk
No need to store everything in a array then print at the end.

awk -F", " 'NR==1{split($0,a,FS);print "Field   | Sample\n------- | -------";next} {for (i=1;i<=NF;i++) printf "%-8s| %s\n",a[i],$i}' file
Field   | Sample
------- | -------
Name    | Fred
Phone   | 1234567
State   | TX
Name    | John
Phone   | 2345678
State   | NC

How it works:

awk -F", " '                            # set field separator to ","
NR==1{                                  # if first line do:
    split($0,a,FS)                      # split first line to an array named "a" to get the labels 
    print "Field   | Sample"            # print header
    print "------- | -------"           # print separator
    next}                               # prevents nothing more run for first line
    {                                   # for all lines except first do:
    for (i=1;i<=NF;i++)                 # loop trough all element in line
        printf "%-8s| %s\n",a[i],$i     # print data for every element
    }
' file

Collectives™ on Stack Overflow

AWK: Converting CSV with Headers to Summary Table

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related