I have a csv document and I would like to store data from that document into a sql database. I was told that I can use Instrument interface module but i don’t know where I should start (after several hours trying). Can anybody help me??
I thought I’d try doing this in Python … and got a bit carried away! Anyway, here it is in case it’s useful. I imagine this would go into a Gateway Timer script:#Allow Ignition to use 'with'
from __future__ import with_statement
import csv
#Open and read the csv file - 'with' will automatically close it when it finishes
with open('ejemplo.csv', 'rb') as csvfile:
#Return an iterator of lists
rows = csv.reader(csvfile, delimiter=';')
#Read the first line as a list of fieldnames
fieldnames = rows.next()
#Throw away the first 2 column names
fieldnames = fieldnames[2:]
#Replace spaces in the fieldnames with underscores
fieldnames = [field.replace(' ', '_') for field in fieldnames]
#Add a new 'time' fieldname at the start of the list
fieldnames = ['time'] + fieldnames
#Create a comma separated string from the fieldnames
fieldnames = ','.join(fieldnames)
#Start building the query string
query = "INSERT INTO tableName (%s) VALUES " % fieldnames
for row in rows:
#Concatenate the date and time into one field
time = "%s %s" % (row[0], row[1])
#Remove the separate date and time fields from the list of data values
data = row[2:]
#Add the combined date and time field to the start of the list
data = [time] + data
#Create a string from the data values, wrapping each value in single quotes
data = ','.join(["'%s'" % d for d in data])
#Add this line of data to the query
query += "\n(%s)," % data
#Remove the trailing comma
query = query[:-1]
#Run the query
system.db.runUpdateQuery(query)
The latest beta release of the Instrument Interface module will be out today or tomorrow. It will be able to handle a CSV file with the large number of columns that you are trying to import.
Currently when you paste the contents of that file in the Parse Template of an Instrument Configuration the text is wrapping so you cannot define a CSV parsing box.
Once you create a CSV Parsing Box around the text you can then define each column that will be parsed.
In the client you use a File Monitor Controller component and assign it the Instrument Interface Name you defined in the Instrument Interface Configurations.
On the File Monitor Controller event onAfterParse you could use this code to get all the data read into an ignition data table:
results = event.getParseResults()
if results != None:
if results.isRequiredValid():
ds=results.createDataset("CSVColumnBox")
event.source.parent.getComponent('Table').data=ds
(CSVColumnBox is the default name of a CSV column parsing box.)
The Instrument Interface manual can be downloaded from the Inductive Automation website downloads page. It is at the bottom of the page under User Manual.
what I did was create a template and configure the component with the script and this is what i got:
ERROR [BasicExecutionEngine-DesignerExecEngine-thread-4] Task filemonitorcontroller file monitor controller-monitor threw uncaught exception.
java.lang.NoClassDefFoundError: Could not initialize class org.python.core.PyTraceback
at org.python.core.PyException.tracebackHere(PyException.java:158)
at org.python.core.PyException.tracebackHere(PyException.java:145)
at org.python.core.PyTableCode.call(PyTableCode.java:169)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1275)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:548)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:155)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:266)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:55)
at sun.proxy.$Proxy16.onAfterParse(Unknown Source)
at com.sepasoft.parse.components.PMIFileMonitorController.fireAfterParseEvent(PMIFileMonitorController.java:420)
at com.sepasoft.parse.components.PMIFileMonitorController.internalReadFile(PMIFileMonitorController.java:575)
at com.sepasoft.parse.components.PMIFileMonitorController.internalRead(PMIFileMonitorController.java:494)
at com.sepasoft.parse.components.PMIFileMonitorController.access$100(PMIFileMonitorController.java:66)
at com.sepasoft.parse.components.PMIFileMonitorController$FileMonitorRunner.run(PMIFileMonitorController.java:649)
at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:573)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask$Sync.innerRunAndReset(Unknown Source)
at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Please confirm you have the latest Instrument Interface module. It should be 1.5.6 BETA 4.
Also check the memory on the machine. The error trace shows a Python error that is unusual in that it did try to call the after parse event, which means it did parse the file. But it could not give a reason why it failed.
If you need more help please call the Inductive Automation support line and ask for me, Pete Low.