JSON format non convertible

[Masfeir]

Hi everyone, I'm brand new on GitHUB, VBA, and coding in general but I'm very excited to be here. I'm tasked to extract data from a web API on excel, which is why I tried using the JsonConverter (thanks for building it). Now here's my issue: running the VBA pops execution errors (450, 5, or other) it changes every time I fix the script. The JSON format is similar to the following: [{"AA":"AAtext","BB":"BBtext","CC":"CCnumber"}] (you get the gist of it)

Now when I run it like this it shows an error. But, I tried to change the format to match the example from the README.md file and the script ran just fine outputting the expected values. The successful format is as follows: "{""AA"":'AAtext',""BB"":'BBtext',""CC"":CCnumber}" Changes made: 1) removing brackets [] 2) adding quotations "" before and after the accolades 3) doubling quotations on parameters ex ""AA"" 4) adding apostrophes '' to text values 5) removing quotations "" from number values

Quite frankly I don't understand what the issue is, I am unable to share my actual code because of data ownership and sensitivity, but here's a snippet of what it looks like: ................................................................................................................................................................................................................................. Sub Beta()

' Simple script for data extraction

Dim url As String, parameters As String
url = "..."
parameters = "..."

' Set the request Dim request As New WinHttpRequest request.Open "Get", url & parameters

request.SetRequestHeader "Accept", "text/json"
request.SetRequestHeader "Authorization", "key"

' Send request request.Send

' Response JSON verification If request.Status <> 200 Then MsgBox "Error" & request.responseText Exit Sub End If

' Parse the JSON Dim response As Object
Set response = JsonConverter.ParseJson([{"AA":"AAtext","BB":"BBtext","CC":"CCnumber"}]) A 'Set response = JsonConverter.ParseJson("{""AA"":'AAtext',""BB"":'BBtext',""CC"":CCnumber}") B Debug.Print response("CCnumber")

End Sub .................................................................................................................................................................................................................................

A is the original extracted JSON format from the API -> does not work B is the modified JSON format yielding the right answer from CCnumber

Any help figuring this out is much appreciated, thanks in advance and thanks again for building the JsonConverter!!

[aholden10]

When you say "Removing [] brackets" they are there to denote an array...your data is coming back as a JSON array so you need to address the items by prefacing with the array identifier [0]

So my starting suggestion would be to explore your data structure in a JSON parser or JSON viewer first. As you may know Notepad++ has them available as plugins if you don't want to put sensitive data into an online tool.

On Tuesday, July 19, 2022 at 11:30:48 AM EDT, Masfeir ***@***.***> wrote:  

Hi everyone, I'm brand new on GitHUB, VBA, and coding in general but I'm very excited to be here. I'm tasked to extract data from a web API on excel, which is why I tried using the JsonConverter (thanks for building it). Now here's my issue: running the VBA pops execution errors (450, 5, or other) it changes every time I fix the script. The JSON format is similar to the following: [{"AA":"AAtext","BB":"BBtext","CC":"CCnumber"}] (you get the gist of it)

Now when I run it like this it shows an error. But, I tried to change the format to match the example from the README.md file and the script ran just fine outputting the expected values. The successful format is as follows: "{""AA"":'AAtext',""BB"":'BBtext',""CC"":CCnumber}" Changes made:

• removing brackets []
• adding quotations "" before and after the accolades
• doubling quotations on parameters ex ""AA""
• adding apostrophes '' to text values
• removing quotations "" from number values

Quite frankly I don't understand what the issue is, I am unable to share my actual code because of data ownership and sensitivity, but here's a snippet of what it looks like: ................................................................................................................................................................................................................................. Sub Beta()

' Simple script for data extraction Dim url As String, parameters As String url = "..." parameters = "..."

' Set the request Dim request As New WinHttpRequest request.Open "Get", url & parameters request.SetRequestHeader "Accept", "text/json" request.SetRequestHeader "Authorization", "key"

