# Export dataset to CSV and send as email attachment

**URL:** <https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529>\
**Category:** Ignition\
**Created:** [July 9, 2020, 9:39am UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529 "2020-07-09T09:39:30Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![jmacascas](https://avatars.discourse-cdn.com/v4/letter/j/aeb1de/32.png) [@jmacascas](https://forum.inductiveautomation.com/u/jmacascas)\
**Post date:** [July 9, 2020, 9:39am UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/1 "2020-07-09T09:39:30Z")

</div>

Hi Good day!  
I just want to ask some idea from you guys. is it possible to export dataset to CSV file without saving to a file directory? what i want to do after exporting the dataset is automatically send as file attachment in email?

---

<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:** [July 9, 2020, 12:00pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/2 "2020-07-09T12:00:35Z")

</div>

Instead of using `system.dataset.exportCSV` use [system.dataset.toCSV](https://docs.inductiveautomation.com/display/DOC80/system.dataset.toCSV). Then use [system.net.sendEmail](https://docs.inductiveautomation.com/display/DOC80/system.net.sendEmail) to attach the data and send

---

<div class="post-metadata">

**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\
**Post date:** [July 9, 2020, 12:03pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/3 "2020-07-09T12:03:27Z")

</div>

I don’t think your suggestion will work since sendEmail() requires byte data.

@jmacascas, have a look at this: [Email with Excel file Attached without prompting](http://forum.inductiveautomation.com/t/email-with-excel-file-attached-without-prompting/7758)

---

<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:** [July 9, 2020, 12:06pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/4 "2020-07-09T12:06:50Z")

</div>

You probably could use [system.file.readFileAsBytes](https://docs.inductiveautomation.com/display/DOC80/system.file.readFileAsBytes) after you export the CSV.  
**EDIT**  
Another solution would be to use a report if you have the reporting module. Then you can email, FTP, print, and or save the file fairly easy.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 9, 2020, 12:52pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/5 "2020-07-09T12:52:42Z")

</div>

Use system.dataset.toCSV(), then use Java’s [StandardCharsets](https://docs.oracle.com/javase/8/docs/api/java/nio/charset/StandardCharsets.html)`.UTF_8.encode(theCSVstring).array()` to get the bytes for system.net.sendEMail().

---

<div class="post-metadata">

**Author:** ![diat150](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@diat150](https://forum.inductiveautomation.com/u/diat150)\
**Post date:** [July 9, 2020, 2:29pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/6 "2020-07-09T14:29:28Z")

</div>

Ive done something similar using:

```auto
			csv = system.dataset.toCSV(your dataset here)
			from org.python.core.util import StringUtil
			email = []
			email.append('your@email.com')
			shared.app.email.send(email,'Subject here','Message here', attachmentNames = ['filename.csv'],attachmentData=[StringUtil.toBytes(csv)])

```

shared.app.email.send is just a script that wraps around system.net.sendEmail

---

<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:** [July 9, 2020, 4:45pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/7 "2020-07-09T16:45:09Z")

</div>

Yet another option:

```auto
from java.lang import String
String(system.dataset.toCSV(<dataset>)).getBytes("UTF-8")

```

---

<div class="post-metadata">

**Author:** ![jmacascas](https://avatars.discourse-cdn.com/v4/letter/j/aeb1de/32.png) [@jmacascas](https://forum.inductiveautomation.com/u/jmacascas)\
**Post date:** [July 10, 2020, 12:56am UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/8 "2020-07-10T00:56:09Z")

</div>

Hi Guys, here’s my script.

```auto
from datetime import datetime 

myDataset=system.tag.read("[default]Machine/machine1/Stats/Monitoring/TimeRecord").value
hostName = system.tag.read("[default]Machine/machine1/Hostname").value

#Iterate the dataset
for row in range(myDataset.getRowCount()):
	for col in range(myDataset.getColumnCount()):
		myDataset.getValueAt(row, col) 

excelstr = system.dataset.dataSetToExcel(1,[myDataset])
excelbytes = excelstr.encode("UTF8")
filename = hostName +"_"+(datetime.today().strftime("%m-%d-%y - %H %M %S"))+ ".xls"

body = "<HTML><BODY><H1>Test101</H1>"
#body += "This is a system generate email <font color='black'>black</font></BODY></HTML>"
recipients = ["jmacascas@email.com"]
cc_recipients = [""]
smtp_server = "default"
subject = "Here is the file you requested"
system.net.sendEmail(smtpProfile=smtp_server, fromAddr="ignition@email.com", subject= subject, body=body, html=0, to=recipients, cc=cc_recipients,[filename],[excelbytes])

```

but i’m getting this syntax error.  
SyntaxError: (‘non-keyword arg after keyword arg’, (’’, 26, 146, ‘system.net.sendEmail(smtpProfile=smtp\_server, [fromAddr="ignition@email.com](mailto:fromAddr=%22ignition@email.com)", subject= subject, body=body, html=0, to=recipients, cc=cc\_recipients,[filename],[excelbytes])\n’))

---

<div class="post-metadata">

**Author:** ![jmacascas](https://avatars.discourse-cdn.com/v4/letter/j/aeb1de/32.png) [@jmacascas](https://forum.inductiveautomation.com/u/jmacascas)\
**Post date:** [July 10, 2020, 6:36am UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/9 "2020-07-10T06:36:14Z")

</div>

Hi Code\_skin,

Thank you for sharing the link. I got some idea and able to solve my problem.  
my script is now working using system.dataset.exportExcel

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [August 29, 2023, 8:09pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/10 "2023-08-29T20:09:47Z")

</div>

```auto

#Export Datasets
today = system.date.now()
fileSuffix	= system.date.format(today, "yyyyMMddHHmm")
fileName = 'Results' +fileSuffix+'.xlsx'
ss = system.dataset.toExcel(True, [dataOut],
sheetNames = ['Results'])

#end=time.clock()
#print end-start

#filePath = system.file.saveFile(fileName)
#if filePath is not None:
#	system.file.writeFile(filePath, ss)
#print dataOut

excelBytes = ss.encode("UTF8")

```

**I am getting an error that array.array object has no attribute 'encode'.**

**How do make a workbook and get the binaries?**

* * *

I can get it as a CSV except the datetimes come with the wrong format.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/e/4e9fdef17c4ec1dd36fb49c36d143a0a1ad5afae.png)

```auto
from java.lang import String

dataOut = system.db.runNamedQuery('test1')

#Export Datasets
today = system.date.now()
fileSuffix	= system.date.format(today, "yyyyMMddHHmm")
fileName = 'Results' +fileSuffix+'.csv' #.xlsx'
ss = system.dataset.toExcel(True, [dataOut],
sheetNames = ['Results'])

#end=time.clock()
#print end-start

#filePath = system.file.saveFile(fileName)
#if filePath is not None:
#	system.file.writeFile(filePath, ss)
#print dataOut

#excelBytes = ss.encode("UTF8")
excelBytes = String(system.dataset.toCSV(dataOut)).getBytes("UTF-8")

```

---

<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:** [August 29, 2023, 8:20pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/11 "2023-08-29T20:20:15Z")

</div>

If you pay attention to the return types of the system functions you are using, you'll see that `toExcel` returns a byte array and `toCSV` returns a string. When using `toExcel` you are assuming it is a string that needs to be encoded as utf-8 bytes, but it already is.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [August 29, 2023, 8:29pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/12 "2023-08-29T20:29:50Z")

</div>

Well, it kind of improved.

Thanks @dkhayes117, I made it an .xlsx file extension again.  
Set the `excelBytes = ss`

The RecordedDate now shows as the date, but the time doesn't show.

Though, it also says "protected view, be careful attachments can cause viruses" lol.  
Then asks if I want to enable the editing.

I think another few hours of this, and I will have it finished maybe.  
I mean this is emailing me correctly with the quark that RecordedDate comes as date format instead of date and time. I have to put time in getting the correct dataset.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [August 29, 2023, 9:15pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/13 "2023-08-29T21:15:23Z")

</div>

That is just the default excel format.  
Select the column, format cells, add in the time to the format string.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [August 29, 2023, 9:18pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/14 "2023-08-29T21:18:49Z")

</div>

Dug up some of my old export to excel code that addresses that issue.

```auto

from com.inductiveautomation.ignition.common import BasicDataset
tDS = event.source.parent.getComponent('Power Table').data
pDS = system.dataset.toPyDataSet(tDS)
hdr = []
hdr.append('t_stamp')
for row in pDS:
	hdr.append(row[1])
dataset = BasicDataset(event.source.parent.getComponent('Power Table 1').data)
dataset.setColumnNames(hdr)

def SetDateFormat(excelXMLStr):
	"""Adds seconds to datetime format for time stamp field"""
	# Add new xml style type
	withStyle = excelXMLStr.replace('<Styles>', ('<Styles>'
		'<Style ss:ID="s65">'
		'<NumberFormat ss:Format="m/d/yyyy\ h:mm:ss"/>'
		'</Style>'))

	withStyle = withStyle.replace('<Cell ss:Index="1" ss:StyleID="s21">',
		'<Cell ss:StyleID="s65">')
		
	return withStyle
	
secondsFormat = SetDateFormat(system.dataset.dataSetToExcel(True, [dataset]))
	
savePath = system.file.saveFile('Export.xls', 'xls', 'Excel sheet')
system.file.writeFile(savePath, secondsFormat)
if savePath != None:
   system.net.openURL("file://"+savePath)

```

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [August 30, 2023, 5:49pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/15 "2023-08-30T17:49:41Z")

</div>

How do I synthesis that to my code?

Is it something like this?:

```auto
ss = system.dataset.toExcel(True, [dataOut], sheetNames = ['Results'])
ss = ss.replace('<Styles>', ('<Styles>'
		'<Style ss:ID="s65">'
		'<NumberFormat ss:Format="m/d/yyyy\ h:mm:ss"/>'
		'</Style>'))
ss = ss.replace('<Cell ss:Index="1" ss:StyleID="s21">',
		'<Cell ss:StyleID="s65">')

```

What should I understand that 'StyleID' and 's65' are to me?  
Is import BasicDataset a library that older versions of Ignition required?

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [August 30, 2023, 6:11pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/16 "2023-08-30T18:11:42Z")

</div>

That is an internal style code for Excel, it is setting the value in the export to set the column to use the number format specified.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [August 30, 2023, 6:21pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/17 "2023-08-30T18:21:06Z")

</div>

Thanks

I get an error that array.array does not have the replace attribute.

I also have this `<end>` text showing up.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/f/cfc3ae3240297ce982253e13aa8a38a7974be89b.png)

---

<div class="post-metadata">

**Author:** ![Zach\_Larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zach_larson/32/54388_2.png) [@Zach\_Larson](https://forum.inductiveautomation.com/u/Zach_Larson)\
**Post date:** [February 28, 2024, 9:23pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/18 "2024-02-28T21:23:38Z")

</div>

Having an issue with my csv is cutting off comments.

'Staffing 22/29' is what I see instead of 'Staffing 22/29 Grinding....' very long comment.

I tried a bunch of replace( , , ) in sql. Nothing seems to fix it.

---

<div class="post-metadata">

**Author:** ![Zach\_Larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zach_larson/32/54388_2.png) [@Zach\_Larson](https://forum.inductiveautomation.com/u/Zach_Larson)\
**Post date:** [February 29, 2024, 12:39pm UTC](https://forum.inductiveautomation.com/t/export-dataset-to-csv-and-send-as-email-attachment/36529/19 "2024-02-29T12:39:13Z")

</div>

I think, it might be because the comments are allowed to be so long.  
Hoping I can split it up in SQL.

* * *

Some of the comments are 2000 characters long. I am not sure what to do.

* * *

`, REPLACE(REPLACE(REPLACE(note, CHAR(13), ''), CHAR(10), ''), CHAR(9), '') as note`  
Appeared to be some kind of junk from how comments were being transferred around. These characters are some kind of carriage characters that mess up exports.

`CHAR(13)` represents the carriage return character.  
`CHAR(10)` represents the line feed character.  
`CHAR(9)` represents the tab character.