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
2
  • 1
    Welcome to U&L! Rather than just dismissing what you've tried as "mess[ed] up", please share what you have so far so that we can help you make it work. Also, as an aside, you might be better served in generating the raw HTML for the table separately, and using CSS to color the cells. – DopeGhoti Sep 28 '18 at 19:05
  • Thanks for your reply @DopeGhoti..I have added the code to the main question... – D.Joe Sep 28 '18 at 19:41
0

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.

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) – D.Joe Oct 1 '18 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. – DopeGhoti Oct 1 '18 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 – D.Joe Oct 1 '18 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.