Convert pipe delimited column data to HTML table format for email

Question

I am trying to convert delimited data format to html column table output for email printing and I am unsure how to use pipe delimiter as a separater for HTML tabular formatting.

Below is what I could use if a space was a separator, but in this example I am using a pipeline (|).

awk ' BEGIN {
print "To: '[email protected]'"
#print "MIME-Version: 1.0"
print "Content-Type: text/html"
print "Subject: This is a test email"
print "<html><body><table border=1 cellspacing=0 cellpadding=3>"
print "<tr>"
print "<td>SID</td>";
print "<td>PID</td>";
print "<td>Username</td>";
print "<td>Database</td>";
print "<td>Hostname</td>";
print "<td>Program</td>";
print "<td>Connected</td>";
print "<td>Idle Time</td>";
print "<td>Query Time</td>";
print "<td>EST COST</td>";
print "<td>SEQ SCAN</td>";
print "<td>Query</td>";
print "</tr>"
} {
print "<tr>"
print "<td>"$1"</td>";
print "<td>"$2"</td>";
print "<td>"$3"</td>";
print "<td>"$4"</td>";
print "<td>"$5"</td>";
print "<td>"$6"</td>";
print "<td>"$7"</td>";
print "<td>"$8"</td>";
print "<td>"$9"</td>";
print "<td>"$10"</td>";
print "<td>"$11"</td>";
print "<td>"$12"</td>";
print "</tr>"
} END {
print "</table></body></html>"
} ' /home/test/test.unl | sendmail -t

Inside the test.unl file is the below:

15422216|-1|dwhvo|test|pd244zax.test.corp|N/A|    10:56:53|    -0:00:30|10:57:22|1045127|1|SELECT sba_sub_aux.sba_subscriber_id, sba_sub_aux.sba_id_number, sba_sub_aux.sba_matchcode, sba_sub_aux.sba_marketing, sba_su|

I would like to achieve the below in table format via email.

If this is a raw email to be sent with SMTP then you'll need a separation in between the header and the body of the email. A dot + empty line, or maybe just an empty line, I don't remember — Fravadona
– Fravadona, Commented Dec 12, 2024 at 11:10
Also, since there's an SQL query involved here, this looks a lot as if you're doing this in a strange manner: in some other tool you're already causing these queries. That other tool is almost certainly more than suitable for generating the HTML output, and quite possibly can even send emails, obsoleting your need for both awk and sendmail. — Marcus Müller
– Marcus Müller, Commented Dec 12, 2024 at 11:13
"achieve the below in table format" is a completely separate question from "via email". Only ask about 1 thing in 1 question, in this case how to convert pipe-delimited input to an HTML table output. You can figure out how to add it to an email later and ask a new question if you run into a problem. — Ed Morton
– Ed Morton, Commented Dec 14, 2024 at 11:31

Stéphane Chazelas · Accepted Answer · 2024-12-17 08:26:39Z

If you remove the trailing |, and add a header:

SID|PID|Username|Database|Hostname|Program|Connected|Idle Time|Query Time|EST COST|SEQ SCAN|Query

Then, that becomes a simple TSV with pipe as delimiter, and you can use for instance mlr to convert to a proper CSV, which pandoc can convert to HTML:

{
  echo 'SID|PID|Username|Database|Hostname|Program|Connected|Idle Time|Query Time|EST COST|SEQ SCAN|Query'
  sed 's/|$//' your-file
} |
  mlr --itsvlite --ifs pipe --ocsv cat |
  pandoc -s -f csv -t html -M 'pagetitle=My table' \
    -V 'header-includes=<style>table,th,td{border:1px solid;border-collapse:collapse}</style>' > result.html

That will take care of properly escaping HTML in cell contents.

To right-align some of the columns as in your expected output, another option is to use markdown as the intermediary format.

{
 echo 'SID|PID|Username|Database|Hostname|Program|Connected|Idle Time|Query Time|EST COST|SEQ SCAN|Query'
 sed 's/|$//' your-file
} |
  mlr --itsvlite --ifs pipe --omd cat |
  perl -pe 's{-+}{substr("110000101110",$n++,1)?"$&:":":$&"}ge if $. == 2' |
  pandoc -s -f markdown -t html -M 'pagetitle=My table' \
    -V 'header-includes=<style>table,th,td{border:1px solid;border-collapse:collapse}</style>' > result.html