' Send request request.Send

' Response JSON verification If request.Status <> 200 Then MsgBox "Error" & request.responseText Exit Sub End If

' Parse the JSON Dim response As Object Set response = JsonConverter.ParseJson([{"AA":"AAtext","BB":"BBtext","CC":"CCnumber"}]) A 'Set response = JsonConverter.ParseJson("{""AA"":'AAtext',""BB"":'BBtext',""CC"":CCnumber}") B Debug.Print response("CCnumber")

End Sub .................................................................................................................................................................................................................................

A is the original extracted JSON format from the API -> does not work B is the modified JSON format yielding the right answer from CCnumber

Any help figuring this out is much appreciated, thanks in advance and thanks again for building the JsonConverter!!

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you are subscribed to this thread.Message ID: @.***>

[houghtonap]

See my response to issue #221. You might find it helpful in the future to search the existing issues to see whether someone else experienced a similar issue. Hope that helps.

[Nick-vanGemeren]

If you have a serious sensitive application, you should code to handle the cases where the API returns zero or multiple records. (test response .Count) The square brackets do not convert to an array - it's a Collection. If you don't use For Each, you access records with an index (1-based) and not a subscript (0-based).

[Masfeir]

See my response to issue #221. You might find it helpful in the future to search the existing issues to see whether someone else experienced a similar issue. Hope that helps.

See my response to issue #221. You might find it helpful in the future to search the existing issues to see whether someone else experienced a similar issue. Hope that helps.

Thanks for your comment. I don't see how issue #221 is relevant to my question?

[Masfeir]

If you have a serious sensitive application, you should code to handle the cases where the API returns zero or multiple records. (test response .Count) The square brackets do not convert to an array - it's a Collection. If you don't use For Each, you access records with an index (1-based) and not a subscript (0-based).

Hi Nick, thank you for your feedback. Would you mind being more explicit? I tried so many different things, I also set a collection and dictionary to extract the data and it did not work either. Below is the code I used:

Dim response As Object Set response = JsonConverter.ParseJson(request.responseText)

Dim RateCollec As Collection Set RateCollec = response() Dim Rate As Dictionary For Each Rate In RateCollec Debug.Print Rate("Value") Next Rate

Thanks for the help.

[Nick-vanGemeren]

“it did not work” is a pretty useless error report. You should show how the output differed from your expectations for a certain input.

The line Set RateCollec = response() should have given you a run-time error 450 Wrong number of arguments … The brackets are interpreted as asking for a subroutine call. In fact,RateCollecis superfluous, sinceresponsewill be a Collection with your expected input. So you can just use responsedirectly.

The amount of response validation (Collection?, record count?, data fields? …) should reflect the consequences for your application of receiving bad/unexpected data. The web API can always change without warning.

Andrew pointed you to issue #221. That’s concerned with unintentionally calling the API asynchronously because of a default 3rd argument to the MSXML2.XMLHTTP Open method. I can’t find whether it also applies to WinHttpRequest, but it might be safer to set the argument to False explicitly.

Try the code below as a study of handling several response scenarios. A clear Immediate window will help. Use breakpoints and the Locals window to help understanding as necessary. Your application code will be different but may adopt or expand some elements.

Option Explicit

Sub TestCases()
    TestJSON "[{'AA':'AAtext','BB':'BBtext','CC':'CCnumber'}]"  ' Expected data
    TestJSON "[g{'AA':'AAtext','BB':'BBtext','CC':'CCnumber'}]" ' invalid JSON
    TestJSON "[]"                                               ' No records
    TestJSON "[{'AA':'AAtext','CC':'CCnumber'}," & vbCr & _
             "{}]"                                              ' 2 records, last empty
End Sub

