Is it possible to include SQL data in the email body?

Ignition
1

Hi,

I am trying to send an email with SQL database data embedded directly into the email body. We've tested this using the reporting module, and it works by attaching files like PDFs, Excel spreadsheets, and CSVs. However, while the customer is fine with the attachments, they also want the data to be included in the email body itself.

To simplify my requirements, I am attaching an image of the data from the SQL database. I can send an email by attaching the report as a PDF, but we need the same text to be displayed in the body itself.

Is it possible to include SQL data in the email body?

IMG_20250220_123029
IMG_20250220_1230291920×1002 341 KB

2

I would format the dataset as a string and append it to the email body.

Here is a link to a print dataset function written by @JordanCClark that you could modify for your requirements. You could use html formatting to get newlines etc., ie <html>header<br>row1<br>row2</html>

4 Likes
3

Another option would be to use a the <table>,<tr>,<th>,and <td> html tags.

This may be friendly to copy and paste from email to excel or sheets.

3 Likes
4

image
image889×382 15.4 KB

def toHtml(dataset):
	
	baseTable   = '<table>{}{}</table>'
	baseThead   = '<thead>{}</thead>'
	baseTbody   = '<tbody>{}</tbody>'
	baseColName = '<th scope="col">{}</th>'
	baseRow     = '<tr>{}</tr>'
	baseItem    = '<td>{}</td>'
	
	thead = baseThead.format(''.join([baseColName.format(colName) for colName in list(dataset.columnNames)]))
	
	rowListOut = []
	for row in system.dataset.toPyDataSet(dataset):
		rowListOut.append(baseRow.format(''.join([baseItem.format(item) for item in list(row)])))
	
	tbody = baseTbody.format(''.join([row for row in rowListOut]))
	
	return  baseTable.format(thead, tbody)


dataset = util.dataset.sampleData(5,6)
html = '''<html>
<head>
<style>
table {{
  font-family: arial, sans-serif;
  border-collapse: collapse;
  width: 50%;
}}

td, th {{
  border: 1px solid #dddddd;
  text-align: left;
  padding: 8px;
}}

tr:nth-child(even) {{
  background-color: #dddddd;
}}
</style>
</head><body>This is a table.<br><br>{tableIn}'''

path = '/home/jordanc/test.html'
system.file.writeFile(path, html.format(tableIn=toHtml(dataset)))
5 Likes
5

Remember that email bodies use some horrendous bastardized version of HTML that's got wildly different support across clients.

I don't know if that will actually cause issues for this simple case, but OP be sure to test in the actual email client(s) you care about before declaring mission accomplished :wink:

5 Likes
6

Taking out the style and adding the border to the table itself may make it a bit more email client friendly.

baseTable = '<table border=1>{}{}</table>'

image
image231×257 4.43 KB

4 Likes
7

Dear @JordanCClark, thanks for your replay.

I am a bit confused about the following:

  1. How do I get the data into HTML format as a string? Where should I write my MS-SQL query?
  2. For example, my query is: SELECT a, b, c, d FROM table WHERE timestamp BETWEEN '2025-01-01' AND '2025-01-01'.
  3. How can I send an email at 6 AM every day?
8

3: Create a gateway scheduled event. Write a script in there.
2: system.db.runQuery | Ignition User Manual
1: Include @JordanCClark's toHTML function in your code and call it while passing the results of step 2.
0: system.net.sendEmail | Ignition User Manual

2 Likes