Upload csv to dataset

Travis_Opperud · September 28, 2023, 3:15pm

I want have a database that is tracking equipment. Occasionally, when we are getting hundreds of new pieces of equipment at once, I want users to be able to upload batches at a time, by taking the csv file provided from the vendor and uploading to the perspective interface.

Testing environment: I have an "upload file" element on an xy container, and I am dragging a csv onto it. This is my "onFileReceived" script:

system.perspective.print('file received')
# save file in tmp
filepath = system.file.getTempFile('csv')
system.file.writeFile(filepath, event.file.getString())
system.perspective.print('file saved temporarily')
# load file as string
file_string = system.file.readFileAsString(filepath)
# print to confirm
system.perspective.print(file_string)
# load into dataset
data = system.dataset.fromCSV(file_string)
# print to confirm
system.perspective.print(data)

I have also tried to not use the temporary file as an intermediate, as I have no desire to save the file long term.

It seems that the issue is coming from the requirement to have the wonky #NAMES #TYPES #ROWS format. Is there a way to save a file in this format in excel?

Is what I am trying to do even possible?

Travis_Opperud · September 28, 2023, 4:33pm

Figured it out, Not sure why I got hung up like I did. If anyone else gets stuck: the following class can parse csv files and store as a dataset:

class CSVParser:
	
	def __init__(self, file_in):
		self.file = file_in
		self.file_string = file_in.getString()
		self.rows = [row.split(',') for row in self.file_string.split('\r')]
		self.num_cols = max([len(row) for row in self.rows])
		self.num_rows = len(self.rows)
		self.headers = self.rows[0]
		self.data = self.rows[1:]
	
	@property
	def extended_headers(self):
		headers_output = self.headers
		ii = 1
		while len(headers_output) < self.num_cols:
			headers_output.append('Placeholder{}'.format(ii))
			ii += 1
		return headers_output
	
	def extend_row(self, row):
		row_output = row
		while len(row_output) < self.num_cols:
			row_output.append(None)
		return row_output
	
	@property
	def dataset(self):
		headers = self.extended_headers
		data = [self.extend_row(row) for row in self.data]
		return system.dataset.toDataSet(headers, data)

On fileUpload script:

system.perspective.print('file received')
parser = parsers.CSVParser(event.file)
	
self.getSibling("Table").props.data = parser.dataset

jacque_trahan · April 16, 2024, 5:57pm

I am trying to do the same but only make a one column dataset. I was able to make a one row dataset set just fine, but for some reason Ignition give me a headers and column number are different error. Even through both the len() of my data set and headers are 1.

Here is the code with me trying to figure out how the headers and ata are different:

def runAction(self, event):
	"""
	Grabs one column from a CSV file and makes a dataset of that column and saves it to a tag.
	Logger name = getTagAddDatasetAsColumn
	"""
	import csv
	# variable
	myLogger = system.util.getLogger("getTagAddDatasetAsColumn")
	# Get our CSV path
	filePath = self.getSibling("CSVFilePath").props.text.replace('\\','\\\\')
	path = filePath.strip('"')
	
	# build a column list to make a one column dataSet
	datasetList = []
	data = 
	# open the CSV file
	with open(path, 'rU') as csvFile:
		fileObject = csv.DictReader(csvFile,delimiter=',')
		headerNameCounts = fileObject.fieldnames
#		self.custom.key = headerNameCounts
		headerName = str(fileObject.fieldnames[0])
#		self.custom.key = headerName
		if len(headerNameCounts) == 1:
			for row in fileObject:
#				self.custom.key = row[headerName]
				datasetList.append(row[headerName])
			data = [datasetList] # must be a list of rows
			headers = [headerName] # needs to be a list of strings
#			buildDataset = system.dataset.toDataSet(headers, data)
			
			
			system.perspective.print("Len of our headers: {}".format(len(headers)))
			system.perspective.print("headers output: {}".format(headers))
			system.perspective.print("Len of our dataSet {}:".format(len(data)))
			system.perspective.print("Data output: {}".format(data))
#			system.perspective.print(buildDataset)
			

				
		else:
			myLogger.info("You have to many columns for this function call. Only one column is needed.")

Here is the out put. Its just UDT parameters' in a one column CSV file:

12:55:04.551 [Browser Thread: d20c45f7-fdf4-46ba-97a4-f6b4956f33ea] INFO Perspective.Designer.Workspace - Len of our headers: 1
12:55:04.551 [Browser Thread: d20c45f7-fdf4-46ba-97a4-f6b4956f33ea] INFO Perspective.Designer.Workspace - headers output: ['UDT Parameters']
12:55:04.551 [Browser Thread: d20c45f7-fdf4-46ba-97a4-f6b4956f33ea] INFO Perspective.Designer.Workspace - Len of our dataSet 1:
12:55:04.551 [Browser Thread: d20c45f7-fdf4-46ba-97a4-f6b4956f33ea] INFO Perspective.Designer.Workspace - Data output: [['AlarmRollupTag', 'AssociatedAnalyzer', 'AssociatedL3', 'Description', 'DeviceAddress', 'Disposition', 'DOT', 'EquipmentID', 'EquipSubType', 'EquipType', 'Facility', 'FacilityID', 'FilterID_01', 'FilterID_02', 'FilterID_03', 'ForemanArea', 'Latitude', 'Longitude', 'MDMKey', 'MeasurementArea', 'MeasurementType', 'MeterID', 'OrderAndType', 'PressureType', 'Producer', 'Route', 'RTU_Instance', 'Safety', 'SAP_ParentEquipment', 'SAPID', 'Service', 'Site', 'SpanOfControl', 'State', 'Topic', 'TotalInterlocks']]

