How to extract the data from Json to Excel

Help
1

Hi,

I am trying to download Json file with HTTP request. The next step for me is extracting data from the Json file to Excel data table. Please advice how could I extract/convert it into Excel format.

Thank you.

2

Can we get a look at required Jason file? Does it have a fixed format ?

3

Hi @RishiVC1,

Sorry that I couldn’t upload the Json file as it is confidential. However, I could provide you some details on how does it looks like.

Yes, it has a fixed format. The format would be “data”:[{“id”: 0111, “name”: xxx:, “status”: “C”,“address”:“xxx”:{“id”: 0112, “name”: xxx:, “status”: “C”,“address”:“xxx”:{“id”: 0113, “name”: xxx:, “status”: “C”,“address”:“xxx”}]}.

And I wanted to convert and sort the data into excel with the header, Id, name, status and address.

Thank you.

4

hi @leewe01

If You using HTTP Request and got your variable in output you need to deserialize jason. In assign window You can ask about needed value for example: variablefromdeserializetion(“value”)(0)(“nameofyourcolumn”).tostring assigned value you need to put in to the predefined table using add data row. Last thing is write ready table to excel file if You need sort it you can use “sort table”

Regards
@fudi5

5

Hi @leewe01

You provided incorrect json string, I’m going to assume you meant:

{"data":[{"id": 0111, "name": "xxx", "status": "C","address":"xxx"},{"id": 0112, "name": "xxx", "status": "C","address":"xxx"},{"id": 0113, "name": "xxx", "status": "C","address":"xxx"}]}

I would do it that way:

  1. Create DataTable with necessary columns and headers
  2. Use ‘Deserialize json’ activity on string (I will refer to Output JsonObject as json)
  3. Loop throug json.Item(“data”) as per below screenshotimageimage
  4. To get needed value use Item property of JObject for example: item.Item("name:).tostring
  5. Add needed data to DataTable using Add Data Row activity
  6. Save DataTable to the file (Write Range activity)
1 Like
6

@leewe01, Along with Heynow’s solution, we can also use DeserializeJson activity to convert a JSON to datatable in a single activity. The type Argument has to be changed to Datatable to get the require output.

[
	{ 
		"id":"0111",
	        "name":"xxx",
	        "status": "C",
                "address":"xxx"
        },

	{ 
		"id": "0112",
	        "name":"xxx",
	        "status": "C",
                "address":"xxx"
        },

	{ 
		"id": "0113",
	        "name": "xxx",
	        "status": "C",
                "address":"xxx"
        }
]

Refer the below sample,
JsonToDatatable.zip (2.2 KB)

3 Likes
7

Hi @heynow

Here is the example of my json file.
example.pdf (160.3 KB)

I have tried your suggested way. Unfortunately, the program has shown error (Output data table: Object reference not set to an instance of an object.) after the first ID. It can’t loop the second ID.

8

@leewe01 can you attach your workflow?

10

Hi @heynow,

I have removed some confidential data, so please find the copy here.

Text.zip (7.8 KB)

11

@leewe01

I can’t see DataTable creation part in your workflow, can’t really replicate your issue.

Your “add” object keeps additional JObject inside, you can get value of it like this: item.Item("add").Item("add").ToString

12

Hi @heynow,

I haven’t create the data table yet, as the execution stopped at Write line activity after looped the first ID. I checked the output from the Output panel.

What’s the way to keep it loops until the final ID? Thanks!

13

Hi @leewe01,

What kind of loop ? If you are using while then the best way make an incremantation its mean assign before and after loop. Condition should be counter >0. First assign counter = 0 last assign conter=counter +1.

Regards
@fudi5

15

Hi @heynow ,

I’ve added Add Data Row activity to the workflow, could you please advise what’s the input for ArrayRow or DataRow?

Thanks.

16

@leewe01 Your json data has a nested structure. What data you want in your data table?

image
image1034×564 24.1 KB

17

Hi @Madhavi,

Here are the data that I wanted it in my data table.

id name status add(name) add (add)
001 xxx x xxx xxx
002 xxx x xxx xxx
003 xxx x xxx xxx
… … … … …
18

@leewe01
Use DeserializeJson activity and convert the json string to json object(jsonResult).
Loop through the array node in the json result. (jsonResult(“data”)
Add values of each array item into a datatable.
Once all the records are processed, Use write range activity to write into excel file.

19

Thanks, @Madhavi!

20

@Madhavi Hi, Could you please tell the package name that include the " DeserializeJson “ activity ?

21

@Harvey the package to get “Deserialixe Json” is “Uipath.web.activities”

22

Thanks !