Script for Dataset build

Ignition
,
1

I am trying to send data from a web service call to a dataset tag. It has multiple rows with 4 columns.

This is the returned data from my WS call. Just not great at parsing the data with Python. I would also like some good learning resources on more advance scripting for these types of problems.

{u'Name': u'Column_A', u'Value': u'4001'}
{u'Name': u'Column_B', u'Value': 8014844}
{u'Name': u'Column_C', u'Value': u'4001--1'}
{u'Name': u'Column_D', u'Value': u'12'}
{u'Name': u'Column_A', u'Value': u'570'}
{u'Name': u'Column_B', u'Value': 4640301}
{u'Name': u'Column_C', u'Value': u'570--001'}
{u'Name': u'Column_D', u'Value': u'12'}
{u'Name': u'Column_A', u'Value': u'4568'}
{u'Name': u'Column_B', u'Value': 4640362}
{u'Name': u'Column_C', u'Value': u'4568--002'}
{u'Name': u'Column_D', u'Value': u'23'}
{u'Name': u'Column_A', u'Value': u'570-000066-000'}
{u'Name': u'Column_B', u'Value': 4150345}
{u'Name': u'Column_C', u'Value': u'570-000066-000'}
{u'Name': u'Column_D', u'Value': u'44'}
{u'Name': u'Column_A', u'Value': u'g581-123'}
{u'Name': u'Column_B', u'Value': 4152115}
{u'Name': u'Column_C', u'Value': u'g581-123--001'}
{u'Name': u'Column_D', u'Value': u'508'}

2

Looks like you have a dict type, though curly braces can also mean a set. I generally find W3schools.com to have good examples. Of course, the forum is also an excellent source of examples and help if you need it.
Python Dictionaries

3

Yea I have been fighting this formatting from my WS source, keeps throwing me for a loop with the braces.

4

Are all of the dictionaries returned in a list?

5

This is the raw form. With 1000 results of the 4 columns.

