SQL query output convert to HTML & send as email body

Shell Programming and Scripting
1

Hi ,

I have a sql query in the unix script ,whose output is shown below.I want to convert this output to HTML table format & send email from unix with this table as email body.

p_id  src_system  amount
1          A             100
2          B              200
3          C              300

Thanks in advance,
Jagadeesh

2

It depends which database system you are using.

In MySQL, you can use the -H (or --html) option. In Oracle SQLPlus, you can use set markup html on .

3

you can redirect the output of a query into one file........ex. output_query.log
and use this code for converting and mail this log as HTML.

awk '
        BEGIN {
                print "From: from@domain.com"
                print "To: to@domain.com"
                print "MIME-Version: 1.0"
                print "Content-Type: text/html"
                print "Subject: Email Subject"
                print "<html><body>"
                print "<table border=1 cellspacing=2 cellpadding=2>"
        }
        !/^#/ && /^S/ {
                print "<tr>"
                for ( i = 1; i <= NF; i++ )
                        print "<td><b>" $i "<b></td>"
                print "</tr>"
        }
        !/^#/ && !/^S/ {
                print "<tr>"
                for ( i = 1; i <= NF; i++ )
                        print "<td>" $i "</td>"
                print "</tr>"
        }
        END {
                print "</table></body></html>"
        }
'  output_qery.log | /usr/sbin/sendmail -t
4

joy, the code that you posted was written and provided to meet your requirement. It will not work for OP's requirement.

I think the solution Scott provided is really good and simple approach. OP has to simply spool the output and later email the spool file.