Parsing JSON with VBA JSON parser

Question

I've looked at the examples on stack overflow and vba JSON parser : https://github.com/VBA-tools/VBA-JSON . I can't work out how to parse this complex JSON:

{
    "au": {
        "success": true,
        "data": {
            "events": {
                "Chicago Bulls_Orlando Magic": {
                    "participants": ["Chicago Bulls",
                    "Orlando Magic"],
                    "commence": "1489017900",
                    "status": "Pending",
                    "sites": {
                        "sportsbet": {
                            "odds": {
                                "h2h": ["1.86",
                                "1.99"]
                            },
                            "last_update": 1488956952
                        },
                        "tab": {
                            "odds": {
                                "h2h": ["1.70",
                                "2.10"]
                            },
                            "last_update": 1488957101
                        },
                        "crownbet": {
                            "odds": {
                                "h2h": ["1.83",
                                "1.98"]
                            },
                            "last_update": 1488957104
                        },
                        "williamhill": {
                            "odds": {
                                "h2h": ["1.83",
                                "2.00"]
                            },
                            "last_update": 1488957115
                        }
                    }
                }
            }
        }
    }
}

My goal is to get the event participants details into 1 database table and the sites data into its own table. I've followed the examples in GitHub but can only get to "Chicago Bulls_Orlando Magic". Where am I going wrong?

Public Function ScanJson()

    Dim FSO As New FileSystemObject
    Dim JsonTS As TextStream
    Dim JsonText As String
    Dim Parsed As Dictionary
    Dim strPath As String
    Dim blnSuccess As Boolean

    strPath = CurrentProject.Path & "\test_Json.txt"
    ' Read .json file
    Set JsonTS = FSO.OpenTextFile(strPath, ForReading)
    JsonText = JsonTS.ReadAll
    JsonTS.Close
    'clean string
    JsonText = Replace(JsonText, vbCrLf, "")
    JsonText = Replace(JsonText, vbTab, "")

    ' Parse json to Dictionary
    ' "values" is parsed as Collection
    ' each item in "values" is parsed as Dictionary
    Set Parsed = JsonConverter.ParseJson(JsonText)

    'test theres data
    blnSuccess = Parsed("au")("success")
    If blnSuccess Then
        For Each Value In Parsed("au")("data")
            On Error Resume Next
            Debug.Print Value("events")
            Debug.Print Value("events")(0)(1)
            Debug.Print Value("participants")(0)
            Debug.Print Value("participants")(1)
            Debug.Print Value("commence")
            Debug.Print Value("status")
        Next Value
    Else
        MsgBox "No data for Key: AU "
    End If

End Function

Tim Williams · Accepted Answer · 2018-01-16 00:22:14Z

2

Example code:

Sub Tester()
    Dim j, o, events, k, participants, v, sites

    'loading json from worksheet cell...
    Set j = JsonConverter.ParseJson(Sheet1.Range("A6").Value)

    Set events = j("au")("data")("events")

    For Each k In events
        Debug.Print "event", k

        Set participants = events(k)("participants")
        For Each v In participants
            Debug.Print , "participant", v
        Next v

        Set sites = events(k)("sites")
        For Each v In sites
            Debug.Print , "site", v
        Next v

    Next

End Sub

Output:

event         Chicago Bulls_Orlando Magic
              participant   Chicago Bulls
              participant   Orlando Magic
              site          sportsbet
              site          tab
              site          crownbet
              site          williamhill

answered Jan 16, 2018 at 0:22

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parsing JSON with VBA JSON parser

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related