Perspective onFileReceived - CSV

Ignition
1

Having issues trying to figure out how to parse a CSV and get it into a database from perspective using the file upload component. I have looked around most of the afternoon on the forum and can’t quite find any examples that are not people trying to parse images.

	sdata = event.file.getBytes()
	byteArray = bytearray(sdata).decode("UTF-8")
	system.perspective.print(byteArray)

I have the above and it will print out the data in the CSV file in a string list I am assuming with the decode. What I need to do though is get the data into ‘rows’ so I can insert each row from the csv to the database. I had this working in Vision with open.file (below) but can’t seem to figure out how to migrate this to perspective.

	filePath = system.file.openFile("csv", "%user%")
	csvFile = open(filePath, 'r')
	reader = csv.DictReader(csvFile)
		
	for row in reader:
	        entrytype = row['entrytype']
		freq= row['freq']
		taskName = row['taskname']
		priority = row['priority']
		system.db.runUpdateQuery("INSERT INTO maint_admin(taskname,freq,entrytype,priority) VALUES ('%s', '%s', '%s', %d)" % (taskName, freq, entrytype, int(priority)))
2 Likes
2

If you want to use Python’s built-in csv module, you need to construct a file-like object to pass to the reader. Luckily, Python also predicted the idea of interpreting a string in memory as a ‘file-like’ object, so the StringIO module already exists.
What you want is probably something like this:

from StringIO import StringIO

fileContents = event.file.getString("UTF-8")
reader = csv.DictReader(StringIO(fileContents))

for row in reader:
	print row
5 Likes
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!

8 Likes
4

Thanks DS1600 that is most helpful

1 Like
5

If using the parse to string method, what is the equivalent of this next function? I need the system to disregard the first row in the .csv if possible. (I would like to avoid having the user have to modify any file)

Thanks

image

6

Please use preformatted code </> instead of a picture of code.
What do you think the next function is doing here? Why not just ignore the first row?

from StringIO import StringIO
import csv
	
fileContents = event.file.getString("UTF-8")
reader = csv.DictReader(StringIO(fileContents))
	
for row in reader:
	if row >0:
		system.perspective.print(row)
7

Are you ignoring the first line of data, or are you ignoring it because it’s a header line? The csv module has different readers, so it helps to know what they do.

from StringIO import StringIO
import csv

csvString = """col1, col2, col3
abc, def, ghi
jkl, mno, pqr
stu, vwx, yz"""

reader = csv.DictReader(StringIO(csvString))


print 'Using csv.DictReader()'
for row in reader:
		print row

print '\nUsing csv.reader()'
reader2 = csv.reader(StringIO(csvString))
for row in reader2:
	print row

Output:

Using csv.DictReader()
{' col3': ' ghi', ' col2': ' def', 'col1': 'abc'}
{' col3': ' pqr', ' col2': ' mno', 'col1': 'jkl'}
{' col3': ' yz', ' col2': ' vwx', 'col1': 'stu'}

Using csv.reader()
['col1', ' col2', ' col3']
['abc', ' def', ' ghi']
['jkl', ' mno', ' pqr']
['stu', ' vwx', ' yz']
>>>
1 Like
8

I tried this code but am getting the following error:

Error running action 'component.onFileReceived' on MainPages/AccountingSettings@D/root/FlexContainer_3/FileUpload: Traceback (most recent call last): File "function:runAction", line 6, in runAction
java.lang.IllegalArgumentException: java.lang.IllegalArgumentException: Cannot create PyString with non-byte value

9

I’m getting this as well

10

if row >0:

row is not an integer so you can't use the > comparator.

  • In Jordan's first example it's a dictionary. e.g.,
    {' col3': ' ghi', ' col2': ' def', 'col1': 'abc'}
  • In the second example it's a list. e.g.,
    ['col1', ' col2', ' col3']

Use print type(row) to see for yourself.

11

Perhaps this would render the result you are looking for:

rowNum = 0
for row in reader:
	rowNum = rowNum + 1
	if rowNum > 1
		print row
12

Probably more pythonic to use enumeration, something like

for x, row in enumerate(reader):
2 Likes
13

If using the csv.DictReader() option. Might be better to do:

for row in reader:
    for name,val in row.iteritems():
          print name,':', val