.xml or .txt file automatically dumping to SQL

I have an existing gate and RFID badge installation that I would like to integrate into Ignition. My options for what I have now is the gate software will continuously update a TXT ot XML file. Are there any options available to where I can get the data from ether of these files into a MySQL database to be used in Ignition other then having to manually importing it?

You could monitor the file for modification in a gateway timer script, and then parse it to update your DB. Java has pretty good built-in XML handling (usable from jython). What that script would look like would depend on the XML structure.

It looks like the attached file.

Stream.xml (1.2 KB)

IIRC, MySQL has INSERT IGNORE, that you may be able to use to avoid duplicate inserts. I don’t have it around to test.

The XML is, thankfully, pretty straightforward and can use ElementTree easily enough.

import xml.etree.ElementTree as ET

filepath = 'C:/Test/stream/Stream.xml'

# Load the element tree
tree = ET.parse(filepath)

# Grab the tree root. It's the outermost tag of the XML
# I like naming the root varible the same as root tag, as it helps
# remind me of the XML structure if I have to go back later. 
transactionFile = tree.getroot()

# Generate all lists to make datasets, or flat list for runPrepUpdate
# We're looping through everthing anyway, so we may as well do it once.
# 'data' will be a list of lists, 'flatList' will be a single list of all the values.
headers = [] 	# column names for either dataset or query
data = []		# to help create dataset
flatList = []	# for use in runPrepUpdate
for transaction in transactionFile:
	newRow = []
	for child in transaction:
		# add to headers if it doesn't already exist
		if child.tag not in headers:

# A dataset is not necessary for this script, but you never know. 
datasetOut = system.dataset.toDataSet(headers, data)
# Generate db query	
tableCols = '(' + ', '.join(headers) + ')'
valueString = ', '.join(['(' + ','.join('?' * len(headers)) + ')'] * len(data))

# INSERT IGNORE is a uniquely MySQL thing. YMMV.
query = 'INSERT IGNORE INTO table '+ tableCols + ' VALUES ' + valueString

# Run the query
system.db.runPrepUpdate(query, flatList)