Format data in dataset from tag history query

[michael.black]

I am running ignition edge and once a day i am creating a csv file with floating point values coming from the tag historian. I have the tag history set to be one floating point value but when my csv file is created it has more than one. I want the values in the csv file to be one decimal point. Best way to do it?

Here is a screenshot of the csv file and also the script I have to create the file:

def create_analogin_csv():
	"""Create analog input csv file with values from past 24 hours
	
	Params:
		none
	
	Returns:
		none
	"""

	#establish logger
	logger = system.util.getLogger("AI CSV file")

	#dynamically create list of analog input tag paths including filter to only look at UDT instance tags
	path = '[PLC]AnalogInputs'
	fltr = {'tagType': "UdtInstance"}
	tags = system.tag.browse(path,fltr)
	dataTagPaths = [str(tag['fullPath']) + "/InputValue" for tag in tags]

	#create custom header row
	headers = []
	headerTagPaths = [str(tag['fullPath']) + "/DeviceName" for tag in tags]
	  #reads in device names and creates array for header.  Inserts DATE/TIME header in first index position
	headers = ['DATE/TIME'] + [str(header.value) for header in system.tag.readBlocking(headerTagPaths)]

	#execute tag history query returning hourly values for last 24 hours
	AIdataset = system.tag.queryTagHistory(paths=dataTagPaths, returnSize=24,
		aggregationMode="LastValue", returnFormat='Wide', columnNames=headers, rangeHours = -24)

	#write data to dataset tag
	AIdatasetTag = '[PLC]AnalogInputCSV'
	system.tag.writeBlocking(AIdatasetTag, AIdataset)
	logger.info("Data written to dataset tag")

	#take resulting dataset and format time and date
	AIdatasetF = system.dataset.formatDates(AIdataset, "MM-dd-yyyy HH:mm")
	
	#convert dataset to csv file
	csv = system.dataset.toCSV(dataset = AIdatasetF, showHeaders = True)

	#define file path and populate file
	filePath = "C:\\DailyCSVFiles\\analogin.csv"
	system.file.writeFile(filePath, csv)

[paul-griffith]

The answer is basically the same as your other question about HTML formatting of a dataset:

CSV file from tag historian showing too many decimal points

That long string of digits is Java attempting to be polite and give you an as-exact-as-possible representation of the value as it can. If you want to provide explicit formatting, you must do that yourself (by looping over the dataset and changing each float value to a string using your own formatting operation. system.dataset.toHTML doesn't offer you control over this. You could do this with system.dataset.addColumn and system.dataset.filterColumn (to remove the now duplicate value columns). O…

[michael.black]

I did use the round() function and it worked great for creating the html part and rounding the values to one decimal place. For the csv file, would the easiest way be to do embedded FOR loops to go through the rows and columns and alter the data in the [column][row] format and use the round() function to then create a new dataset and use that in the system.dataset.toCSV function?

[paul-griffith]

That's one way to do it.
CSV is, by nature, an extremely simple format; it might be easiest to just write out your CSV yourself, though:

	#convert dataset to csv file
	outputRows = [headers]
	for row in xrange(AIdatasetF.rowCount):
		# add the date value
		outputRow = [AIdatasetF.getValueAt(row, 0)]
		# skip the date column
		for col in xrange(1, AIdatasetF.columnCount):
			outputRow.append(round(AIdatasetF.getValueAt(row, col)))
		outputRows.append(outputRow)

	csv = "\n".join(
		",".join(row for row in outputRows)
	)

[michael.black]

Looks good. I tried that code and I came back with an error on the 'csv =' line. Its looking for a string on the list. I tried putting str() in a few places and I can't get it to produce the csv file correctly. Where would I convert the data to strings so the csv line works?

Here is the error I am getting in the logs...

[michael.black]

Or is the headers row, the first row of outputRows, causing the problem because those aren't strings?

[michael.black]

Thanks! I was able to get it to work with your code as a base and some tweaking.