JSON to Datatable convert

Help Studio
, ,
1

I have below Json data. I want to add CaseNumber value and Origin to datatable. Any help would be appericated. I am very new to Json so having hard time to extract data to Datatable.
Output like this

image

JSON

{
“columns”: [
{
“ascendingLabel”: “Z-A”,
“descendingLabel”: “A-Z”,
“fieldNameOrPath”: “CaseNumber”,
“hidden”: false,
“label”: “Case Number”,
“searchable”: true,
“selectListItem”: “CaseNumber”,
“sortDirection”: “ascending”,
“sortIndex”: 0,
“sortable”: true,
“type”: “string”

	}
],
"developerName": "HELiO_Support_Car_Vouchers",
"done": true,
"id": "00B3W0000019rgp",
"label": "HELiO Support - Car Vouchers",
"records": [
	{
		"columns": [
			{
				"fieldNameOrPath": "CaseNumber",
				"value": "01302452"
			},
			{
				"fieldNameOrPath": "Origin",
				"value": "Email (Helio Supplier)"
			}
		]
	},
	{
		"columns": [
			{
				"fieldNameOrPath": "CaseNumber",
				"value": "01318863"
			},
			{
				"fieldNameOrPath": "Origin",
				"value": "Email (Helio Supplier)"
			}
		]
	},
	{
		"columns": [
			{
				"fieldNameOrPath": "CaseNumber",
				"value": "01318981"
			},
			{
				"fieldNameOrPath": "Origin",
				"value": "Email (Helio Supplier)"
			}
		]
	},
	{
		"columns": [
			{
				"fieldNameOrPath": "CaseNumber",
				"value": "01318990"
			},
			{
				"fieldNameOrPath": "Origin",
				"value": "Email (Helio Supplier)"
			}
		]
	}
],
"size": 4

}

2

Assign a datatable to JsonConvert.DeserializeObject(of DataTable)(YourJArray.ToString)

3

Use Deserialized Json Activty.

or

JsonConvert.DeserializedObject(Of Datatable)(JsonString)

4

I tried it but I am getting this error

image
image1365×623 33.8 KB

5

{

“developerName”: “HELiO_Support_Car_Vouchers”,
“done”: true,
“id”: “00B3W0000019rgp”,
“label”: “HELiO Support - Car Vouchers”,
“records”: [
{
“columns”: [
{
“fieldNameOrPath”: “CaseNumber”,
“value”: “01302452”
},
{
“fieldNameOrPath”: “Origin”,
“value”: “Email (Helio Supplier)”
}
]
},
{
“columns”: [
{
“fieldNameOrPath”: “CaseNumber”,
“value”: “01318863”
},
{
“fieldNameOrPath”: “Origin”,
“value”: “Email (Helio Supplier)”
}
]
},
{
“columns”: [
{
“fieldNameOrPath”: “CaseNumber”,
“value”: “01318981”
},
{
“fieldNameOrPath”: “Origin”,
“value”: “Email (Helio Supplier)”
}
]
},
{
“columns”: [
{
“fieldNameOrPath”: “CaseNumber”,
“value”: “01318990”
},
{
“fieldNameOrPath”: “Origin”,
“value”: “Email (Helio Supplier)”
}
]
}
],
“size”: 4
}

Can you use this json

6

Tried but its not a valid JSON

7

BlankProcess.zip (7.7 KB)
Check if this helps

8

@Lak_Ui Thanks heaps for working on this. The issue is the the json file. The one used in demo is not as complex as the one I extracted from API call. I have attached the file for you to have a look and see if you can find a solution.

API_table.json (9.1 KB)

9

Hi @ravindar.singh ,

Check the Below Workflow :
Convert_JsonToDT.xaml (7.9 KB)

From the Json, we get to know that we have records Array and it has many columns sub arrays.

We first Convert the records to JArray and then Loop through each columns, convert it to JArray and pick our values as required.

Hope the xaml file covers the Explanation.

1 Like
10

@supermanPunch Thanks mate for your help. Can you please let me know what activity did you used here so I can download the correct dependencies.

image
image684×566 13.7 KB

11

@ravindar.singh , I have used Read Text File Activity at the point to read the Json String from a File.

Maybe because you have a Lower Version of System.Activities it may be not be able to detect it. Upgrading the System.Activities package or simply using Read Text File Activity and assigning the JsonString variable as it’s Output should solve this issue.

12

@supermanPunch Thanks you heaps mate you are a legend. Sorry to be pain but as you mentioned earlier that Loop through each columns and get the value.

I extracted 5 records with API call are you help me how to put all the in the DT. Thank you again.
new_API_table.json (12.1 KB)

13

@ravindar.singh , The Extracted Values are already available as a Datatable, OutputDT.

You could use Write Range Activity to Write the OutputDT to an Excel Sheet. Did not implement this in the xaml file provided.

If this is not what you are expecting, Could you explain in a bit more detail as to what you want to add to the DT ?

14

I edited this to add subject as well. I think i figured out how to get all columns if required.

Thanks heaps for your help :slight_smile:

image
image850×302 12 KB

1 Like
15

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.