Tuesday, September 6, 2016

Send simple tabular data emails from a database

It's possible to send emails containing tabular data using Pentaho. The setup is a bit labyrinthine and it requires a bit of noodling with HTML/XSL, but once it's set up it works very well.

There are three main steps, plus cleanup:

  1. Create an XML file from the data that you want to send (Make XML transformation)
  2. Use an XSL file to transform the XML into HTML (XSL Transformation job entry)
  3. Place the HTML into a single field which will form the body of the email, and send the email (HTML to email transformation)
  4. Delete files






Make XML

This transformation outputs an XML file called raw_xml.xml, containing data from the Data Grid step. You could substitute a database query for the Data Grid step.



For this example, I made a simple dataset consisting of information about the five American Great Lakes.







Here's what the XML looks like:

XSL Transformation


The XSL transformation converts the XML file into an HTML file. For this step to work, you need an XSL file. Here's what my html_structure.xsl looks like:



Setting up the XSL transformation is easy, using the XML file created in the previous transformation:




HTML to email

The HTML has to be input into a single field (called "html" here) using the "Group By" step. Then you can add any constants needed to send the email, such as login info and subject line.




The html field will be the field used for the Comment entry in the Mail step.



Delete files

And then we do cleanup!


Here's how the output looks in my gmail account.

It's not pretty, but it works, and someone with HTML chops can make it look fantastic.

If anyone knows how to do this without creating the intermediate XSL and HTML files, I'm all ears. I played around with passing the rows between entries and didn't get anywhere.

Stay tuned for part 2, which will show how to get tabular data out of emails and put it into a database.

6 comments:

  1. Managing of your data is very easy but when we looking for managing the big data of the big companies than it’s not easy for us. For making this task easier for us the data scientist are available for hiring and you can hire the best data scientists here at form this activewizards.com/.

    ReplyDelete
  2. The trade-off is that while relational databases are more powerful, they also require more time to master.
    oracle dashboards

    ReplyDelete
  3. This isn't anything but difficult to deal with your whole database for you on the grounds that the administration of database can be taken care of by the expert information researcher who is the master in information taking care of. Be that as it may, this is likewise difficult to employ the best information researcher so you could check here the original source Active Wizards for a contract the best information researcher for your database.

    ReplyDelete
  4. Most large systems have a need to store massive amounts of process and commercial data. Different databases often need to work together. Learning differences between databases helps in selecting the best database for your situation.dbdesigner.net

    ReplyDelete
  5. Defilement makes SQLite database distant. In such circumstance,dbdesigner.net you have three choices:

    ReplyDelete
  6. Indeed, even fresh out of the plastic new hard drives are not absolutely insusceptible to having electrical disappointments. data recovery manchester uk

    ReplyDelete