{"Root": {"ExecuteDataSourceResponse": {
"@": {"xmlns": ""},
"ExecuteDataSourceResult": {
"DataSourceKey": xxxxx,
"DataSourceName": "xxxxxx",
"Error": true,
"ErrorNo": 770,
"InstanceNo": 10464,
"LastPrimaryDeployment": "NULL_VALUE",
"LastTestDeployment": "NULL_VALUE",
"Message": "There were more rows found than the allowed maximum of 1000",
"QuarantinedForDevelopment": "NULL_VALUE",
"ResultSets": {"ResultSet": [{
"RowCount": 1000,
"Rows": {"Row": [
{"Columns": {"Column": [
{
"Name": "Customer_Part_No",
"Value": "123456"
},
{
"Name": "Customer_Part_Key",
"Value": 11111
},
{
"Name": "Customer_Part_No_Revision",
"Value": "123456"
},
{
"Name": "Customer_Code",
"Value": "X"
}
]}},

6

This is the code I was using to parse the data down, which works fine for a single return but not for multiples.

headers = []
data = []
for i in data_dict:
	for j in i['Rows']['Row']:
		rowData = []
		for item in  j['Columns']['Column']:
			print item
			if not item['Name'] in headers:
				headers.append(item['Name'])
			rowData.append(item['Value'])
		data.append(rowData)
print data
ds2 = system.dataset.toDataSet(headers,data)
print ds2
7

What is the difference between a single return and multiple returns?

8

The script will move the first set of information from the first group in .

9

I am by no stretch of the imagination an expert, but i like to play around with this kind of thing in the scrip console. Here is my basic setup of a list of dict’s that i convert to a dataset:

myList = []
myDict = {u'Name': u'Column_A', u'Value': u'4001'}
headers = ["key", "value"]
myDS = system.dataset.toDataSet(headers,[])

#Fill list with dict(s)
for i  in range(1, 5):
	myList.append(myDict)

#Append dict data to myDS
for x in range(len(myList)):
	row = []
	row.append(myList[x]['Name'])
	row.append(myList[x]['Value'])
	myDS = system.dataset.addRow(myDS, row)

print myDS
10

Sorry, I’m struggling to understand what the structure will look like with multiple returns, and why that is different from what you showed earlier.

@josborn I think the OP is wanting a WIDE table output.

11

Oh, not sure then, im no good with wide tables =x

12

Sorry I am not great with dicts.

I am pulling the same data but with my script I currently have I can only move the first part of the return.

The raw data return from the call looks like

[
{“Columns”: {“Column”: [
{
“Name”: “Customer_Part_No”,
“Value”: “123456”
},
{
“Name”: “Customer_Part_Key”,
“Value”: 11111
},
{
“Name”: “Customer_Part_No_Revision”,
“Value”: “123456”
},
{
“Name”: “Customer_Code”,
“Value”: “X”
}
]}},
[
{“Columns”: {“Column”: [
{
“Name”: “Customer_Part_No”,
“Value”: “222222”
},
{
“Name”: “Customer_Part_Key”,
“Value”: 88888
},
{
“Name”: “Customer_Part_No_Revision”,
“Value”: “25252”
},
{
“Name”: “Customer_Code”,
“Value”: “y”
}
]}},
[
{“Columns”: {“Column”: [
{
“Name”: “Customer_Part_No”,
“Value”: “654321”
},
{
“Name”: “Customer_Part_Key”,
“Value”: 58964
},
{
“Name”: “Customer_Part_No_Revision”,
“Value”: “654321”
},
{
“Name”: “Customer_Code”,
“Value”: “q”
}
]}}, ECT

So with the script I have I just get a dataset with one row that has the first set of data

Like so

Customer_Part_No
row 0 = 123456
Customer_Part_Key
row 0 = 58964
Customer_Part_No_Revision
row 0 = 654321
Customer_Code
row 0 = x

What I am looking to get is

Customer_Part_No
row 0 = 123456
row 1 = 222222
row 2 = 654321
Customer_Part_Key
row 0 = 58964
row 1 = 88888
row 2 = 58964
Customer_Part_No_Revision
row 0 = 654321
row 1 = 25252
row 2 = 654321
Customer_Code
row 0 = x
row 1 = y
row 2 = q

13

This is a truly awful JSON representation. Where is this data coming from?

I would do something like this, making the assumption that the column names in the first row will be the same throughout the rest of the data coming in:

resultsets = data["Root"]["ExecuteDataSourceResponse"]["ExecuteDataSourceResult"]["ResultSets"]["ResultSet"]
resultset = resultsets[0]

headers = []
data = []

rows = resultset["Rows"]["Row"]
# peek at the first row to get column names
row = rows[0]
columns = row["Columns"]["Column"]
for column in columns:
	headers.append(column["Name"])
# now go through each row object
for row in rows:
	columns = row["Columns"]["Column"]
	rowData = []
	for column in columns:
		rowData.append(column["Value"])
	data.append(rowData)
		
print headers
print data
2 Likes
14

That's what I was thinking. You beat me to the punch with the code.

1 Like
15

This is from our PLEX ERP system.

16

From the verbosity and general structure, I’m guessing someone did a fairly low effort transliteration of XML to JSON, in order to pretend check a box somewhere saying they issue JSON as a response. Not really a problem, just an annoyance; this JSON could be structured much more densely without losing any information.

17

I tried to push the data to a dataset tag with

ds = system.dataset.toDataSet(headers,data)		
system.tag.write("dataset_tag", ds)

and got the following error

Traceback (most recent call last):
File “”, line 150, in
TypeError: Unable to convert row 102, column 0 to type class java.lang.String

18

The header a data.looked good though, it looked like so

[u'Customer_Part_No', u'Customer_Part_Key', u'Customer_Part_No_Revision', u'Customer_Code']
[123456, 58964, 654321,x], [222222,88888,25252,y]

19

You'll have to look at what's different about [102, 0] in your actual response. system.dataset.toDataSet does automatic type casting based on the values in the first row, and chose to be a string (which would also allow nulls). But it looks like row 102 has some other type of value in it.

20

first row data

[u’400-819502R01’, 8014844, u’400-819502R01’, u’Insitu’]

row 102
[5808989, 4150217, 5808989, u’NSWC’]