Parsing CSV Instrument Interface

Hello All!

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

Thanks a lot! :prayer:
ejemplo.csv (4.29 KB)

Sam:

Is this a one time thing or ongoing?

If it is onetime, You can use SQL server’s import wizard to do it.

Is ongoing, it is possible do it using Instrument Interface?

I use this guy all the time. I havent tried it with anything more than a handful of columns though, and I mostly just use it for a one time load.

viewtopic.php?f=25&t=7122&p=21163&hilit=csv+parser#p21163

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)

Hi Sam,

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.

-Pete Low
Inductive Automation

Thanks for your answer!

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)

Am I doing something wrong?

Hi,

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.

Regards,
Pete Low

I installed the version 1.5.6 BETA 4 and now is working! thanks for your help! :thumb_left:

I just wanted you to know that I am using the basis of what you wrote here in Oct. 2018. Thanks so much!!! Clint

That’s good to hear!