Excel JSON VBA Parsing - Determining if an array is empty

Question

I am trying to parse a JSON response. I cannot use the VBA-JSON library. I need to check to see if a nested array is empty or null. I keep getting this error:

Example JSON:

{
"gardenAssets": [],
"gardenAssetsAlertCount": 0,
"gardenAssetsCount": 0,
"gardenAssetsErrorCount": 0,
"locationsSummaries": [
    {
        "locations": [
            {
                "auditOrder": "102",
                "code": "POT 102",
                "name": "POT 102",
                "type": "ProcessingLocation",
                "gardenAssets": [
                    {
                        "annotation": "Pallets",
                        "broker": {
                            "code": "TMTO",
                            "isOwner": null,
                            "name": null
                        },
                        "datetimeOfArrivalIngarden": 1622754283.937,
                        "id": "crusaf",
                        "isSealable": true,
                        "load": null,
                        "mastergardenCode": null,
                        "name": null,
                        "owner": {
                            "code": "SUN",
                            "isOwner": null,
                            "name": null
                        }
                    }
                ]
            },
            {
                "auditOrder": "103",
                "code": "POT 103",
                "description": "POT 103",
                "id": "110746",
                "name": "POT 103",
                "type": "ProcessingLocation",
                "gardenAssets": []
            },
            {
                "auditOrder": "104",
                "code": "POT 104",
                "name": "POT 104",
                "gardenAssets": [
                    {
                        "annotation": "Soil",
                        "broker": {
                            "code": "OTHR",
                            "isOwner": null,
                            "name": null
                        },
                        "datetimeOfArrivalIngarden": 1622571699.767,
                        "id": "arserana",
                        "isSealable": true,
                        "load": null,
                        "mastergardenCode": null,
                        "name": null,
                        "owner": {
                            "code": "WTR",
                            "isOwner": null,
                            "name": null
                        }
                    }
                ]
            },
            {
                "auditOrder": "111",
                "code": "POT 111",
                "name": "POT 111",
                "type": "ProcessingLocation",
                "gardenAssets": [
                    {
                        "annotation": null,
                        "broker": {
                            "code": "CLD",
                            "isOwner": null,
                            "name": null
                        },
                        "datetimeOfArrivalIngarden": 1622746446.932,
                        "id": "Bacrea",
                        "isSealable": true,
                        "load": null,
                        "mastergardenCode": null,
                        "name": null,
                        "owner": {
                            "code": "ICE",
                            "isOwner": null,
                            "name": null
                        },
                        "status": "EMPTY",
                        "type": "JUNK",
                        "unavailable": false,
                        "visitId": "1003768526"
                    }
                ]
            }
        ],
        "logingarden": true,
        "mastergardenCodes": [],
        "gardenCode": "FUN5"
    }
],
"offsitegardens": [],
"gardenAssetsInTransit": []}

Code:

Option Explicit
Dim S as Object, k, Ks as Object
Set S = CreateObject("ScriptControl")
S.Language = "JScript" 
S.addcode "function k(a){var k=[];for(var b in a){k.push('[\'' + b + '\']');}return k;}"

S.Eval ("var J = " & http.ResponseText)
S.Eval ("var L = J.locationsSummaries['0'].locations")
Set Ks = S.Eval("J.locationsSummaries['0'].locations")
For Each K In Ks
    If Not IsNull(S.Eval(K.gardenAssets)) = True Then
        Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1) = "Assets"
    End If

Next K

I need to pull different information out of the JSON depending on if there are any gardenAssets. But I can't seem to check to see if the array is empty or not.

You can use the JsonConverter by Tim Hall. It will create a data structure of nested dictionaries and collections. To check if there is an entry for "gardenAssets" you only need to check if the collection count has more than 0 entries. A dictionary is generated by every {} and a collection by [] github.com/VBA-tools/VBA-JSON — Zwenn
– Zwenn, Commented Jun 4, 2021 at 14:52
Because? It's a .bas file you can import as an own module to your VBA project. No need for admin rights. Or is it something lika a homework and you must use the ScriptControl object? — Zwenn
– Zwenn, Commented Jun 4, 2021 at 15:23
IIRC eval via scriptcontrol is a security risk. Please don't implement projects in this way. — QHarr
– QHarr, Commented Jun 4, 2021 at 18:47

CDP1802 · Accepted Answer · 2021-06-04 17:11:01Z

1

You can use the length property in JScript.

    Dim S As Object
    Dim n As Integer, i As Integer, r As Long
    r = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Set S = CreateObject("ScriptControl")
    With S
        .Language = "JScript"
        .eval "var J = " & http.ResponseText
        .eval "var A = J.locationsSummaries['0'].locations"
        For n = 1 To S.eval("A.length")
            .eval "var L = A[" & n - 1 & "]"
            For i = 1 To .eval("L.gardenAssets.length")
               Sheet1.Cells(r, 1) = .eval("L.code")
               Sheet1.Cells(r, 2) = .eval("L.gardenAssets[" & i - 1 & "].id")
               r = r + 1
            Next
        Next
    End With

answered Jun 4, 2021 at 17:11

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

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

Comments

barrowc · Accepted Answer · 2021-06-04 15:25:34Z

0

The example JSON isn't valid. The last member of an object or the last element of an array shouldn't have a comma after it. So where you have:

  "broker": {
    "code": "TMTO",
    "isOwner": null,
    "name": null,
  }

There shouldn't be a comma after "name": null - there are multiple other errors like this in the example JSON.

You can use an online JSON validator (like this one) to detect these errors. You would ideally want to fix the system that is generating this invalid JSON rather than trying to correct the issues yourself during processing

answered Jun 4, 2021 at 15:25

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

1 Comment

zimide82 Over a year ago

Fixed the JSON. It should be all good now. This is an example that I have modified. Sorry for the errors.

Collectives™ on Stack Overflow

Excel JSON VBA Parsing - Determining if an array is empty

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related