×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Help with Json

thread855-1819730

Help with Json

Help with Json

(OP)
I have the following JSON output from a URL

{
"motd": {
"msg": "If you or your company use this project or like what we doing, please consider backing us so we can continue maintaining and evolving this project.",
"url": "https://exchangerate.host/#/donate"
},
"success": true,
"base": "AUD",
"date": "2022-12-10",
"rates": {
"AED": 2.496891,
"AFN": 59.467886,
"ALL": 74.793316,
"AMD": 268.658185,
"ANG": 1.225349,
"AOA": 342.648579,
"ARS": 115.280112,
"AUD": 1,
"AWG": 1.223333,

}
}

My code below extracts the rates information but with the three letter cod and the value into a string like:

CODE

Dim url As String = "https://api.exchangerate.host/latest?base=AUD"

            Dim request As HttpWebRequest
            Dim WebResponse As HttpWebResponse = Nothing
            Dim reader As StreamReader

            request = DirectCast(WebRequest.Create(url), HttpWebRequest)
            WebResponse = DirectCast(request.GetResponse(), HttpWebResponse)
            reader = New StreamReader(WebResponse.GetResponseStream())

            Dim RawResponse = reader.ReadToEnd()
            Dim json As String = RawResponse

            Dim jsonObject As Newtonsoft.Json.Linq.JObject = Newtonsoft.Json.Linq.JObject.Parse(json)

            Label1.Text = jsonObject.SelectToken("rates").ToString

CODE

{ "AED": 2.495872, "AFN": 59.443631, "ALL": 74.537735, "AMD": 269.1604, "ANG": 1.225355, "AOA": 342.552248, "ARS": 115.456501, "AUD": 1, "AWG": 1.223042, "AZN": 1.155178, "BAM": 1.260642, "BBD": 1.359196, "BDT": 70.269386, "BGN": 1.260442, "BHD": 0.256184, "BIF": 1401.68335, "BMD": 0.67943, "BND": 0.918869, "BOB": 4.698483, "BRL": 3.559331, "BSD": 0.679603, "BTC": 3.9E-05, "BTN": 55.986922, "BWP": 8.762125, "BYN": 1.717008, "BZD": 1.370884, "CAD": 0.932719, "CDF": 1397.217232, "CHF": 0.63587, "CLF": 0.021571, "CLP": 585.595319, "CNH": 4.731697, "CNY": 4.727379, "COP": 3278.8834, "CRC": 398.76697, "CUC": 0.679886, "CUP": 17.495448, "CVE": 71.036587, "CZK": 15.655931, "DJF": 121.03866, "DKK": 4.798556, "DOP": 37.384907, "DZD": 93.763028, "EGP": 16.692146, "ERN": 10.191969, "ETB": 36.369497, "EUR": 0.644695, "FJD": 1.491565, "FKP": 0.554593, "GBP": 0.554517, "GEL": 1.807457, "GGP": 0.554687, "GHS": 8.821982, "GIP": 0.554671, "GMD": 42.414134, "GNF": 5962.079622, "GTQ": 5.377711, "GYD": 142.323738, "HKD": 5.290909, "HNL": 16.78467, "HRK": 4.872813, "HTG": 96.510913, "HUF": 270.022996, "IDR": 10601.290905, "ILS": 2.323138, "IMP": 0.554417, "INR": 56.013894, "IQD": 992.347569, "IRR": 28094.812915, "ISK": 96.426679, "JEP": 0.554837, "JMD": 104.621903, "JOD": 0.482642, "JPY": 92.795334, "KES": 83.52796, "KGS": 57.718952, "KHR": 2803.970113, "KMF": 316.87418, "KPW": 611.49584, "KRW": 886.172632, "KWD": 0.208819, "KYD": 0.566868, "KZT": 319.803998, "LAK": 11757.475046, "LBP": 1027.999431, "LKR": 249.868602, "LRD": 104.633831, "LSL": 11.758275, "LYD": 3.296476, "MAD": 7.176456, "MDL": 13.173462, "MGA": 3005.199144, "MKD": 39.668915, "MMK": 1427.81879, "MNT": 2314.826039, "MOP": 5.449939, "MRU": 25.755219, "MUR": 29.671919, "MVR": 10.429459, "MWK": 697.884998, "MXN": 13.437892, "MYR": 2.992467, "MZN": 43.416532, "NAD": 11.659568, "NGN": 301.539681, "NIO": 24.731812, "NOK": 6.798568, "NPR": 89.578942, "NZD": 1.059807, "OMR": 0.261433, "PAB": 0.679757, "PEN": 2.627768, "PGK": 2.395946, "PHP": 37.624472, "PKR": 152.776964, "PLN": 3.025923, "PYG": 4855.481002, "QAR": 2.488362, "RON": 3.176028, "RSD": 75.63698, "RUB": 42.475591, "RWF": 728.936617, "SAR": 2.555519, "SBD": 5.569607, "SCR": 9.244687, "SDG": 388.300175, "SEK": 7.033013, "SGD": 0.921544, "SHP": 0.554676, "SLL": 12002.294297, "SOS": 386.539849, "SRD": 21.465205, "SSP": 88.504367, "STD": 15507.515029, "STN": 15.783755, "SVC": 5.949858, "SYP": 1707.111441, "SZL": 11.757957, "THB": 23.526181, "TJS": 6.867343, "TMT": 2.382163, "TND": 2.180303, "TOP": 1.591945, "TRY": 12.672378, "TTD": 4.613765, "TWD": 20.865351, "TZS": 1585.556628, "UAH": 24.983723, "UGX": 2508.888349, "USD": 0.679936, "UYU": 26.574618, "UZS": 7650.465445, "VES": 9.226743, "VND": 16170.654069, "VUV": 80.204053, "WST": 1.833321, "XAF": 422.789363, "XAG": 0.029254, "XAU": 0.001095, "XCD": 1.836708, "XDR": 0.513675, "XOF": 422.789207, "XPD": 0.00104, "XPF": 76.914429, "XPT": 0.001198, "YER": 170.030078, "ZAR": 11.791482, "ZMW": 11.827688, "ZWL": 218.779657 }

