How to convert complex JSON file in to a datatable

Hello every one.
I need to convert a complex JSON file to a datatable within Studio 2021.10.8.
I have searched a lot via Google, UiPath documentation, the UiPath Forum and even looked for the answer with ChatGPT, but unfortunately i did not get the solution for my issue.
I managed to get the result via importing the JSON file in to Excel (365) so that i could see the result, but i was not able to realize the same thing via Studio.

So i hope that someone of you is able to help me out.
Thanks in advance.

The JSON file and the Excel results file are attached.
The first of 2 records of the JSON file is also shown in the below Text
{
@odata.context”: “$metadata#Promos(status,extOfferId,ID,SalesOrgDistCh(salesOrganisation,distributionChannel,up__ID),Terms(termTypeName,discountType,up__ID,ID,TermProducts(articleNumber,up__up__ID,up__ID,ID)),GeneralDescriptions(brandProductDescription,up__ID,ID),Tactics(tacticType,up__ID,ID))”,
@odata.count”: 20,
“value”: [
{
“status”: “06”,
“extOfferId”: “3002113”,
“ID”: “7ac825e2-7845-4210-b20d-05df6f704555”,
“SalesOrgDistCh”: [
{
“up__ID”: “7ac825e2-7845-4210-b20d-05df6f704555”,
“salesOrganisation”: “Sales1”,
“distributionChannel”: “10”
},
{
“up__ID”: “7ac825e2-7845-4210-b20d-05df6f704555”,
“salesOrganisation”: “Sales1”,
“distributionChannel”: “20”
},
{
“up__ID”: “7ac825e2-7845-4210-b20d-05df6f704555”,
“salesOrganisation”: “Sales2”,
“distributionChannel”: “10”
}
],
“Terms”: [
{
“up__ID”: “7ac825e2-7845-4210-b20d-05df6f704555”,
“ID”: “073236ff-b20e-4be3-9f1c-68f9f82c8113”,
“termTypeName”: “Buy”,
“discountType”: “02”,
“TermProducts”: [
{
“up__up__ID”: “7ac825e2-7845-4210-b20d-05df6f704555”,
“up__ID”: “073236ff-b20e-4be3-9f1c-68f9f82c8113”,
“ID”: “f4c2a7df-b249-40c4-8c0f-adda8c64196b”,
“articleNumber”: “588454”
},
{
“up__up__ID”: “7ac825e2-7845-4210-b20d-05df6f704555”,
“up__ID”: “073236ff-b20e-4be3-9f1c-68f9f82c8113”,
“ID”: “741cf629-c665-467c-9811-6b2c75124760”,
“articleNumber”: “588457”
},
{
“up__up__ID”: “7ac825e2-7845-4210-b20d-05df6f704555”,
“up__ID”: “073236ff-b20e-4be3-9f1c-68f9f82c8113”,
“ID”: “0080293e-1308-4b02-84af-bd8285f14838”,
“articleNumber”: “588637”
}
]
}
],
“Tactics”: [
{
“up__ID”: “7ac825e2-7845-4210-b20d-05df6f704555”,
“ID”: “99eec720-b86c-42ee-bc59-9a73e777e6d3”,
“tacticType”: “80”
}
],
“GeneralDescriptions”: [
{
“up__ID”: “7ac825e2-7845-4210-b20d-05df6f704555”,
“ID”: “01922d59-e63e-423a-9272-96d0de846afd”,
“brandProductDescription”: “Hello UiPath”
}
]
}
]
}
UiPath Forum - HTTP Output.json (5,1 KB)
UiPath Forum - HTTP Output.xlsx (27,8 KB)

That’s a really old version. You really need to upgrade. Anyway, I’ll work on the logic for this and see what I can come up with.

Here you go…

Main.xaml (41.7 KB)
Marco data.xlsx (8.4 KB)

UiPath.System.Activities 2.6.21
UiPath.WebAPI.Activities 1.16.2

The packages may be a problem for you since you are on such an old version.

Thanks Paul on helping me out on this.
Towards my initial request it would be the solution.
At this point it is a real good start for me, but i would like to make the situation more generic, because now we need to work on the supplied JSON file, but the next response could provide a JSON file with completely other data at other different levels (other Array headers).
So if you have some spare time and can help me on that as well, it would be very appreciated.
Thanks in advance

About the only way to do that would be to know all the possible headers and use IFs to look and see if they exist and process them. You can’t make it generic to just magically know how to process multiple different formats.

Paul, i already created a dt containing all possible headers that i need based on the keywords that were provided to the API request.
For that reason i was also able to already create the reporting data table with these headers.
Now it is just a question on how to build up each reporting row.

But thanks very much and i will add your reply as the solution to my initial request.

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