Tag to Excel

Looking for the best option to write a few tags to Excel for a customer at 5 min interval.
In the following format

Tagname Date Value Units

e.g. Tag1 11:00 6 m3/h
Tag2 11:00 6.3 m
Tag3 11:00 5.5 bar
Tag1 11:05 6.2 m3/h
Tag2 11:05 7.1 m
Tag3 11:05 3.5 bar

I tried using a query and writing the dataset to excel but they were coming out in columns
ie t_stamp Tag1 Tag2 Tag3
11:00 6 6.3 5.5
11:05 6.2 7.1 3.5

Once you have your dataset from the query, you can rearrange through scripting.

In the attached window, I made a table with your example dataset, there’s a button to process it and display in another table, and another button to clear the results.

Here’s the meat of the script:

[code]# get input dataset
dataIn=system.dataset.toPyDataSet(event.source.parent.getComponent(‘Table In’).data)

get column names from input dataset

headersIn = dataIn.getUnderlyingDataset().getColumnNames()

initialize output data

headersOut = [‘Tagname’,‘Date’,‘Value’,‘Units’]
units_list = [’’,‘m3/h’,‘m’,‘bar’]

process data

for row in dataIn:

This might look strange, but pyhton lets you iterate theough multiple lists in parallel.

The zip() function ties the lists together

for header, col, unit in zip(headersIn, row, units_list):
#set timestamp value, otherwise, add to output data
if header == ‘t_stamp’:
ts = col
dataOut.append([header, ts, col, unit])

datasetOut = system.dataset.toDataSet(headersOut, dataOut)
event.source.parent.getComponent(‘Table Out’).data = datasetOut

Thanks Jordan, will give it a try

I have added and manipulated all the data and have it writing to excel with the touch of a button.

I now need to automate it that it writes to file once a day - via gateway script I presume.
except I don’t seem to be able to access the window/table from the gateway script.

Window components are not accessible to the gateway.

The gateway script should be similar to what you have, you will just need to export the manipulated dataset to Excel directly instead of sending it to a table first.

I figured that eventually!

Instead of a table using Tag History on a window, I used a query tag to get the data and then the gateway script accessed the tag and manipulated the dataset as required with your script.
Thanks again for your input as I had hit a brick wall up to that :prayer:

Hey guys, was this application writing to an existing Excel file, or to a new one?

I’m trying to write tag data into an existing macro-enabled spreadsheet to a specific tab, and specific intervals. Have either of you dealt with this or know where I could get some direction on this? Or better yet, know someone who would like to be hired to handle the code for me?