Convert Json to Datatable (Specific data)

Help Activities
, ,
1

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

2

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

3

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

4

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
grafik855×205 84.1 KB

grafik
grafik840×193 76.1 KB

grafik
grafik
grafik848×44 12.2 KB

1 Like
5

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

6

@postwick Yes that won’t work

7

@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

8

see the updated post above

9

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

out1
out1922×155 6.53 KB

10

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

grafik
grafik898×445 119 KB

11

@ppr check these once where i’m doing wrong

WhatsApp Image 2021-09-10 at 10.51.57 PM
WhatsApp Image 2021-09-10 at 10.51.57 PM1600×966 401 KB

Er2
Er2874×482 24.3 KB

er1
er11044×216 10.3 KB

12

this was mentioned above

13

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

Er3
Er3986×547 30 KB

14

@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

15

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

16

Hi @ranaprathap928

Please refer below video link:-

Thanks,
Prashant

17

@PRASHANT_GABHANE Video is not available

18

Hi @ranaprathap928

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

19

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

20

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