Perspective onFileReceived - CSV

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

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

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!

7 Likes

Thanks DS1600 that is most helpful

1 Like

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

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)

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

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

I’m getting this as well

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.

Perhaps this would render the result you are looking for:

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

Probably more pythonic to use enumeration, something like

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

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

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