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

**URL:** <https://forum.inductiveautomation.com/t/is-it-possible-to-include-sql-data-in-the-email-body/102612>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [February 20, 2025, 7:41am UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-include-sql-data-in-the-email-body/102612 "2025-02-20T07:41:14Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Rajashekar\_Reddy](https://avatars.discourse-cdn.com/v4/letter/r/c0e974/32.png) [@Rajashekar\_Reddy](https://forum.inductiveautomation.com/u/Rajashekar_Reddy)\
**Post date:** [February 20, 2025, 7:41am UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-include-sql-data-in-the-email-body/102612/1 "2025-02-20T07:41:14Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/4/24c2b731c6c81f5c6b953c7945afb30cb441bbe2.jpeg)

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 20, 2025, 1:24pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-include-sql-data-in-the-email-body/102612/2 "2025-02-20T13:24:39Z")

</div>

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

[Here](https://forum.inductiveautomation.com/t/view-dataset-from-script-console/36704/4) 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>`

---

<div class="post-metadata">

**Author:** ![Benjamin\_Furlani](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/benjamin_furlani/32/66084_2.png) [@Benjamin\_Furlani](https://forum.inductiveautomation.com/u/Benjamin_Furlani)\
**Post date:** [February 20, 2025, 3:00pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-include-sql-data-in-the-email-body/102612/3 "2025-02-20T15:00:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [February 20, 2025, 4:39pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-include-sql-data-in-the-email-body/102612/4 "2025-02-20T16:39:45Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/f/0fd209d0ee0f874c2319891d7d9fb410703ce909.png)

```python
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)))

```

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [February 20, 2025, 5:09pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-include-sql-data-in-the-email-body/102612/5 "2025-02-20T17:09:22Z")

</div>

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 😉

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [February 20, 2025, 5:18pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-include-sql-data-in-the-email-body/102612/6 "2025-02-20T17:18:42Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/a/2a2a496a99b72b02a3e66efe082f7bf38c8f8e30.png)

---

<div class="post-metadata">

**Author:** ![Rajashekar\_Reddy](https://avatars.discourse-cdn.com/v4/letter/r/c0e974/32.png) [@Rajashekar\_Reddy](https://forum.inductiveautomation.com/u/Rajashekar_Reddy)\
**Post date:** [February 21, 2025, 10:05am UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-include-sql-data-in-the-email-body/102612/7 "2025-02-21T10:05:02Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [February 21, 2025, 10:22am UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-include-sql-data-in-the-email-body/102612/8 "2025-02-21T10:22:21Z")

</div>

3: Create a gateway scheduled event. Write a script in there.  
2: [system.db.runQuery | Ignition User Manual](https://www.docs.inductiveautomation.com/docs/8.1/appendix/scripting-functions/system-db/system-db-runQuery)  
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](https://www.docs.inductiveautomation.com/docs/8.1/appendix/scripting-functions/system-net/system-net-sendEmail)