Help with data formatting

Shell Programming and Scripting
1

Hi,

I am generating the following output from my script.

Country,A,B,C,D,E,F
INDIA    ,3755019,774604,484749,329838,7333612,442031
CHINA       ,3716520,889197,530899,379754,6198475,355768
JAPAN       ,52038,30462,231224,147275,1272,498
USA,9494,1130,0,0,15303,451
UK,3680478,1085154,999262,645190,4453831,421336
CANADA      ,2,1,0,0,0,0
SINGAPORE         ,3548689,715416,1073298,733718,3520766,304011

I want to format this into html table code:

<table>
<tr><td>Country</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr>
<tr><td>INDIA    </td><td>3755019</td><td>774604</td><td>484749</td><td>329838</td><td>7333612</td><td>442031</td></tr>
<tr><td>CHINA       </td><td>3716520</td><td>889197</td><td>530899</td><td>379754</td><td>6198475</td><td>355768</td></tr>
<tr><td>JAPAN       </td><td>52038</td><td>30462</td><td>231224</td><td>147275</td><td>1272</td><td>498</td></tr>
<tr><td>USA</td><td>9494</td><td>1130</td><td>0</td><td>0</td><td>15303</td><td>451</td></tr>
<tr><td>UK</td><td>3680478</td><td>1085154</td><td>999262</td><td>645190</td><td>4453831</td><td>421336</td></tr>
<tr><td>CANADA      </td><td>2</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>SINGAPORE         </td><td>3548689</td><td>715416</td><td>1073298</td><td>733718</td><td>3520766</td><td>304011</td></tr>
</table>

Also Bold the first column (Country names) and first row (Column names).
But here I have to handle cases if the output from the initial script gives one more extra country.

I want to use this final output for UNIX mail command with HTML. So the body of the message will display a table for this.

Thanks.

2

Try this as a starting point. I'm not sure if the long OFS and ORS separators will work on every awk implementation, so your mileage may vary:

awk  'BEGIN  {printf  "<table>\n<tr><td>"; ORS="</td></tr>\n<tr><td>"}
      END    {ORS="\n";print "</table>"}
      NR==1  {for (i=1;i<=NF;i++) $i="<b>"$i"</b>"}
      NR>1   {$1="<b>"$1"</b>" } 
      1
     ' FS="," OFS="</td><td>"  file
<table>
<tr><td><b>Country</b></td><td><b>A</b></td><td><b>B</b></td><td><b>C</b></td><td><b>D</b></td><td><b>E</b></td><td><b>F</b></td></tr>
<tr><td><b>INDIA    </b></td><td>3755019</td><td>774604</td><td>484749</td><td>329838</td><td>7333612</td><td>442031</td></tr>
<tr><td><b>CHINA       </b></td><td>3716520</td><td>889197</td><td>530899</td><td>379754</td><td>6198475</td><td>355768</td></tr>
<tr><td><b>JAPAN       </b></td><td>52038</td><td>30462</td><td>231224</td><td>147275</td><td>1272</td><td>498</td></tr>
<tr><td><b>USA</b></td><td>9494</td><td>1130</td><td>0</td><td>0</td><td>15303</td><td>451</td></tr>
<tr><td><b>UK</b></td><td>3680478</td><td>1085154</td><td>999262</td><td>645190</td><td>4453831</td><td>421336</td></tr>
<tr><td><b>CANADA      </b></td><td>2</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td><b>SINGAPORE         </b></td><td>3548689</td><td>715416</td><td>1073298</td><td>733718</td><td>3520766</td><td>304011</td></tr>
<tr><td></table>

Not easy to get rid of the last line's "<tr><td>" ... I'll work on it.

Corrected for the syntax error (closing }).

3

First line of the output is getting comma separated.

4

Yes, already noticed and changed in post #2. Pls try again.

The easiest way to correct the last line is to append a small sed script:

awk ... | sed 's:^.*\(</table>\):\1:'
5 
Syntax Error The source line is 6.
 The error context is
                     >>>   <<<
 awk: 0602-502 The statement cannot be correctly parsed. The source line is 6.
        awk: 0602-540 There is a missing } character.
6

Don't know how I spoiled that: append a } to the line NR>1...