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