# File Upload in Perspective CSV to database

**URL:** <https://forum.inductiveautomation.com/t/file-upload-in-perspective-csv-to-database/42813>\
**Category:** Ignition\
**Tags:** ignition80\
**Created:** [February 1, 2021, 1:27pm UTC](https://forum.inductiveautomation.com/t/file-upload-in-perspective-csv-to-database/42813 "2021-02-01T13:27:10Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![jgonya](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jgonya/32/9304_2.png) [@jgonya](https://forum.inductiveautomation.com/u/jgonya)\
**Post date:** [February 1, 2021, 1:27pm UTC](https://forum.inductiveautomation.com/t/file-upload-in-perspective-csv-to-database/42813/1 "2021-02-01T13:27:10Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/6/36ecc19edcf57078a1c7d6839151ac9ee8aec26f.png)

Any help would be greatly appreciated.

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [February 1, 2021, 4:42pm UTC](https://forum.inductiveautomation.com/t/file-upload-in-perspective-csv-to-database/42813/2 "2021-02-01T16:42:34Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![jgonya](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jgonya/32/9304_2.png) [@jgonya](https://forum.inductiveautomation.com/u/jgonya)\
**Post date:** [February 8, 2021, 1:12pm UTC](https://forum.inductiveautomation.com/t/file-upload-in-perspective-csv-to-database/42813/3 "2021-02-08T13:12:17Z")

</div>

> [@cmallonee](#):
>
> import csv csvData = csv.reader(open(event.file))

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)

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 8, 2021, 1:37pm UTC](https://forum.inductiveautomation.com/t/file-upload-in-perspective-csv-to-database/42813/4 "2021-02-08T13:37:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jgonya](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jgonya/32/9304_2.png) [@jgonya](https://forum.inductiveautomation.com/u/jgonya)\
**Post date:** [February 8, 2021, 3:18pm UTC](https://forum.inductiveautomation.com/t/file-upload-in-perspective-csv-to-database/42813/5 "2021-02-08T15:18:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [February 8, 2021, 5:12pm UTC](https://forum.inductiveautomation.com/t/file-upload-in-perspective-csv-to-database/42813/6 "2021-02-08T17:12:57Z")

</div>

Diagnosing this would be much easier if you provided the errors you’re encountering while using this script, but after looking closer at [open](https://cpython-test-docs.readthedocs.io/en/latest/library/functions.html#open), I suspect that you actually should probably be trying

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

```auto
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](https://cpython-test-docs.readthedocs.io/en/latest/library/csv.html) is just expecting the file, so use the available special method [getBytes()](https://docs.inductiveautomation.com/display/DOC81/Perspective+-+File+Upload) to get the file itself and bypass the need to use `open`.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [February 8, 2021, 6:00pm UTC](https://forum.inductiveautomation.com/t/file-upload-in-perspective-csv-to-database/42813/7 "2021-02-08T18:00:12Z")

</div>

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

> [@Perspective onFileReceived - CSV](http://forum.inductiveautomation.com/t/perspective-onfilereceived-csv/39893/3):
>
> Awesome - Felt I was close just couldn’t find what I needed. Should have asked sooner slight_smile FYI for future readers below was the updated script to make it work from perspective from StringIO import StringIO import csv fileContents = event.file.getString("UTF-8") reader = csv.DictReader(StringIO(fileContents)) for row in reader: system.perspective.print(row) Thanks again!

---

<div class="post-metadata">

**Author:** ![nicholas.robinson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nicholas.robinson/32/22188_2.png) [@nicholas.robinson](https://forum.inductiveautomation.com/u/nicholas.robinson)\
**Post date:** [April 14, 2022, 12:44pm UTC](https://forum.inductiveautomation.com/t/file-upload-in-perspective-csv-to-database/42813/8 "2022-04-14T12:44:33Z")

</div>

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.

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