File Upload in Perspective CSV to database

Ignition
1

So I have a script that work in Vision that I would like to get something like it working in Perspective. The vision script is on a button when “actionPerformed” it opens an file browse then insert data to a table in the database. The file is a CSV. I was hoping to do something like this but with the File Upload component in Perspective. All of the examples that a found was uploading the whole file and not trying to get the data out of it. So basically, upload a CSV file then insert the data from each columns into a database and NOT the whole file itself.

Here is the vision script:
filePath = system.file.openFile(‘csv’)
if filePath != None:
import csv
csvData = csv.reader(open(filePath))
csvData.next()
for title, quantity, areaName, bayName in csvData:
namedQuery = ‘BayIdLookUp’
params = {
‘area’:areaName,
‘bay’:bayName,
}
bayId = system.db.runNamedQuery(namedQuery, params)
if bayId != None:
insertQuery = ‘InsertStorageRow’
insertparams = {
‘nq_title’:title,
‘nq_bayId’:bayId,
‘nq_quantity’:quantity,
}
system.db.runNamedQuery(insertQuery, insertparams)
system.db.refresh(event.source.parent.getComponent(‘Power Table’),‘data’)

The CSV look like this:
image

Any help would be greatly appreciated.

2

The examples you found might only be for writing the entire file, but the Event/Action provides the file for your use, so I’m not sure what portion of your script would fail. Did you try this script in Perspective and it failed?

I think the general idea would be to use the script you supplied, but replace the top portion with

import csv
csvData = csv.reader(open(event.file))

I think everything below that should work.

The system.db.refresh call can be removed from the script. If you’re trying to update the binding of a displayed table at the same level of the component hierarchy as the File Upload component and that table has a name of “MyTable” you would do this: self.getSibling('MyTable').refreshBinding('props.data')

If you try that and it fails, reply here with information about the failure and I’ll help more at that point.

3

I did tried it in perspective before posting, and it failed.
So, I replace the top portion and also failed.
I think "filePath = system.file.openFile('csv')" is the failure. I dont think file.openFile is in perspective.

I also simplified the code. Just for testing

filePath = system.file.openFile('csv')
if filePath != None:
import csv
csvData = csv.reader(open(event.file))

	csvData.next()
	for data in csvData:		
	
		namedQuery = 'Add'
		params = {'data':data}
		bayId = system.db.runNamedQuery(namedQuery, params)
4

You can’t open a local file from the client in Perspective. You can provide them a file upload component, and they have to pick the file to supply to you. The event.file in @cmallonee’s reply presumes you’ve added an upload component and placed your code in the onFileReceived event.

5

I trying to use a file upload component in the onFileReceived event. I deleted the "system.file.openFile(‘csv’)” Can you not use the import csv?

import csv
csvData = csv.reader(open(event.file))
		
csvData.next()
for data in csvData:		
	
		namedQuery = 'Add'
		params = {'data':data}
		system.db.runNamedQuery(namedQuery, params)
6

Diagnosing this would be much easier if you provided the errors you’re encountering while using this script, but after looking closer at open, I suspect that you actually should probably be trying

csvData = csv.reader(event.file.getBytes())

Again, without seeing the error this script is throwing I can only help so much. When using YOUR code locally, I see this error, which seems to confirm my suspicions about open:

Error running action 'component.onFileReceived' on Experimentation/TabulaRasa@C/root/FileUpload: Traceback (most recent call last): File "<function:runAction>", line 3, in runAction java.lang.ClassCastException: class org.python.core.PyObjectDerived cannot be cast to class org.python.core.PyString

It seems that csv.reader is just expecting the file, so use the available special method getBytes() to get the file itself and bypass the need to use open.

7

Or use the builtin StringIO, as in this thread dealing with the exact same issue:

8

Thanks for these examples, I will add one more that is derived from this thread in case it can help someone else who is searching.

Here I am uploading a CSV who’s first row is blank and the headers are on the second row (index 1). The data is from index 2 and onwards. The use case is importing a CSV to a dataset tag.

	from StringIO import StringIO
	import csv

	file = event.file.getString("UTF-8")
	data = [row for row in csv.reader(StringIO(file))]
	ds = system.dataset.toDataSet(data[1], data[2:])
	system.tag.write(self.view.params.area1TagPath, ds)

Cheers,

Nick

2 Likes