CODEWITHSUNDEEP
jsonexcelvba
Tim
Tim

Reputation: 206

How to split json into single json strings using VBA?

I would like to split a json string into individual json.

I was looking around and I found a VBA code https://github.com/VBA-tools/VBA-JSON to split json into objects which I want, but I would like to split one big json into individual single json.

Basically I would like to split this:

    [
        {
            "Status" : "COLLECTION.WAY",
            "Status_DateTime" : "2016-08-15 15:13:11",
            "Status_Lat" : "52.4784879192565",
            "Status_Long" : "-1.88211519765142",
            "idJobsIn" : 159,
            "idStatusout" : "29078448-ded3-92b7-cedb-49e87472e912",
            "lastUpdated" : "2016-08-15 15:13:13"
        },
        {
            "Status" : "COLLECTION.ARRIVED",
            "Status_DateTime" : "2016-08-15 15:13:16",
            "Status_Lat" : "52.4784420889455",
            "Status_Long" : "-1.88212609975582",
            "idJobsIn" : 154,
            "idStatusout" : "81fbadbb-d347-2908-50cf-cf4fcc064996",
            "lastUpdated" : "2016-08-15 15:13:17"
        },
        {
            "Status" : "COLLECTION.INSPECTION",
            "Status_DateTime" : "2016-08-15 15:13:18",
            "Status_Lat" : null,
            "Status_Long" : null,
            "idJobsIn" : 154,
            "idStatusout" : "74762c57-8cd4-0ef5-b121-aa5204d0c0fb",
            "lastUpdated" : "2016-08-15 15:13:19"
        },
        {
            "Status" : "DELIVERY.WAY",
            "Status_DateTime" : "2016-08-15 15:16:31",
            "Status_Lat" : "52.4784953811743",
            "Status_Long" : "-1.88214593074766",
            "idJobsIn" : 158,
            "idStatusout" : "8e693455-6009-521a-4e0a-b61daa2c3c5d",
            "lastUpdated" : "2016-08-15 15:16:32"
        },
        {
            "Status" : "DELIVERY.ARRIVED",
            "Status_DateTime" : "2016-08-15 15:16:31",
            "Status_Lat" : "52.4784856667105",
            "Status_Long" : "-1.88227903409833",
            "idJobsIn" : 157,
            "idStatusout" : "d4c1ffb0-b1f0-5b8a-2db7-400e71ef07ae",
            "lastUpdated" : "2016-08-15 15:16:32"
        },
        {
            "Status" : "DELIVERY.OVER",
            "Status_DateTime" : "2016-08-15 15:16:32",
            "Status_Lat" : null,
            "Status_Long" : null,
            "idJobsIn" : 156,
            "idStatusout" : "76861f40-7bfc-acd5-7765-fe5cb592993e",
            "lastUpdated" : "2016-08-15 15:16:33"
        },
        {
            "Status" : "COLLECTION.WAY",
            "Status_DateTime" : "2016-08-15 15:36:57",
            "Status_Lat" : "52.4784306486522",
            "Status_Long" : "-1.88221678930354",
            "idJobsIn" : 155,
            "idStatusout" : "781eae25-31b4-03c7-95d7-96c2c3aa2279",
            "lastUpdated" : "2016-08-15 15:36:56"
        },
        {
            "Status" : "COLLECTION.ARRIVED",
            "Status_DateTime" : "2016-08-15 15:36:57",
            "Status_Lat" : "52.4784010100062",
            "Status_Long" : "-1.88211220916548",
            "idJobsIn" : 154,
            "idStatusout" : "a73944e8-9cab-77fd-2758-40fa3a9450ae",
            "lastUpdated" : "2016-08-15 15:36:56"
        },
        {
            "Status" : "COLLECTION.INSPECTION",
            "Status_DateTime" : "2016-08-15 15:36:59",
            "Status_Lat" : null,
            "Status_Long" : null,
            "idJobsIn" : 153,
            "idStatusout" : "385ebb40-dd20-22ae-b536-6dd02f5d2c49",
            "lastUpdated" : "2016-08-15 15:36:58"
        }
    ]

to this if I can:

    [
        {
            "Status" : "COLLECTION.WAY",
            "Status_DateTime" : "2016-08-15 15:13:11",
            "Status_Lat" : "52.4784879192565",
            "Status_Long" : "-1.88211519765142",
            "idJobsIn" : 154,
            "idStatusout" : "29078448-ded3-92b7-cedb-49e87472e912",
            "lastUpdated" : "2016-08-15 15:13:13"
        }
    ]

Upvotes: 1

Views: 2052

Answers (2)

Sabrina Dondic
Sabrina Dondic

Reputation: 11

John Coleman´s solution is not quite correct. Inside some JSON structure there might be additional curly bracket. Splitting hard at "}" might screw up the complete structure. Instead you have to count opening { and closing } until your counte reaches 0, something like this (using IsArrayAllocated from modArraySupport)

Function SplitJson(ByVal jsons As String) As Variant

    Dim a() As Variant
    Dim i As Long

    Dim pos As Long
    Dim posStart As Long
    Dim openBr As Long

    openBr = 0

    For pos = 1 To Len(jsons)
        If Mid(jsons, pos, 1) = "{" Then
            openBr = openBr + 1
            If openBr = 1 Then posStart = pos ' first "{"!!
        End If
        If Mid(jsons, pos, 1) = "}" Then
            openBr = openBr - 1
        End If
        If posStart > 0 And openBr = 0 Then
            If IsArrayAllocated(a) Then
                ReDim Preserve a(LBound(a) + 1)
            Else
                ReDim a(0)
            End If
            a(UBound(a)) = Mid(jsons, posStart, pos - posStart + 1)
            posStart = 0
        End If
    Next pos

    SplitJson = a

End Function

Upvotes: 1

John Coleman
John Coleman

Reputation: 51998

Maybe something like this:

Function SplitJson(ByVal jsons As String) As Variant
    Dim A As Variant
    Dim i As Long

    jsons = Trim(jsons)
    i = InStr(1, jsons, "{") 'index of first json
    jsons = Mid(jsons, i)
    jsons = Mid(jsons, 1, Len(jsons) - 1) 'strip off final ]

    A = Split(jsons, "},")

    For i = 0 To UBound(A)
        A(i) = Trim(A(i)) & "}"
    Next i

    SplitJson = A
End Function

To test it, I took your data and stored it in a text file and then ran:

Sub test()
    Dim s As String, A As Variant
    Dim FSO As Variant, f As Variant

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set f = FSO.OpenTextFile("C:/Programs/testjson.txt", 1)
    s = f.ReadAll()
    f.Close
    A = SplitJson(s)
    Debug.Print UBound(A)
    Debug.Print A(0)

End Sub

Output:

8 
{
        "Status" : "COLLECTION.WAY",
        "Status_DateTime" : "2016-08-15 15:13:11",
        "Status_Lat" : "52.4784879192565",
        "Status_Long" : "-1.88211519765142",
        "idJobsIn" : 159,
        "idStatusout" : "29078448-ded3-92b7-cedb-49e87472e912",
        "lastUpdated" : "2016-08-15 15:13:13"
}

Upvotes: 0

Related Questions