.xml or .txt file automatically dumping to SQL

General Discussion
1

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?

2

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.

3

It looks like the attached file.

Stream.xml (1.2 KB)

4

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:
			headers.append(child.tag)
		newRow.append(child.text)
		flatList.append(child.text)
	data.append(newRow)

# 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)
2 Likes