UDFs - CSV to HTML Table (with basic formatting!)

This sample UDF takes a CSV (string of Text) as input and returns an HTML table, with some basic formatting applied.

Given the following input (dataType: Text):

my,csv,headers
this,is,one row
this,is,another row
and this,here is,yet another row

Let’s build a UDF that takes the CSV above and returns an HTML table. To build it out we’ll be concatenating the text strings:

  • style tag
  • table tag and children:
    • theader
    • tbody

The Function Body is:

"<style>
  .tb { border-collapse: collapse; }
  .tb th, .tb td { padding: 5px; }
  .tb th { background-color: #777; border: solid 1px #f4f4f4; color: white;}
  .tb td { border: solid 1px #777; }
</style>
  <table class=\"tb\">{{
  CONCAT(
    "<thead><tr>",
    JOIN(
      FROM
        th
      IN
        SPLIT(
          JOIN(
            FROM row IN SPLIT(csv, "\n") WHERE index = 0 SELECT row
          ),
          ","
        )
      SELECT
        "<th title=\"{{th}}\">{{th}}</th>"
    ),
    "</tr></thead>"
  )
}} 
<tbody>{{
  JOIN(
    FROM
      tr
    IN
      FROM row IN SPLIT(csv, "\n") WHERE NOT(index = 0) SELECT row
    SELECT
      "<tr>{{JOIN(FROM td IN SPLIT(tr, ",") SELECT "<td>{{td}}</td>")}}</tr>"
  )
}}
  </tbody>
  </table>"

And the HTML output will look like:

<style>
  .tb { border-collapse: collapse; }
  .tb th, .tb td { padding: 5px; }
  .tb th { background-color: #777; border: solid 1px #f4f4f4; color: white;}
  .tb td { border: solid 1px #777; }
</style>
  <table class="tb">
    <thead>
      <tr>
         <th title="my">my</th>
         <th title="csv">csv</th>
         <th title="headers">headers</th>
       </tr>
    </thead>
    <tbody>
       <tr>
         <td>this</td>
         <td>is</td>
         <td>one row</td>
       </tr>
       <tr>
         <td>this</td>
         <td>is</td>
         <td>another row</td>
       </tr>
       <tr>
         <td>and this</td>
         <td>here is</td>
         <td>yet another row</td>
       </tr>
    </tbody>
  </table>

The HTML can then be used as the body of an HTML email to display reporting data, etc.

Screen Shot 2022-02-11 at 23.34.41

TIP: Play around with the UDF’s Inputs to allow dynamic formatting (<style></style>), or getting the headers and the table rows from different sources.

1 Like

This is awesome! Do you have a way to take JSON to HTML? I can see this being really useful for taking JSON data and sending it as a table through an email using the Send Email data operation.

1 Like

Yeaaah! coming later this week (now I wonder if that should be ‘sooner’ this week rather than ‘later’)

1 Like