Convert Dynamic JsonArray into Datatable

Hey, I am trying to retrieve a dynamic json array response from any api url.
Now, I want to convert the json array into a datatable to write it into a excel sheet.

eg json array ;
{“status”:200,“result”:[{“postcode”:“CM8 1EF”,“quality”:1,“eastings”:581459,“northings”:213679,“country”:“England”,“nhs_ha”:“East of England”,“longitude”:0.629806,“latitude”:51.792326,“european_electoral_region”:“Eastern”,“primary_care_trust”:“Mid Essex”,“region”:“East of England”,“lsoa”:“Braintree 017F”,“msoa”:“Braintree 017”,“incode”:“1EF”,“outcode”:“CM8”,“distance”:1.98709706,“parliamentary_constituency”:“Witham”,“admin_district”:“Braintree”,“parish”:“Witham”,“admin_county”:“Essex”,“admin_ward”:“Witham South”,“ced”:“Witham Southern”,“ccg”:“NHS Mid Essex”,“nuts”:“Essex Haven Gateway”,“codes”:{“admin_district”:“E07000067”,“admin_county”:“E10000012”,“admin_ward”:“E05010388”,“parish”:“E04003939”,“parliamentary_constituency”:“E14001045”,“ccg”:“E38000106”,“ced”:“E58000470”,“nuts”:“UKH34”}},{“postcode”:“CM8 1EU”,“quality”:1,“eastings”:581508,“northings”:213652,“country”:“England”,“nhs_ha”:“East of England”,“longitude”:0.630501,“latitude”:51.792068,“european_electoral_region”:“Eastern”,“primary_care_trust”:“Mid Essex”,“region”:“East of England”,“lsoa”:“Braintree 017F”,“msoa”:“Braintree 017”,“incode”:“1EU”,“outcode”:“CM8”,“distance”:54.12009472,“parliamentary_constituency”:“Witham”,“admin_district”:“Braintree”,“parish”:“Witham”,“admin_county”:“Essex”,“admin_ward”:“Witham South”,“ced”:“Witham Southern”,“ccg”:“NHS Mid Essex”,“nuts”:“Essex Haven Gateway”,“codes”:{“admin_district”:“E07000067”,“admin_county”:“E10000012”,“admin_ward”:“E05010388”,“parish”:“E04003939”,“parliamentary_constituency”:“E14001045”,“ccg”:“E38000106”,“ced”:“E58000470”,“nuts”:“UKH34”}},{“postcode”:“CM8 1PH”,“quality”:1,“eastings”:581421,“northings”:213740,“country”:“England”,“nhs_ha”:“East of England”,“longitude”:0.629287,“latitude”:51.792887,“european_electoral_region”:“Eastern”,“primary_care_trust”:“Mid Essex”,“region”:“East of England”,“lsoa”:“Braintree 017H”,“msoa”:“Braintree 017”,“incode”:“1PH”,“outcode”:“CM8”,“distance”:73.09110168,“parliamentary_constituency”:“Witham”,“admin_district”:“Braintree”,“parish”:“Witham”,“admin_county”:“Essex”,“admin_ward”:“Witham Central”,“ced”:“Witham Southern”,“ccg”:“NHS Mid Essex”,“nuts”:“Essex Haven Gateway”,“codes”:{“admin_district”:“E07000067”,“admin_county”:“E10000012”,“admin_ward”:“E05010386”,“parish”:“E04003939”,“parliamentary_constituency”:“E14001045”,“ccg”:“E38000106”,“ced”:“E58000470”,“nuts”:“UKH34”}},{“postcode”:“CM8 1PQ”,“quality”:1,“eastings”:581399,“northings”:213755,“country”:“England”,“nhs_ha”:“East of England”,“longitude”:0.628977,“latitude”:51.793028,“european_electoral_region”:“Eastern”,“primary_care_trust”:“Mid Essex”,“region”:“East of England”,“lsoa”:“Braintree 017H”,“msoa”:“Braintree 017”,“incode”:“1PQ”,“outcode”:“CM8”,“distance”:98.10933201,“parliamentary_constituency”:“Witham”,“admin_district”:“Braintree”,“parish”:“Witham”,“admin_county”:“Essex”,“admin_ward”:“Witham Central”,“ced”:“Witham Southern”,“ccg”:“NHS Mid Essex”,“nuts”:“Essex Haven Gateway”,“codes”:{“admin_district”:“E07000067”,“admin_county”:“E10000012”,“admin_ward”:“E05010386”,“parish”:“E04003939”,“parliamentary_constituency”:“E14001045”,“ccg”:“E38000106”,“ced”:“E58000470”,“nuts”:“UKH34”}}]}

Can you please tell me on how I can do this?

1 Like

use Deserialize JSON activity.

1 Like

You need to install a package named UiPath.web.activity

Hi @cursed_katana,

You can use Deserialize JSON activities to parse the json you got.
Using the existing activities will reduce your effort as well. all you need is to import the package

Here is the example

yes, I have deserialized the json array. how do I convert the json array ( I wouldnt know the keys in the array as its a dynamic array) into a datatable

I have attached one example.

Yes, but it talks about json and not json array. I want to convert a jsonarray into datatable. I have already deserialized the jsonarray

Hi Hareesh

I have converted the jsonstring into a json array and stored it in a jarray. now how do I convert that jarray into a datatable?

Loop through the array based on the length and column names. read the row wise data of the array. You will have the build data table activity to make the data you have as a data table. Store the data you read row wise into the data table.

Read the column data and write the same into the column in build data table.
Hope the answer is confusing :slight_smile: but you can get it easily

1 Like

Sorry, But I dont have much knowledge about json and its objects. So my questions based on your suggestion is

  1. what should be the ‘typeargument’ in the for each loop for json array.
  2. how can I assign the object to a datatable?

Hi,

Refer this post, similar issue with solution

Hey Sarathi,
Thanks for the suggestion.
SO, here in the example given, he was able to go thru the arary objects cos he know the key names. but I want to create a process which would work with any kind of proper jsonarray. in that case I wont be able to know the name of the keys right.

If you are more comfortable using Excel - you can have excel deserialise the json array file into the table format you need, you just need to set up the excel workbook with a data connection to the json file. It uses PowerQuery.

Once you have it in the format you need, you can use the Execute Macro activity in UiPath and use this to refresh the workbook each time your process is run, making it dynamically updated from the source Json File.

Hi

Thanks. This looks promising. Can you please provide me more info on this? I m not familiar with powerquery

This is essentially how you set up your connection to the JSON file.

You can reformat the table so that it contains only the columns/data you need from the source. You can then reread the data back into a datatable using the Excel Read Range activity.

The only thing to remember is to complement this JSON-excel conversion with a macro to ensure the excel file is refreshed by the bot. Therefore the excel workbook needs to saved as ‘macro-enabled’ and the following vba script added.

Then use the ‘Execute Macro’ activity in UiPath to make the whole process dynamic.

ExMacro

This will ensure that latest data in the JSON file is reflected in the subsequent excel table.

type of argument for For each should be JToken