Please be forewarned, this is only my second Ignition project and Java/Python scripting is not my strong suit… (I’m a recovering FactoryTalk user)
I have a handful of tags of which History is enabled and logged to a local mySQL database. Per my customers requirement, I need to export this historical data to a CSV file at the end of each day (new file each day).
I’ve been looking at the dataset.exportCSV scripting (https://docs.inductiveautomation.com/display/DOC80/system.dataset.exportCSV) and it seems to be on the right path however it appears to just pull data from a table on a window and export to CSV.
Is there a way to pull data from a database, with a specified date/time range, and export to CSV using a time based trigger?
Thanks in advance for any insight!
v8.0.12, Vision Module
Yes, it's possible.
However, whilst selecting data from a database that has been entered using the "Store History" option is not impossible, it certainly makes it a bit harder.
It would be much easier if the tags got logged into the database via e.g. a transaction group, if you have that module, if not you can write your own script... however depending on the quantity of tags/complexity, it may take longer in man-hours than just buying the TG module.
1 Like
Thanks for the response.
I do have Transaction Groups available, do you have any further insight using this method?
Sure, can you give me an example of this.
Do you mean, e.g. every day at 8pm, pull the last 12 hours (shift data) from the DB and insert this to CSV
Close, yes.
The exact scenario would be: at 12:00am on Tuesday, pull all the logged data from Monday and create a new CSV file with Monday’s date in the filename. This would happen every in the background every day, with a new CSV file each day, with no operator intervention required (buttons, acknowledgments, etc.)
OK. Are you already using the TG’s i.e. are you familiar with them? And how is your SQL?
If not familiar with TG’s check out a few IU videos on them while I write a few scripts for your example…
We used some TG’s on my last project, so I have an example and some experience.
As far as SQL, my knowledge is very limited. Just for clarification, we are using MariaDB (mySQL) with HeidiSQL on this particular project (not sure if that matters).
Set the TG up right and you won’t have to do much SQL. Even so, the SQL Syntax between the flavours you listed should be fairly cross-flavour
Do you want any summation etc or just, e.g. if you are logging the tag data every minute, you want the raw data?
Ok great.
Nope, just the raw data would be great.
OK, caveat’s;
- I threw this together
- The PC I was at had 7.9, you will need to make any 8.0 specific changes
- This is doing a scalar return, ok if you have a couple of variables, if you have lots you will need different, more efficient method
- It’s semi-pseudo code with the hashes for the SQL calls, just to give you an idea
import system
def DailyData():
import system
import datetime as DT
import os
import csv
from java.util import Date, Calendar
system.util.getLogger("Daily_Data").info("Daily Data report triggered")
now = Calendar.getInstance()
now.add(Calendar.DATE, -1)
yday = Date(now.getTimeInMillis())
yesterday = system.db.dateFormat(yday,"YYYY/MM/dd")
print yesterday
def write_file(yesterday):
#row_count = system.db.runScalarPrepQuery("select count(*) from database.table_name where datetime = (?), [yesterday]")
row_count = 10 # for testing only
print "rows: " + str(row_count)
#index = system.db.runScalarPrepQuery("select min(table)_index) from database.tablename where left(datetime, 10) = ? ", [yesterday])
index = 77651 # for my testing only
print "Target index: " + str(index)
#path to store the file, make sure you double up all back slashes as shown
pathw = '\\\\C:\\Temp\\csv\\'
filename = yesterday.replace('/','_')
filew = str(filename)+".CSV"
filepathw = pathw + filew
try:
fo = open(filepathw, "ab")
fw = csv.writer(fo, delimiter=',')
#Write the headers to the file
print "w is 0, first line of file, this is reserved for the headers"
fw.writerow(["Widgets", "Time_Stamp"])
#Write the data
for x in range(0, row_count):
#Widget = system.db.runScalarPrepQuery("select widget_column_name from database.tablename where left(datetime, 10) = ? and index = (?)", [yesterday, index])
#Time_stamp = system.db.runScalarPrepQuery("select datetime from database.tablename where left(datetime, 10) = (?) and index = (?)", [yesterday])
fw.writerow(["this is the widget", "this is the timestamp"]) # replace with yours ont static text
index = index + x
fo.close()
except Exception,e:
print(e)
system.util.getLogger("Daily_Data").info("Error running Daily Data report")
finally:
fo.close()
system.util.getLogger("Daily_Data").info("CSV Output for Daily Data report")
write_file(yesterday)
DailyData()
#Create a Gateway Timer Script, insert the code below
#Set the gateway timer script to execute every 750mS, with dedicated threading
#Run a script at a certain time
from time import localtime, strftime
curTime = strftime("%H:%M:%S", localtime())
print curTime
if curTime == "00:00:00":
#At midnight, run the shared script DailyData
shared.toCsv.DailyData()
#Notes: While this is set to midnight, this isn't a great idea. Because in the script we do "select * from yesterday...",
#if the time sync is off then this will skew the data you want.
#So for example if the CSV is only going to be looked at, generally at 7AM when the new day shift starts at,
#then run it at "00:15:00" as an example
################################
Also, there's a single function, system.dataset.toCSV, that can write the entire contents of a dataset to csv, then you can use the system.file.write function to append it to the csv file.
Check out the example in the help
Edit: whoops, looks like I didn't read all of your OP... you already mentioned the exportCSV function (which really combines the above), although you have more flexibility with separating them, such as being able to append the data to an existing file
1 Like
Wow, thank you so much for taking the time to put this together.
This is a bit more complex than I anticipated (due to my inexperience), but thorough enough that I think I can manage.
Just a few questions that jump out:
-
You mentioned this would work for a “couple” of variables. I will have roughly 30-40 variables, does that exceed the efficiency limits of this method?
-
Would this be created as a Named Query, with the type Scalar Query?
-
What are the commented lines for Widget and Time_stamp in the for loop used for?
Thanks again!
If all you’re after is to record a set of tag histories to CSV each day, then you can use this within a gateway timer script, I would execute it around 2am in case of any DST:
paths = []
paths.append('[default]tagPath1')
paths.append('[default]tagPath2')
paths.append('etc...')
now = system.date.now()
endDate = system.date.addDays(now,-1)
endDate = system.date.setTime(endDate, 23,59,59)
startDate = system.date.setTime(endDate, 0,0,0)
returnSize = -1 # return the raw data, not sampled
filePath = r'C:\Temp\History_%s.csv' % system.date.format(endDate, "yyyyMMdd")
tag_hist_ds = system.tag.queryTagHistory(paths=paths, startDate=startDate, endDate=endDate, returnSize=returnSize)
tag_hist_csv = system.dataset.toCSV(tag_hist_ds)
system.file.writeFile(filePath, tag_hist_csv)