ParseJson Type mismatch error

[DKRD]

Hi:
I get a Type mismatch with the following code:

Dim Parsed As Dictionary
Dim JSonText As String
...
...
JSonText = Response.Content
Set Parsed = JsonConverter.ParseJson(JSonText)

I believe this is due to some values being "" (zero length string)?

So I tried a different method using Response.Data which appears to be in the form of a Dictionary but I can't seem to retrieve the key names. I can loop through Response.Data and get the values if I explicitly state each key name (example: Response.Data(i)("[key name]")), but I can't seem to loop through the keys and output the key name and value.

What am I missing?

[zgrose]

In the simplest case:

Option Explicit

Public Sub EnumerateADictionary()

    Dim jsonString As String
    jsonString = "{ ""a"":"""", ""b"":""foo"", ""c"":""bar""}"
    
    Dim myDictionary As Dictionary
    Set myDictionary = ParseJson(jsonString)
    
    Dim dictionaryKey As Variant
    For Each dictionaryKey In myDictionary.Keys
        Debug.Print dictionaryKey & "=" & myDictionary(dictionaryKey)
    Next dictionaryKey
    
End Sub

Should produce the output:

a=
b=foo
c=bar

You can also look at some of the samples in #260 if you need to test what kind of value a key has. JSON like this

[{"a":true, "b":12, "c":[], "d":[1,2,3]},{"a":true, "b":12, "c":[], "d":[1,2,3]}]

can start to get more challenging if you're going in blind.

[DKRD]

Thanks zgrose,

Here's a condensed sample of the json string returned in Response.Content:

JSonText = "[{"row":1,"a:"0007","b:"Blue","c:"2008"},{"row":2,"a:null,"b:"","c:"1709"}]

The real response could have 100 rows with 17 keys, so the ultimate goal is to extract the data as records to write to a db. The Response.Data returned from WebClient.GetJson is, I'm guessing, a Collection. So how can I loop through the rows and get both the keys and values for each row? Ultimately I could explicitly use the key name and get the values as stated above but it seems like the long way around the problem when you could step through the keys with an index. I'm wondering if I'm using the correct tool for the problem. In any case I still prefer to learn the proper use of the functions.

[zgrose]

Here is any Excel-y way to dump your rows into a worksheet. There are lots of ways to skin this same cat but hopefully gets you closer.

Option Explicit

Public Sub IterateOverACollection()

    Dim jsonString As String
    jsonString = "[{""row"":1, ""a"":""0007"", ""b"":""Blue"", ""c"":""2008""}, {""row"":2, ""a"":null, ""b"":"""", ""c"":""1709""}]"
    
    Dim myCollection As Collection
    Set myCollection = ParseJson(jsonString)
    
    Dim myDictionary As Dictionary
    Dim i As Long, j As Long
    Dim StartCell As Range
    Set StartCell = ActiveSheet.Range("A1")
    
    For i = 1 To myCollection.Count
        Set myDictionary = myCollection(i)
        For j = 1 To myDictionary.Count
           StartCell.Offset(i - 1, j - 1) = myDictionary.Items(j - 1)
        Next j
    Next i
    
End Sub

Should output like:

[zgrose]

Better version that reads the object properties and makes into a table with headers:

Option Explicit

Public Sub IterateOverACollection()

    Dim jsonString As String
    jsonString = "[{""row"":1, ""a"":""0007"", ""b"":""Blue"", ""c"":""2008""}, {""row"":2, ""a"":null, ""b"":"""", ""c"":""1709""}]"
    
    Dim myCollection As Collection
    Set myCollection = ParseJson(jsonString)
    
    Dim myDictionary As Dictionary
    Dim i As Long, j As Long
    
    Dim StartCell As Range
    Set StartCell = ActiveSheet.Range("A1")
    
    Set myDictionary = myCollection(1)
    For j = 1 To myDictionary.Count
        StartCell.Offset(0, j - 1) = myDictionary.Keys(j - 1)
        StartCell.Offset(0, j - 1).Font.Bold = True
    Next j
    
    For i = 1 To myCollection.Count
        Set myDictionary = myCollection(i)
        For j = 1 To myDictionary.Count
           StartCell.Offset(i, j - 1) = myDictionary.Items(j - 1)
        Next j
    Next i
    
End Sub

[DKRD]

Thanks zgrose. That method did the job.