Export dataset to CSV and send as email attachment

Ignition
1

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?

2

Instead of using system.dataset.exportCSV use system.dataset.toCSV. Then use system.net.sendEmail to attach the data and send

1 Like
3

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

1 Like
4

You probably could use 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.

5

Use system.dataset.toCSV(), then use Java’s StandardCharsets.UTF_8.encode(theCSVstring).array() to get the bytes for system.net.sendEMail().

5 Likes
6

Ive done something similar using:

			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

3 Likes
7

Yet another option:

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

Hi Guys, here’s my script.

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", subject= subject, body=body, html=0, to=recipients, cc=cc_recipients,[filename],[excelbytes])\n’))

2 Likes
9

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

1 Like
10 

#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

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")
11

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.

1 Like
12

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.

13

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

1 Like
14

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


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)
1 Like
15

How do I synthesis that to my code?

Is it something like this?:

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?

16

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.

1 Like
17

Thanks

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

I also have this <end> text showing up.
image

18

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.

19

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.