Extracting info from JSON file received through REST API

  • 184 Views
  • Last Post 27 October 2020
ayushk@abm-bmpl.com posted this 01 October 2020

Hi,

I used Rest Api client to extract data from a webpage in json format. Now I want to do two things

1. Convert data in json format into excel file.

2. Extract data from a particular index field to be used in further process.

  • Liked by
  • admin@repcat.cat
Order By: Standard | Newest | Votes
RepCat posted this 27 October 2020

Thanks, I don't believe I explained myself clearly.

For my script as shown above, in the second flow, that only returns the letter "J", i.e. the first letter of "JohnDoe".

Data:

{"Members":[{"ID":1,"Name":"JohnDoe"},{"ID":2,"Name":"JaneDoe"},{"ID": 3,"Name":"JamesFranco"}]} 

How do I access the value of the key "Name" for ID 3? That is to say, return

JamesFranco

Edit: It would be useful to me possibly others if the solution could be given for all the script languages that 'Format Data' supports.

Edited again: Working solution. Flow is below. Transform language is VB.Net.

Closeup of 'Format Data' screen:

Or:

Closeup of 'Format Text' code:

 Desired output:

Regards,
RepCat

Intellibot Support posted this 27 October 2020

Hi,

In transform data we are taking the first item from the list of collection. In Transform data we have written the script " Return Value(0) ". Hope this helps!

Thanks & Regards,
Intellibot Support.

RepCat posted this 27 October 2020

Hi, I had the same problem as ayushk@abm-bmpl.com:

"Conversion from type 'JObject' to 'String' is invalid".

That part I worked around as shown:

As you can see, I took the variable, serialized it and deserialized it. Now the error is gone. At the DeserializeObject port:

So far so good.

The comment in the provided automation is:

If members always gives only one result then we can always refer to the first index using transform data

Might I trouble for a snippet of code you would use to access the ID and Name values of the data shown above using Transform Data?

Regards,
RepCat

 

Intellibot Support posted this 06 October 2020

Hi,

Our team will connect with you over the email and share the details on this.

Thanks & Regards,

Intellibot Support.

ayushk@abm-bmpl.com posted this 06 October 2020

Hi,

I'm still getting the same error "Conversion from type 'JObject' to 'String' is invalid".

And the database members has multiple objects. The above format was just an example of how the database is nested.

Consider the structure of the database as below:

{

   "Members":[

      {

         "ID":"1",

         "Name":"JohnDoe"

      },

      {

         "ID":"2",

         "Name":"JaneDoe"

      },

      {

         "ID":"3",

         "Name":"JamesFranco"

      }

]      

}

 

 

Thanks

  • Liked by
  • admin@repcat.cat
Intellibot Support posted this 06 October 2020

Hi,

Please find the sample automation  below on how to extract the above mentioned data from Json Format, Hope it helps!

https://drive.google.com/file/d/10DAmdA9QIAyUyimpfHtPXegEpI0HK5Rs/view?usp=sharing

Thanks & Regards,

Intellibot Support.

ayushk@abm-bmpl.com posted this 05 October 2020

Hi,

I tried the method you shared. I received the error as shown in image below.

My json file is nested like:

{    "Members": [            {                "Id"1,                "Name""JohnDoe",            }                       ] }

I want to access the ID and Name data

Intellibot Support posted this 01 October 2020

Hi,

Please find the sample automation  below on how to extract data from Json Format, Hope it helps!

https://drive.google.com/file/d/13O4E40vEne7zLgcvNZ-ducRNE53o8rgN/view?usp=sharing

Thanks & Regards,

Intellibot Support.

Close