How to parse JSON object and sub JSON object of currency API

Help
1

Hi everybody,

I am trying to use the JSON data of the currencyconversion api and try to parse them and save to excel file.

Now, the problem is that I dont understand how to parse a sub JSON object.

The api url is : https://exchangeratesapi.io/

So the sample data would be:

{ “base”:“EUR”,
“date”:“2018-05-07”,
“rates”: {
“AUD”:1.5882,
“BGN”:1.9558,
“BRL”:4.2162,
“CAD”:1.5338,
“CHF”:1.1964,
“CNY”:7.5778,
“CZK”:25.518,
“DKK”:7.4486,
“GBP”:0.8801,
“HKD”:9.3428,
“HRK”:7.3985,
“HUF”:314.34,
“IDR”:16735.4,
“ILS”:4.3121,
“INR”:79.904,
“ISK”:121.8,
“JPY”:130.15,
“KRW”:1288.09,
“MXN”:23.0215,
“MYR”:4.6947,
“NOK”:9.619,
“NZD”:1.7013,
“PHP”:61.763,
“PLN”:4.2537,
“RON”:4.6563,
“RUB”:74.8299,
“SEK”:10.5383,
“SGD”:1.5917,
“THB”:37.944,
“TRY”:5.0827,
“USD”:1.1902,
“ZAR”:14.973
}
}

Now I am trying to loop over the rates sub JSON object and automatically add the key for example “CHF” to excel file and access the value also automatically without accessing it by using the exact string value of it.

How can I do this.

2

Hi,

Quick workaround which will help you extracting the rates.

  • Do a first “Deserialize Json” on the given string and set the TypeArgument as Jobject - Assign value to your variable
  • Do a second “Deserialize Json”. "[" & jObjVar("rates").ToString & "]". the “” are required if you want to deserialize as a datatable (with TypeArgument and output to Datatable) to write directly to excel after, As it will requires you a Jarray.

The will give the following horizontal output (as it is the case for datatables)

image

If you want to deserialize it as dictionary<String,Decimal> and being able to access it via the key, you will have to remove the “” from the previous expression (it requires and Jobject)

Cheers

5 Likes
3

Ok, I am gonna try this out now.

4

Would it be possible to give a bit more explanation?

I will include my code example for now, but I do not completely understand your explanation.

I dont completely understand how to write directly to datatable and then to excel with your workaround.

The name is wrong because its included with another project, my apologies. It is about currencies this file.
weatheroverview.xaml (11.6 KB)

5

Hi,

Suppose if you want to get “BRL” currency . You can try j.(“rates”)(“BRL”)

can you go through the below link

Thanks,
karthik.

2 Likes
6

Yeah thx, I already saw this link. The meaning of what I wanna do is to avoid to use “BRL” or “EUR”. I just wanna save all the currencies with their value in an excel sheet and also loop over them to make comparisons.

If I do have to use every time the name of all the keys that are always different then I have a lot of extra code which is not good practice.

7

Hi,

A made two changes to make what you submited work:

  • Change Type argument and output of the second deserialize json to datatable

image

  • Providing filepath is not required in your case to get the ouput of the httprequest

Let me know if everything works fine on your side.

cheers
Edit: attachment is not having AddHeader on the writerange, probably you will what to set it checked
Main.xaml (9.4 KB)

1 Like
8

hey, it works now yes.

So, now I also just have a datatable that i could loop over with a “foreach row” if I am right to make customizations if I want? right? or am i seeing this wrong?

9

Now you have an excel file (or a Datatable) with one row and a header.

The simplest way to loop trough each currency values will be

For each value As Double (you will need to set TypeArgument) in dtCurrencies.Rows(0).ItemArray
WriteLine(value.toString)
Next

If you want to loop both for the currency name and its value, then it is more appropriate to use a dictionary than a datatable to deserialiaze as a explain on my previous post.
It will requires you to remove the “” and set your TypeArgument on the second deserialize json to Dictionary<String,Decimal>.

Then loop using a for normal for each and check/writeline item.key and item.value.

Cheers

1 Like
10

Congrats,

I have made the video ,

Check once.

11

Hello Karthik,

can you please help me retrieve the values from below json array:

{“passwords”: [
{
“oldPassword”: “demo2”,
“newPassword”: “demo1”,
“dialogID”: “123456”,
“username”: “ckuhroeb”
},
{
“oldPassword”: “demo2”,
“newPassword”: “demo1”,
“dialogID”: “123456”,
“username”: “ckuhroeb”
},
{
“oldPassword”: “demo2”,
“newPassword”: “demo1”,
“dialogID”: “123456”,
“username”: “ckuhroeb”
},
{
“oldPassword”: “demo2”,
“newPassword”: “demo1”,
“dialogID”: “123456”,
“username”: “ckuhroeb”
},
{
“oldPassword”: “demo”,
“newPassword”: “demo1”,
“dialogID”: “123456”,
“username”: “ckuhroeb”
},
{
“oldPassword”: “demo12”,
“newPassword”: “demo1”,
“dialogID”: “123456”,
“username”: “ckuhroeb”
}
]}

12

Hi Rakesh,

Please follow below tutorial,

if you are not able to understand, please mention in comments section so that I can explain you clearly.

please do subscribe to the channel for more learning.
Thanks,
Karthik.

13

Thanks Karthik.

I would appreciate if you can send me a workflow as I am not able to loop through the activity as there is a Json object followed by Json array.

14

similar example implementation.DesrializedJson.xaml (6.8 KB)
Main.xaml (10.9 KB)

15

I cannot open. It says unserialized Activities!

16

Hello,

I’m facing similar issue, Please let me know how did you process this.

Thank you