# Script for Dataset build

**URL:** <https://forum.inductiveautomation.com/t/script-for-dataset-build/59319>\
**Category:** Ignition\
**Tags:** ignition79, scripting\
**Created:** [May 6, 2022, 7:35pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319 "2022-05-06T19:35:19Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![boster281](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@boster281](https://forum.inductiveautomation.com/u/boster281)\
**Post date:** [May 6, 2022, 7:35pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/1 "2022-05-06T19:35:19Z")

</div>

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'}

---

<div class="post-metadata">

**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\
**Post date:** [May 6, 2022, 7:42pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/2 "2022-05-06T19:42:29Z")

</div>

Looks like you have a dict type, though curly braces can also mean a set. I generally find [W3schools.com](http://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](https://www.w3schools.com/python/python_dictionaries.asp)

---

<div class="post-metadata">

**Author:** ![boster281](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@boster281](https://forum.inductiveautomation.com/u/boster281)\
**Post date:** [May 6, 2022, 7:43pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/3 "2022-05-06T19:43:56Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 6, 2022, 7:53pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/4 "2022-05-06T19:53:47Z")

</div>

Are all of the dictionaries returned in a list?

---

<div class="post-metadata">

**Author:** ![boster281](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@boster281](https://forum.inductiveautomation.com/u/boster281)\
**Post date:** [May 6, 2022, 8:10pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/5 "2022-05-06T20:10:15Z")

</div>

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"  
> }  
> ]}},

---

<div class="post-metadata">

**Author:** ![boster281](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@boster281](https://forum.inductiveautomation.com/u/boster281)\
**Post date:** [May 6, 2022, 8:15pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/6 "2022-05-06T20:15:45Z")

</div>

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

```auto
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

```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 6, 2022, 8:40pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/7 "2022-05-06T20:40:07Z")

</div>

> [@boster281](#):
>
> single return but not for multiples.

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

---

<div class="post-metadata">

**Author:** ![boster281](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@boster281](https://forum.inductiveautomation.com/u/boster281)\
**Post date:** [May 6, 2022, 8:50pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/8 "2022-05-06T20:50:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\
**Post date:** [May 6, 2022, 8:52pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/9 "2022-05-06T20:52:09Z")

</div>

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:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 6, 2022, 9:00pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/10 "2022-05-06T21:00:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\
**Post date:** [May 6, 2022, 9:01pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/11 "2022-05-06T21:01:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![boster281](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@boster281](https://forum.inductiveautomation.com/u/boster281)\
**Post date:** [May 6, 2022, 9:12pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/12 "2022-05-06T21:12:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 6, 2022, 9:26pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/13 "2022-05-06T21:26:15Z")

</div>

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:

```python
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

```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 6, 2022, 9:28pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/14 "2022-05-06T21:28:55Z")

</div>

> [@paul-griffith](#):
>
> This is a truly awful JSON representation

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

---

<div class="post-metadata">

**Author:** ![boster281](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@boster281](https://forum.inductiveautomation.com/u/boster281)\
**Post date:** [May 6, 2022, 9:30pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/15 "2022-05-06T21:30:23Z")

</div>

This is from our PLEX ERP system.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 6, 2022, 9:36pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/16 "2022-05-06T21:36:51Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![boster281](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@boster281](https://forum.inductiveautomation.com/u/boster281)\
**Post date:** [May 6, 2022, 9:38pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/17 "2022-05-06T21:38:01Z")

</div>

I tried to push the data to a dataset tag with

```auto
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

---

<div class="post-metadata">

**Author:** ![boster281](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@boster281](https://forum.inductiveautomation.com/u/boster281)\
**Post date:** [May 6, 2022, 9:42pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/18 "2022-05-06T21:42:25Z")

</div>

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]

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 6, 2022, 9:53pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/19 "2022-05-06T21:53:29Z")

</div>

> [@boster281](#):
>
> TypeError: Unable to convert row 102, column 0 to type class java.lang.String

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.

---

<div class="post-metadata">

**Author:** ![boster281](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@boster281](https://forum.inductiveautomation.com/u/boster281)\
**Post date:** [May 6, 2022, 10:05pm UTC](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319/20 "2022-05-06T22:05:40Z")

</div>

first row data

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

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

[Next page](https://forum.inductiveautomation.com/t/script-for-dataset-build/59319.md?page=2)