How can I modify my vb code to get the three letter code and the value seperated into seperate columns for example to put into a database table

RE: Help with Json

Unless someone has any inkling of what Newtonsoft.Json does, you probably won't get a reply here.

Does jsonObject.SelectToken("rates") return a dictionary or do you have to access the elements individually like when using JSON in jscript running in vbscript code. Bsically, can you do this with the object returned

CODE

set rates = jsonObject.selectToken("rates")
for each key in rates
    debug.print key & " " & rates(key)
next

RE: Help with Json

This may not be the best solution, but...

I usually work in C#, so you will need to convert this to VB, which should be relatively simple.

Whenver I do something like this, I create a C# object to hold the data, something like this:

CODE

public class ApiData
    {
        public bool success { get; set; }
        public Rate rates { get; set; }

        public class Rate
        {
            public float AED { get; set; }
            public float AFN { get; set; }
            public float ALL { get; set; }
            public float AMD { get; set; }
            public float ANG { get; set; }
            public float AOA { get; set; }
            public float ARS { get; set; }
            public float AUD { get; set; }
            public float AWG { get; set; }
            public float AZN { get; set; }
            public float BAM { get; set; }
            public float BBD { get; set; }
            public float BDT { get; set; }
            public float BGN { get; set; }
        }
    }

I realize there are more exchange rates, I added a couple so you can see how it works.

Then, I use Newtonsoft.Json to convert the json string to an object, like this.

CODE

public ApiData ExchangeRate()
    {
        ApiData output = new ApiData();
        string url = "https://api.exchangerate.host/latest?base=AUD";

        HttpWebRequest request;
        HttpWebResponse WebResponse;
        StreamReader reader;

        request = HttpWebRequest.CreateHttp(url);
        request.Method = "GET";
        WebResponse = (HttpWebResponse)request.GetResponse();

        reader = new StreamReader(WebResponse.GetResponseStream());

        string RawResponse = reader.ReadToEnd();
        output = JsonConvert.DeserializeObject<ApiData>(RawResponse);
        
        return output;
    }

You can access each item like this:

CODE

ApiData yourData = ExchangeRate();
Console.WriteLine(yourData.rates.AED);
Console.WriteLine(yourData.rates.AFN);

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close