Above using perl to specify the alignment of each column based on the 110000101110 "bit map" (1 for right, 0 for left).

Beware mlr doesn't escape the markdown syntax operators in the table cells. So for instance if you have `foo` or **bar** in some of the cells, they'll be converted by pandoc to the HTML equivalent (<code>foo</code>, <strong>bar</strong>).

If pandoc is hard to install on your system, you could do everything in perl (which should be pre-installed), and the HTML::Table and HTML::Entities modules which if not installed already should be easy to install even if not packaged by your system (like by using cpan):

perl -MHTML::Table -MHTML::Entities -C -F'\|' -lane '
  BEGIN {
    $t = new HTML::Table(
      -border  => 1,
      -spacing => 0,
      -padding => 0,
      -head    => ["SID","PID","Username","Database","Hostname","Program","Connected","Idle Time","Query Time","EST COST","SEQ SCAN","Query"]
    );
  }
  $t->addRow(map {encode_entities$_} @F);
  END {
    $t->setColAlign(++$i, qw(left right)[$_]) for qw(1 1 0 0 0 0 1 0 1 1 1 0);
    print "<html><body>$t</body></html>";
  }' your-file > result.html

Perl also has modules for properly formatting and sending emails.

Fravadona · Accepted Answer · 2024-12-19 13:18:53Z

As @GillesQuénot commented, splitting a line on | with awk is as simple as awk -F '|'.

That said, I recommend that you define at least two functions: one for escaping HTML text (the sample data doesn't need it but you never know...), and an other one for printing a whole HTML table row; that'll make the program "cleaner" and robuster.

awk -F '|' '
    BEGIN {
        # ...
        print "<html><body><table border=1 cellspacing=0 cellpadding=3>"
        $0 = "SID|PID|Username|Database|Hostname|Program|Connected|Idle Time|Query Time|EST COST|SEQ SCAN|Query"
        print_as_tr()
    }
    {
        sub(/.$/, "") # fix the record for it to contain only what you want
        print_as_tr()
    }
    END {
        print "</table></body></html>"
    }

    function print_as_tr(    i) {
        print "<tr>"
        for (i = 1; i <= NF; i++)
            print "<td>" html_textify($i) "</td>"
        print "</tr>"
    }
    function html_textify(s) {
        gsub(/&/, "\\&amp;", s)
        gsub(/</, "\\&lt;", s)
        gsub(/>/, "\\&gt;", s)
        return s
    }
'

Would make sense to use <th> instead of <td> for the header row. — Stéphane Chazelas
– Stéphane Chazelas, Commented Dec 14, 2024 at 13:06
@StéphaneChazelas Indeed, I thought of adding it but decided to leave it alone. — Fravadona
– Fravadona, Commented Dec 14, 2024 at 13:13

ReflectYourCharacter · Accepted Answer · 2024-12-12 13:58:34Z

awk -F '|' works for me.

awk -F '|' ' BEGIN {
print "To: '[email protected]'"
#print "MIME-Version: 1.0"
print "Content-Type: text/html"
print "Subject: This is a test email"
print "<html><body><table border=1 cellspacing=0 cellpadding=3>"
print "<tr>"
print "<td>SID</td>";
print "<td>PID</td>";
print "<td>Username</td>";
print "<td>Database</td>";
print "<td>Hostname</td>";
print "<td>Program</td>";
print "<td>Connected</td>";
print "<td>Idle Time</td>";
print "<td>Query Time</td>";
print "<td>EST COST</td>";
print "<td>SEQ SCAN</td>";
print "<td>Query</td>";
print "</tr>"
} {
print "<tr>"
print "<td>"$1"</td>";
print "<td>"$2"</td>";
print "<td>"$3"</td>";
print "<td>"$4"</td>";
print "<td>"$5"</td>";
print "<td>"$6"</td>";
print "<td>"$7"</td>";
print "<td>"$8"</td>";
print "<td>"$9"</td>";
print "<td>"$10"</td>";
print "<td>"$11"</td>";
print "<td>"$12"</td>";
print "</tr>"
} END {
print "</table></body></html>"
} ' /home/test/test.unl | sendmail -t

