0

(Using Shell Script) Need to generate html report from a CSV file which is an output of a DB query and then color code it based on column values. For example, from the given CSV ile, if any cell under column "status%" is less than 80, it should be highlighted ith red background, Anything >=90 Green.. I Tried using awk, but i was messing up with for loop.

CSV file is something like

Status%,Status Message,date 0f examination,type of exam,subject
75, success, 13-09-2013,mid term,physics
90, success, 15-09-203, mid term, Maths
100, success, 17-09-2013, term, biology
100, success, 17-09-2013, term, SST

Adding The Code

awk '
BEGIN {     
print "<html><body></br></br>The report provides overall Percentage Secured in the given subjects.</br></br></br>"
print "<table border=1 cellspacing=1 cellpadding=1>"
        } 
{
print "<tr>"
for ( i = 1; i <= NF; i++ )
{                                                    
if ($i <=75 )
  print "<td> <b><FONT COLOR=RED FACE="verdana"SIZE=2 ></b>"  $i  "</td>"
else
  if ($i >=90 && $i =100)
print "<td> <b><FONT COLOR=GREEN FACE="verdana"SIZE=2 ></b>"  $i  "</td>"
 else
print "<td> <FONT COLOR=YELLOW  FACE="verdana"SIZE=2 >" $i "</td>"
print "</tr>"
}
END { print "</table></body></html>"
}

'  /Reports/output/Output.html | sendmail -t
Improve this question
0

1 Answer 1

Reset to default
1

You have a good start but but are making some basic HTML errors, like never closing the <FONT> tag. Using this for your awk script should get you close to what you are looking for:

BEGIN {
  print "<html><body></br></br>The report provides overall Percentage Secured in the given subjects.</br></br></br>"
  print "<table border=1 cellspacing=1 cellpadding=1>"
}

NR==1 {
  # Header row
  print "<tr>"

  for ( i = 1; i <= NF; i++ ) {
    print "<td><b>"$i"</b></td>"
  }
  print "</tr>"
}

NR>1 {
  # Data rows
  print "<tr>"
  color="RED"
  if( $i > 80 ) {
    color="YELLOW"
  }
  if( $i > 90 ) {
    color="GREEN"
  }
  print "<td><b><FONT COLOR=\""color"\" FACE=\"verdana\" SIZE=2>"$1"</b></FONT></td><td>"$2"</td><td>"$3"</td><td>"$4"</td><td>"$5"</td>"
  print "</tr>"
}
END {
  print "</table></body></html>"
}

You can either run it inline as you are, or put this into its own file (e. g. reportgen.awk, and then run it with awk -f reportgen.awk /path/to/inputfile > outputfile.html.

Improve this answer
3
  • I just want to make it little simple..Any value in column 1 =100 ; then green and any value less than < 100 should be RED...I Tried the above code, with print "<tr>" color="GREEN" if $I < 100 { ; color ="RED" but still the values in the 1st column (Right now all are Hundreds) are coming in Green, if i exclude the if statement or RED, when i include the IF statement..Kindly help me with this.. And also how to add Background color to the cells (Different for Headers and Rows) Commented Oct 1, 2018 at 11:24
  • Your comment here and your example code are contradictory. However, if you only want GREEN to be implemented when the first field is 100 or greater, change the last if statement to be if( $i > 99); the logic there is relatively self-explanatory. As for alternating background colors and the like, that is again something that is better served by separating the formatting into CSS and the actual data into HTML. But you can use NR (speficially, NR % 2 and ! NR % 2 to alter a variable for the background color to be injected into the HTML much as this code already does with color. Commented Oct 1, 2018 at 15:32
  • thanks alot. Taking the cue fro your script i made little changes like if ($1 == 100) { color="GREEN" } if ($1 < 100 ) { color="RED" )..It has worked for me...Getting the output as i wanted... 2) Sorry for the confusion above. I want the font color of the rows to be blue ; background of complete table to be yellow; if ($1<100) ; then background of that cell should be "RED" . No clue abt how to use CSS. Thank you Commented Oct 1, 2018 at 18:34

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.