Sub TestJSON(rText As String)
    Dim response As Object
    Dim Rate As Dictionary
    Debug.Print "Testing: "; rText
    On Error GoTo myError
    Set response = JsonConverter.ParseJson(rText)
    If VBA.TypeName(response) <> "Collection" Then Err.Raise 10000, , "Unexpected data returned by API"
    Debug.Print "Records received: "; response.Count
    If response.Count < 1 Then Err.Raise 10000, , "Unexpected record count"
    For Each Rate In response
        Debug.Print "Value of CC: "; Rate("CC")
      Next Rate
    Exit Sub
myError:
    ' Dump rText to file (not coded)
    Debug.Print "Error"; Err.Number; Err.Description
  End Sub

[aholden10]

Even though I didn't ask the question I find that to be a really interesting piece of code to traverse the JSON! My solution is more limited, does not include any error checking, assumes the API always returns data in a known structure. To the original poster, you have to address the array issue - it's part of the 'path' leading to the item, so in the code below there is one JSON item returned (the array) and so any reference to elements has to start by getting inside the array. Option ExplicitSub Test() Dim Count As LongDim JSON As ObjectDim ResponseText As String ResponseText = "[{""AA"":""AAtext"",""BB"":""BBtext"",""CC"":""CCnumber""}]" Set JSON = ParseJson(ResponseText) Count = JSON.CountDebug.Print CountDebug.Print JSON(Count)("CC") End Sub

On Friday, July 22, 2022 at 08:08:07 PM EDT, Nick van Gemeren ***@***.***> wrote:  

“it did not work” is a pretty useless error report. You should show how the output differed from your expectations for a certain input.

The line Set RateCollec = response() should have given you a run-time error 450 Wrong number of arguments … The brackets are interpreted as asking for a subroutine call. In fact,RateCollecis superfluous, sinceresponsewill be a Collection with your expected input. So you can just use responsedirectly.

The amount of response validation (Collection?, record count?, data fields? …) should reflect the consequences for your application of receiving bad/unexpected data. The web API can always change without warning.

Andrew pointed you to issue #221. That’s concerned with unintentionally calling the API asynchronously because of a default 3rd argument to the MSXML2.XMLHTTP Open method. I can’t find whether it also applies to WinHttpRequest, but it might be safer to set the argument to False explicitly.

Try the code below as a study of handling several response scenarios. A clear Immediate window will help. Use breakpoints and the Locals window to help understanding as necessary. Your application code will be different but may adopt or expand some elements. Option Explicit

Sub TestCases() TestJSON "[{'AA':'AAtext','BB':'BBtext','CC':'CCnumber'}]" ' Expected data TestJSON "[g{'AA':'AAtext','BB':'BBtext','CC':'CCnumber'}]" ' invalid JSON TestJSON "[]" ' No records TestJSON "[{'AA':'AAtext','CC':'CCnumber'}," & vbCr & _ "{}]" ' 2 records, last empty End Sub

Sub TestJSON(rText As String) Dim response As Object Dim Rate As Dictionary Debug.Print "Testing: "; rText On Error GoTo myError Set response = JsonConverter.ParseJson(rText) If VBA.TypeName(response) <> "Collection" Then Err.Raise 10000, , "Unexpected data returned by API" Debug.Print "Records received: "; response.Count If response.Count < 1 Then Err.Raise 10000, , "Unexpected record count" For Each Rate In response Debug.Print "Value of CC: "; Rate("CC") Next Rate Exit Sub myError: ' Dump rText to file (not coded) Debug.Print "Error"; Err.Number; Err.Description End Sub

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: @.***>

[Masfeir]

Update: I figured it out. It was actually pretty simple. I did not understand that prior to this moment, but essentially the argument between the bracket and the accolade defines a specific collection of dictionaries within the larger collection converted from JSON. Since my JSON does not have any said argument, my code was showing different types of errors when I tried to call a sub collection. Basically, once I directly defined a dictionary within my response object the code ran perfectly. @Nick-vanGemeren even though I did not try your code I think it works since you went from the object to the dictionary immediately. Thank you for taking the time to help out!