Convert a csv with headers to HTML in Bash

Question

I have data like below in a csv file

ServerName,Index,Status
10.xxx.xx.xx,1.5.1.1,2
10.xxx.xx.xx,1.5.1.2,3

I need to convert this data to html and also color the row if the value of the "Status" is 3/4/5.. please help me in this. tried below

    awk 'BEGIN{
FS=","
print  "<HTML>""<TABLE border="1"><TH>JOB_NAME</TH><TH>RUN_DATE</TH><TH>STATUS</TH>"
}
 {
printf "<TR>"
for(i=1;i<=NF;i++)
printf "<TD>%s</TD>", $i
print "</TR>"
 }
END{
print "</TABLE></BODY></HTML>"
 }
' 10.106.40.45_FinalData.csv > file.html
sed -i "s/2/<font color="green">2<\/font>/g;s/4/<font color="red">4<\/font>/g;s/5/<font color="red">5<\/font>/g;" file.html

in the latest code i tried, i need to check the value of the status column only and need to color the cell.

Ed Morton · Accepted Answer · 2019-03-12T14:14:15.667

1

$ cat tst.awk
BEGIN{
    FS = ","
    colors[3] = "red"
    colors[4] = "green"
    colors[5] = "blue"

    print "<HTML><BODY>"
    print "<TABLE border=\"1\">"
    print "<TR><TH>JOB_NAME</TH><TH>RUN_DATE</TH><TH>STATUS</TH></TR>"
}
NR>1 {
    printf "<TR>"
    for (i=1; i<=NF; i++) {
        if ( (i == NF) && ($i in colors) ) {
            on  = "<font color=\"" colors[$i] "\">"
            off = "</font>"
        }
        else {
            on = off = ""
        }
        printf "<TD>%s%s%s</TD>", on, $i, off
    }
    print "</TR>"
}
END {
    print "</TABLE>"
    print "</BODY></HTML>"
}

.

$ awk -f tst.awk file
<HTML><BODY>
<TABLE border="1">
<TR><TH>JOB_NAME</TH><TH>RUN_DATE</TH><TH>STATUS</TH></TR>
<TR><TD>10.xxx.xx.xx</TD><TD>1.5.1.1</TD><TD>2</TD></TR>
<TR><TD>10.xxx.xx.xx</TD><TD>1.5.1.2</TD><TD><font color="red">3</font></TD></TR>
</TABLE>
</BODY></HTML>

edited Mar 12 '19 at 14:14

answered Mar 12 '19 at 14:07

Ed Morton

188,023
17
78
185

Your code is assuming CSV values don't contain newlines, what is allowed by the CSV spec, is this an oversight? – Ferrybig Mar 12 '19 at 17:07
@Ferrybig No, it's solving the problem the OP actually has rather than trying to come up with a general-purpose solution for all possible flavors of CSV and all possible input values (there is no one "CSV spec" and in general CSV fields could contain commas and HTML tags too, right?). See https://stackoverflow.com/q/45420535/1745001 for more information on handling CSVs with awk. – Ed Morton Mar 12 '19 at 18:34

score 0 · Answer 2 · answered Mar 12 '19 at 12:31

You don't actually say what the problem is, but I presume it's colorizing the numbers when they appear in the addresses also?

The best solution is probably to add a conditional into your awk script (untested):

if (i == 3 && $i == 2) {
  print "<TD><font color="green">2<\/font></TD>"
} else .....

Alternative, your status field is the only number in the column, whereas the addresses are not, so you can adjust your pattern match:

"s/>2</><font color="green">2<\/font></g;......"

I.e. match the surrounding brackets.

The problem is numbers are there in ServerName & Index also. so if i mention 2/3/4/5, it is coloring all the cells/fonts. bit i need this color to be applied only for the Status column — Sandy, Mar 12 '19 at 13:34

jpseng · Answer 3 · 2021-06-30T16:02:43.970

You can also use jq for this task. jq structures the CSV data instead of working only on a text basis. This makes it easy to remove empty rows or to colour only the 'Status' column.

#!/bin/bash

CSV='
ServerName,Index,Status
10.xxx.xx.xx,1.5.1.1,2
10.xxx.xx.xx,1.5.1.2,3
'

jq -srR '
 def colorize($status):
   if   $status == "3" then "yellow"
   elif $status == "4" then "orange"
   elif $status == "3" then "red"
   else "green"
   end
   | "<font color=\"\(.)\">\($status)</font>";

 split("\n")                 # split lines
 | map(select(length > 0))   # remove empty lines from CSV
 | map(split(","))           # split each line
 | .[1:]                     # drop first line with headers
 | "<table>",                # convert to HTML table
   "  <tr> <th>ServerName</th> <th>Index</th> <th>Status</th> </tr>",
   (.[] | "  <tr> <td>\(.[0])</td> <td>\(.[1])</td> <td>\(colorize(.[2]))</td> </tr>"),
   "</table>"
' <<< "$CSV"

output:

<table>
  <tr> <th>ServerName</th> <th>Index</th> <th>Status</th> </tr>
  <tr> <td>10.xxx.xx.xx</td> <td>1.5.1.1</td> <td><font color="green">2</font></td> </tr>
  <tr> <td>10.xxx.xx.xx</td> <td>1.5.1.2</td> <td><font color="yellow">3</font></td> </tr>
</table>

Convert a csv with headers to HTML in Bash

3 Answers3