I have no clue why this is giving me this error once I uncomment the toDataset part.
error:

com.inductiveautomation.ignition.common.script.JythonExecException
Traceback (most recent call last):
  File "<function:runAction>", line 29, in runAction
IndexError: Row 0 doesn't have the same number of columns as header list.

	at org.python.core.PyException.doRaise(PyException.java:211)
	at org.python.core.Py.makeException(Py.java:1654)
	at org.python.core.Py.makeException(Py.java:1658)
	at org.python.core.Py.makeException(Py.java:1662)
	at org.python.core.Py.makeException(Py.java:1666)
	at org.python.pycode._pyx3318.runAction$1(<function:runAction>:41)
	at org.python.pycode._pyx3318.call_function(<function:runAction>)
	at org.python.core.PyTableCode.call(PyTableCode.java:173)
	at org.python.core.PyBaseCode.call(PyBaseCode.java:306)
	at org.python.core.PyFunction.function___call__(PyFunction.java:474)
	at org.python.core.PyFunction.__call__(PyFunction.java:469)
	at org.python.core.PyFunction.__call__(PyFunction.java:464)
	at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:847)
	at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:829)
	at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runFunction(ProjectScriptLifecycle.java:868)
	at com.inductiveautomation.ignition.common.script.ScriptManager$ScriptFunctionImpl.invoke(ScriptManager.java:1010)
	at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$AutoRecompilingScriptFunction.invoke(ProjectScriptLifecycle.java:950)
	at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:161)
	at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:98)
	at com.inductiveautomation.perspective.gateway.action.ScriptAction.runAction(ScriptAction.java:80)
	at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.lambda$call$0(ActionCollection.java:263)
	at com.inductiveautomation.perspective.gateway.api.LoggingContext.mdc(LoggingContext.java:54)
	at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:252)
	at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:221)
	at com.inductiveautomation.perspective.gateway.threading.BlockingTaskQueue$TaskWrapper.run(BlockingTaskQueue.java:154)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at com.inductiveautomation.perspective.gateway.threading.BlockingWork$BlockingWorkRunnable.run(BlockingWork.java:58)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.python.core.PyException
Traceback (most recent call last):
  File "<function:runAction>", line 29, in runAction
IndexError: Row 0 doesn't have the same number of columns as header list.

	... 31 more

Ignition v8.1.36 (b2024010211)
Java: Azul Systems, Inc. 17.0.8

PGriffith · April 16, 2024, 6:11pm

system.dataset.toDataSet takes two arguments:

A flat list of column names.
A list of lists, where each inner list must have the same number of elements as the number of column names.

So you're trying to define a single column, but trying to add many column values. You need to 'pivot' your list into a sequence of single element lists.

jacque_trahan · April 16, 2024, 6:37pm

Got IT! thanks so much. I was so focused on the row amount i was not even thinking about that.

Final Code( i made components for the CSV path and tagPathToWrite) change as needed:

def getTagAddDatasetAsColumn(self, event, tagPathToWrite)
	""" 
	Extracts One Column from a CSV File and Converts it to a Dataset, then Saves it to a Tag.
	
	Description:
	- This function reads a CSV file and extracts a single column from it.
	- It then creates a dataset from the extracted column and writes it to a specified tag path.
	
	Logger Name: 
	- getTagAddDatasetAsColumn
	
	Args:
	- event: Event object.
	- tagPathToWrite (str): The path of the tag where the dataset will be written.
	
	Returns:
	- None
	
	Details:
	- The function reads the CSV file specified by the user, extracts the first column, and converts it into a dataset.
	- It then attempts to write this dataset to the specified tag path.
	- Log messages are generated to indicate the success or failure of the operation.
	
	"""
	import csv
	# variable
	myLogger = system.util.getLogger("getTagAddDatasetAsColumn")
	myLogger.info("Process Started: Add one column dataset to a tag")
	# Get our CSV path
	filePath = self.getSibling("CSVFilePath").props.text.replace('\\','\\\\')
	path = filePath.strip('"')
	# build a column list to make a one column dataSet
	datasetList = []
	# open the CSV file
	with open(path, 'rU') as csvFile:
		fileObject = csv.DictReader(csvFile,delimiter=',')
		headerNameCounts = fileObject.fieldnames
		headerName = str(fileObject.fieldnames[0])
		if len(headerNameCounts) == 1:
			for row in fileObject:
				datasetList.append([row[headerName]]) # make each row its own 'column row' [['col row 1], [col row 2'], [etc]]
			headers = [headerName] # needs to be a list of strings
			# build data set
			buildDataset = system.dataset.toDataSet(headers, datasetList)
			# write to tag
			try:
				system.tag.writeBlocking(tagPathToWrite, buildDataset)
				myLogger.info("Put one column data set in the tag path: {}".format(tagPathToWrite))
			except:
				myLogger.error("Error writing to tag path: {}".format(tagPathToWrite))
		else:
			myLogger.info("You have to many columns for this function call. Only one column is needed.")

	myLogger.info("Process Finished: Add one column dataset to a tag")