csv2md format output using awk

Question

I need to create a markdown formatted output using awk which is similar to table results that we get using mysql. In other words, I'm trying to mimic https://www.convertcsv.com/csv-to-markdown.htm using the below input.

id|type|cost|date|ship
0|A|223|201603|PORT
0|A|22|201602|PORT
0|A|422|201601|DOCK
1|B|3213|201602|DOCK
1|B|3213|201601|PORT
2|C|2321|201601|DOCK

The output I'm looking for is below

------------------------------
|id |type |cost |  date |ship |
------------------------------
|0  |A    |223  |201603 |PORT |
|0  |A    |22   |201602 |PORT |
|0  |A    |422  |201601 |DOCK |
|1  |B    |3213 |201602 |DOCK |
|1  |B    |3213 |201601 |PORT |
|2  |C    |2321 |201601 |DOCK |
------------------------------

My first attempt is to get the max size for each column and use that in the formatting when printing. But the below one is not working as expected.

awk -F"|" ' 
NR==1 { hdr=$0; for(i=1;i<=NF;i++) { a[i]=length($i) } next } 
{ for(i=1;i<=NF;i++) { ;a[i]=length($i)>a[i]?length($i):a[i] } content[NR]=$0 } 
END { 
for(i in a) len+=a[i]+2;
$0=""; OFS="-"; len++; NF=len; print ;
n=split(hdr,arr,FS); 
for(i=1;i<=n;i++) 
  {  printf("%6s |",arr[i]); }  # instead of 6 i want to pass a[i] ==> "%6" a[i] "s |" is not working
print "";

} 
' data.txt

How to fix it and get the required output.

Answer 1

This requires a 2-pass approach, either of the following would work:

A) reading the input file twice so it uses very little memory:

$ cat tst.awk
BEGIN { FS=OFS="|" }
NR==FNR {
    for (i=1; i<=NF; i++) {
        wid = length($i)
        wids[i] = (wid > wids[i] ? wid : wids[i])
    }
    next
}
FNR==1 {
    totWid = NF+1
    for (i=1; i<=NF; i++) {
        totWid += wids[i]
    }
    dashes = sprintf("%*s",totWid,"")
    gsub(/ /,"-",dashes)
    print dashes
    printf "%s", OFS
    for (i=1; i<=NF; i++) {
        printf "%*s%s", wids[i], $i, OFS
    }
    print ""
    print dashes
    next
}
{
    printf "%s", OFS
    for (i=1; i<=NF; i++) {
        printf "%-*s%s", wids[i], $i, OFS
    }
    print ""
}
END { print dashes }

$ awk -f tst.awk file file
--------------------------
|id|type|cost|  date|ship|
--------------------------
|0 |A   |223 |201603|PORT|
|0 |A   |22  |201602|PORT|
|0 |A   |422 |201601|DOCK|
|1 |B   |3213|201602|DOCK|
|1 |B   |3213|201601|PORT|
|2 |C   |2321|201601|DOCK|
--------------------------

B) storing the whole file in memory and then the 2nd pass is an array traversal rather than reading the file again which should run faster if you have enough memory to do it:

$ cat tst.awk
BEGIN { FS=OFS="|" }
{
    for (i=1; i<=NF; i++) {
        wid = length($i)
        wids[i] = (wid > wids[i] ? wid : wids[i])
        vals[NR,i] = $i
    }
}
END {
    totWid = NF+1
    for (i=1; i<=NF; i++) {
        totWid += wids[i]
    }
    dashes = sprintf("%*s",totWid,"")
    gsub(/ /,"-",dashes)
    print dashes
    printf "%s", OFS
    for (i=1; i<=NF; i++) {
        printf "%*s%s", wids[i], vals[1,i], OFS
    }
    print ""
    print dashes

    for (lineNr=2; lineNr<=NR; lineNr++) {
        printf "%s", OFS
        for (i=1; i<=NF; i++) {
            printf "%-*s%s", wids[i], vals[lineNr,i], OFS
        }
        print ""
    }
    print dashes
}

$ awk -f tst.awk file
--------------------------
|id|type|cost|  date|ship|
--------------------------
|0 |A   |223 |201603|PORT|
|0 |A   |22  |201602|PORT|
|0 |A   |422 |201601|DOCK|
|1 |B   |3213|201602|DOCK|
|1 |B   |3213|201601|PORT|
|2 |C   |2321|201601|DOCK|
--------------------------

Answer 2

There is a program for making tabular output, which is columns -s '|' -t, but it looses the separator | in its output.

You can recover that separator with sed, replacing each white space followed by a non white char by a | followed by that non white char.

Finally, awk helps you to add a line before and after the headers of the columns.

column -s '|' -t input.csv | sed -E 's/ ([^ ])/|\1/g' | awk '(NR == 1) {l = $0; gsub(/./, "-", l); print l "\n" $0 "\n" l} (NR > 1) {print} END{print l}'