Export data from mySQL to CSV

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;

  1. I threw this together
  2. The PC I was at had 7.9, you will need to make any 8.0 specific changes
  3. 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
  4. 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

################################

:grin: I think anyone who has used FT and got out is in recovery! My hairline is in recovery from the last project I had to use FT on.

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:

  1. 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?

  2. Would this be created as a Named Query, with the type Scalar Query?

  3. 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)
  1. Yeah just to clarify by variables this equates to SQL columns. A scalar return returns one item by it’s nature. If your TG to SQL is setup right then it’s better to “select * from db.table where datetime between startDate, EndDate” etc, then in Jython you handle the dataSet returned.

  2. It’s good practice to use named queries but the example I provided was rough and ready and doesn’t. You can copy the code above and paste into script console, and in 7.9 anyway it will output a file as is. I will run it on 8.0 next week and see if there are any changes required

  3. They are just examples, pseudo code, because I don’t know your data. If they were not commented out, then the script would fail to run. As per #1, now we know there are 30-40 columns then it’s irrelevant anyway