Load JSON file and convert to dataset

Ignition
1

I am trying to load a JSON file and convert it into a dataset. I provided a smaller portion of the json file. All I want to do is Load the file and show it in a datatable. I looked at a lot of documentation but could not find anything that works for this.
[
{
"CONFIGURABLEITEMID": 55,
"VALUE": "DIA",
"Location": "Waco",
"Environment": "Production",
"Schema": "UTWSHP"
},
{
"CONFIGURABLEITEMID": 56,
"VALUE": "C:\NetApp\OMS",
"Location": "Waco",
"Environment": "Production",
"Schema": "UTWSHP"
}
]

1 Like
2

One possible way to do it, assuming you're starting from a true JSON string:

content = """[
{
"CONFIGURABLEITEMID": 55,
"VALUE": "DIA",
"Location": "Waco",
"Environment": "Production",
"Schema": "UTWSHP"
},
{
"CONFIGURABLEITEMID": 56,
"VALUE": "C:\\\NetApp\\\OMS",
"Location": "Waco",
"Environment": "Production",
"Schema": "UTWSHP"
}
]"""

def jsonToDataset(content):
	import json
	raw = iter(json.loads(content))

	firstRow = next(raw)
	headers = sorted(firstRow.keys())
	data = [[firstRow[key] for key in headers]]
	for row in raw:
		data.append([row[key] for key in headers])

	return system.dataset.toDataSet(headers, data)
	
ds = jsonToDataset(content)

This makes certain assumptions (your JSON document will never be more deeply nested, the first object always has all the keys you care about, you don't care about the order of columns in the output), but it should be a solid starting point.

3 Likes
3

Paul,

Thank you.

When I run this in script console it works fine.

But when I have a window and add the same code to a button, it fails?

image001.png1239×593 54.6 KB

Button code:

def jsonToDataset(content):
import json
raw = iter(json.loads(content))

firstRow = next(raw)
headers = sorted(firstRow.keys())
data = [[firstRow[key] for key in headers]]
for row in raw:
data.append([row[key] for key in headers])

return system.dataset.toDataSet(headers, data)

content = system.file.readFileAsString("C:\UT Schemas\test.json")
print content
ds = jsonToDataset(content)
print ds
print ds.getColumnAsList(0)
print ds.getColumnNames()
#event.source.parent.getComponent('Table').data = ds

Button error:

image002.png979×518 72.4 KB

Marcel.

4

did you recently change files in user-lib? or pylib
ifso restart your gateway,

5

That did it. Thank you very much.
Now it is working correctly.

Here is the solution with the help from Paul and Victor.

I created a new window.
added a button and a table

Code I put in the button.
content = system.file.readFileAsString("C:\UT Schemas\test.json")

def jsonToDataset(content):
import json
raw = iter(json.loads(content))

firstRow = next(raw)
headers = sorted(firstRow.keys())
data = [[firstRow[key] for key in headers]]
for row in raw:
	data.append([row[key] for key in headers])

return system.dataset.toDataSet(headers, data)

ds = jsonToDataset(content)
print ds
print ds.getColumnAsList(0)
print ds.getColumnNames()
event.source.parent.getComponent('Table').data = ds

Screen shot.
image

2 Likes