# Parsing CSV Instrument Interface

**URL:** <https://forum.inductiveautomation.com/t/parsing-csv-instrument-interface/5859>\
**Category:** Ignition\
**Created:** [February 13, 2013, 3:34pm UTC](https://forum.inductiveautomation.com/t/parsing-csv-instrument-interface/5859 "2013-02-13T15:34:06Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![SamJaimes](https://avatars.discourse-cdn.com/v4/letter/s/a6a055/32.png) [@SamJaimes](https://forum.inductiveautomation.com/u/SamJaimes)\
**Post date:** [February 13, 2013, 3:34pm UTC](https://forum.inductiveautomation.com/t/parsing-csv-instrument-interface/5859/1 "2013-02-13T15:34:06Z")

</div>

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:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:")  
[ejemplo.csv](https://us1.discourse-cdn.com/inductiveautomation/original/2X/e/ebe80b2e92b7552d6d9006db5299e9fcf1c18e5e.csv) (4.29 KB)

---

<div class="post-metadata">

**Author:** ![Dravik](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dravik](https://forum.inductiveautomation.com/u/Dravik)\
**Post date:** [February 13, 2013, 5:12pm UTC](https://forum.inductiveautomation.com/t/parsing-csv-instrument-interface/5859/2 "2013-02-13T17:12:04Z")

</div>

Sam:

Is this a one time thing or ongoing?

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

---

<div class="post-metadata">

**Author:** ![SamJaimes](https://avatars.discourse-cdn.com/v4/letter/s/a6a055/32.png) [@SamJaimes](https://forum.inductiveautomation.com/u/SamJaimes)\
**Post date:** [February 14, 2013, 1:02pm UTC](https://forum.inductiveautomation.com/t/parsing-csv-instrument-interface/5859/3 "2013-02-14T13:02:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![diat150](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@diat150](https://forum.inductiveautomation.com/u/diat150)\
**Post date:** [February 14, 2013, 1:44pm UTC](https://forum.inductiveautomation.com/t/parsing-csv-instrument-interface/5859/4 "2013-02-14T13:44:05Z")

</div>

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](http://www.inductiveautomation.com/forum/viewtopic.php?f=25&t=7122&p=21163&hilit=csv+parser#p21163)

---

<div class="post-metadata">

**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\
**Post date:** [February 14, 2013, 5:30pm UTC](https://forum.inductiveautomation.com/t/parsing-csv-instrument-interface/5859/5 "2013-02-14T17:30:43Z")

</div>

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)`

---

<div class="post-metadata">

**Author:** ![Pete.Low](https://avatars.discourse-cdn.com/v4/letter/p/f05b48/32.png) [@Pete.Low](https://forum.inductiveautomation.com/u/Pete.Low)\
**Post date:** [February 14, 2013, 8:07pm UTC](https://forum.inductiveautomation.com/t/parsing-csv-instrument-interface/5859/6 "2013-02-14T20:07:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![SamJaimes](https://avatars.discourse-cdn.com/v4/letter/s/a6a055/32.png) [@SamJaimes](https://forum.inductiveautomation.com/u/SamJaimes)\
**Post date:** [February 25, 2013, 12:25pm UTC](https://forum.inductiveautomation.com/t/parsing-csv-instrument-interface/5859/7 "2013-02-25T12:25:13Z")

</div>

Thanks for your answer!

what I did was create a template and configure the component with the script and this is what i got:

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

---

<div class="post-metadata">

**Author:** ![Pete.Low](https://avatars.discourse-cdn.com/v4/letter/p/f05b48/32.png) [@Pete.Low](https://forum.inductiveautomation.com/u/Pete.Low)\
**Post date:** [February 26, 2013, 1:56am UTC](https://forum.inductiveautomation.com/t/parsing-csv-instrument-interface/5859/8 "2013-02-26T01:56:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![SamJaimes](https://avatars.discourse-cdn.com/v4/letter/s/a6a055/32.png) [@SamJaimes](https://forum.inductiveautomation.com/u/SamJaimes)\
**Post date:** [February 27, 2013, 4:10pm UTC](https://forum.inductiveautomation.com/t/parsing-csv-instrument-interface/5859/9 "2013-02-27T16:10:19Z")

</div>

I installed the version 1.5.6 BETA 4 and now is working! thanks for your help! ![:thumb_left:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e26afb081ddde6df016df6ee49159cf56aaca688.gif ":thumb\_left:")

---

<div class="post-metadata">

**Author:** ![cbanzet](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cbanzet/32/6207_2.png) [@cbanzet](https://forum.inductiveautomation.com/u/cbanzet)\
**Post date:** [October 11, 2018, 6:14pm UTC](https://forum.inductiveautomation.com/t/parsing-csv-instrument-interface/5859/10 "2018-10-11T18:14:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\
**Post date:** [October 11, 2018, 6:27pm UTC](https://forum.inductiveautomation.com/t/parsing-csv-instrument-interface/5859/11 "2018-10-11T18:27:09Z")

</div>

That’s good to hear!