Convert Json to Datatable (Specific data)

Hi Everyone, I have a json file i want to convert that json data into excel data. I’m unable to extract few details. Can anyone help me how to solve this. I’m attaching json file and my required output below.

DOC042319-04232019125916.1.1.json (3.4 KB)

O/P:
Id: 0001
Left: 303
Right: 819
Top: 520
Bottom: 561

Id: 0002
Left: 954
Right: 1081
Top: 518
Bottom: 563

Etc…

I need all id’s data , in excel sheet

Hi,

Follow below steps

1- Use build datatable activity for creating data table.
2- read json file store it into string variable(jsonString).
3-use for each loop like this split(jsonString,Environment.NewLine)
4- use If condition to check specified string for example id (if string.contains id)->than
5-use assign variable to store value into variable ->split (string,":")(1)
6-When you get values of all variable then use add data row activity to add data into datatable.

Hope it will work

The text he pasted into the post isn’t the JSON. The JSON parsing activities/functions should be used, not split.

We do use the serialize JSON Acitivity form UiPath.WebApi.Activities

Prepare the target datatable structure with Build DataTable Activity

Deserialize the JSON string

Iterate over the blocks by:

For each activity - item in myJObject.SelectToken("*.addData:AdditionalInfo.*.addData:Blocks").Values(Of JOBject)
Alternate: myJObject.SelectToken("..addData:Blocks")
Type Argument: JObject

  • Within the loop acces
    • the id by: item("@Id").Value(Of String)
    • Left by item.("addData:Block")("addData:Rect")("@Left").Value(Of String)
    • like left

collect within each loop the values of intererst andd add it to the datatable

Cross check protoype box did pass:



grafik

1 Like

@ghazanfar This is not working in for each we don’t get environment.newline

@postwick Yes that won’t work

@ppr i’m using those activities i’m able to extract some data but when it come to some data i’m unable to extract. I need below details.

Id: 0001
Left: 303
Right: 819
Top: 520
Bottom: 561

see the updated post above

@ppr for me it is showing error. if u have time means can u share me the xaml or screenshot of the activity .

this was just proofing that it is working
the access for the for each and items is different see post above

if an error / validation message is displayed then please also share it with us

Prototyping code snippets we do in the immediate box

@ppr check these once where i’m doing wrong



this was mentioned above

@ppr i’m getting ID correctly when i try to get below details it is throwing error.

@ranaprathap928
please share with us what was used for the for each value - yellow mark in screenshot above:
grafik

for left it was mentioned:

also take note on

you used item("@Id").toString which has a different meaning

Just try to read carefully the posts with the help when implementing. This will lead you faster to the working bot and reduces ping-pong. Thanks for support

@ppr i tried that code also it is not working for me, I’m getting ID only , LEFT, RIGHT details showing error

Hi @ranaprathap928

Please refer below video link:-

Thanks,
Prashant

@PRASHANT_GABHANE Video is not available

Hi @ranaprathap928

Open the link in the YouTube directly.
Or Click on watch on youtube

@PRASHANT_GABHANE with this video also i’m not getting required output

Can you please Share Details of your current Implementation or the xaml? Thanks