Output displaying as I wanted in email.

Ed Morton · Accepted Answer · 2024-12-14 13:22:31Z

@Fravadona's answer is good, here's just an alternative approach, using any POSIX awk, to consider that's IMO not significantly better aside from a couple of cosmetic differences/bells and whistles. It mainly just isolates each specific part of the output by providing a separate function for each level/segment of HTML which might make it a bit easier and less error prone to modify if necessary in future:

$ cat tst.sh
#!/usr/bin/env bash

awk '

    BEGIN {
        FS = "[|]"
        Indent = -2
        prt_html()
        exit
    }

    function prt_html() {
        prt_beg("<html>")
        prt_body()
        prt_end("</html>")
    }

    function prt_body() {
        prt_beg("<body>")
        prt_table()
        prt_end("</body>")
    }

    function prt_table() {
        prt_beg("<table border=1 cellspacing=0 cellpadding=3>")
        numHdrCells = prt_hdr_row("SID|PID|Username|Database|Hostname|Program|Connected|Idle Time|Query Time|EST COST|SEQ SCAN|Query")
        prt_data_rows(numHdrCells)
        prt_end("</table>")
    }

    function prt_data_rows(numHdrCells, line) {
        while ( (getline line) > 0 ) {
            prt_data_row(numHdrCells,line)
        }
    }

    function prt_hdr_row(s,     numCells) {
        prt_beg("<tr>")
        numCells = prt_hdr_cells(s)
        prt_end("</tr>")
        return numCells
    }

    function prt_data_row(numHdrCells,s) {
        prt_beg("<tr>")
        prt_data_cells(numHdrCells,s)
        prt_end("</tr>")
    }

    function prt_hdr_cells(s,    numCells,f,i) {
        numCells = split(s, f)
        for (i = 1; i <= numCells; i++) {
            prt_cell("th",f[i])
        }
        return numCells
    }

    function prt_data_cells(numHdrCells,s,    f,i) {
        split(s, f)
        # Force the number of cells printed for each data row
        # to match the number of cells printed for the header.
        for (i = 1; i <= numHdrCells; i++) {
            prt_cell("td",f[i])
        }
    }

    function prt_cell(cellType,s) {
        Indent += 2
        # Strip any leading/trailing white space around this cell
        gsub(/^[[:space:]]+|[[:space:]]+$/, "", s)
        prt_indented("<" cellType ">" escape_html(s) "</" cellType ">")
        Indent -= 2
    }

    function escape_html(s) {
        gsub(/&/, "\\&amp;", s)
        gsub(/</, "\\&lt;", s)
        gsub(/>/, "\\&gt;", s)
        return s
    }

    function prt_beg(s) { Indent += 2; prt_indented(s) }
    function prt_end(s) { prt_indented(s); Indent -= 2 }
    function prt_indented(s) { printf("%*s%s\n", Indent, "", s) }

' "${@:--}"

$ ./tst.sh test.unl
<html>
  <body>
    <table border=1 cellspacing=0 cellpadding=3>
      <tr>
        <th>SID</th>
        <th>PID</th>
        <th>Username</th>
        <th>Database</th>
        <th>Hostname</th>
        <th>Program</th>
        <th>Connected</th>
        <th>Idle Time</th>
        <th>Query Time</th>
        <th>EST COST</th>
        <th>SEQ SCAN</th>
        <th>Query</th>
      </tr>
      <tr>
        <td>15422216</td>
        <td>-1</td>
        <td>dwhvo</td>
        <td>test</td>
        <td>pd244zax.test.corp</td>
        <td>N/A</td>
        <td>10:56:53</td>
        <td>-0:00:30</td>
        <td>10:57:22</td>
        <td>1045127</td>
        <td>1</td>
        <td>SELECT sba_sub_aux.sba_subscriber_id, sba_sub_aux.sba_id_number, sba_sub_aux.sba_matchcode, sba_sub_aux.sba_marketing, sba_su</td>
      </tr>
    </table>
  </body>
</html>

Stack Exchange Network

Convert pipe delimited column data to HTML table format for email

4 Answers 4

You must log in to answer this question.

Hot Network Questions

Convert pipe delimited column data to HTML table format for email

4 Answers 4

You must log in to answer this question.

Related

Hot Network Questions