Parse JSON to excel worksheet by JSONConverter-CodePudding

I created a VBA code that enables me to get a string from a website. The string looks like that (json format)

[
   {
      "CD":151,
      "nID":111,
      "sNM":"PNAME1",
      "GDR":"MA",
      "RGN":"MM",
      "reID":1,
      "status":"RSB",
      "NTY":"EG",
      "CLNUM":1,
      "CLNM":"THIR",
      "YER":2022,
      "SCHOD":1718,
      "STID":2,
      "THNM":"BRYYY",
      "SCHNO":"HTBAN",
      "rCD":6,
      "schooL_TYPE":1,
      "SGT":1,
      "CLCD":3,
      "NG1":1,
      "NG2":2,
      "YCOD":null,
      "general":10,
      "special":1,
      "naT_ID":1,
      "GDR_ID":1,
      "sGCOD":8,
      "sTTY":1,
      "obNM":"NTF",
      "obID":0,
      "STYN":"NTHMY",
      "PSNUM":null
   },
   {
      "CD":153,
      "nID":222,
      "sNM":"ALIIKK",
      "GDR":"MA",
      "RGN":"MM",
      "reID":1,
      "status":"RSB",
      "NTY":"EG",
      "CLNUM":1,
      "CLNM":"THIR",
      "YER":2022,
      "SCHOD":1718,
      "STID":2,
      "THNM":"SYYYYY",
      "SCHNO":"HTBAN",
      "rCD":6,
      "schooL_TYPE":1,
      "SGT":1,
      "CLCD":3,
      "NG1":1,
      "NG2":2,
      "YCOD":null,
      "general":10,
      "special":1,
      "naT_ID":1,
      "GDR_ID":1,
      "sGCOD":8,
      "sTTY":1,
      "obNM":"NTF",
      "obID":0,
      "STYN":"NTHMY",
      "PSNUM":null
   }
]

I am trying to store the data into a collection and arrays This is my try but I am confused about the line of setting the collection

Dim json As Object, col As Collection
    Set json = JSONConverter.ParseJson(sResp)
    Set col = json("CD")
    Dim a, i As Long
        ReDim a(1 To col.Count, 1 To 1)
    For i = 1 To col.Count
        'a(i, 1) = col.Item(i)("")
    Next i

The string should have two persons' data. How can I pares all the data included into two rows?

I got an error Dictionary Key Not Found at this line of JSONConverter module

CodePudding user response：

Option Explicit

Sub demo()

    Dim sResp
    sResp = "[{'CD':151,'nID':111,'sNM':'PNAME1','GDR':'MA','RGN':'MM','reID':1,'status':'RSB','NTY':'EG','CLNUM':1,'CLNM':'THIR','YER':2022,'SCHOD':1718,'STID':2,'THNM':'BRYYY','SCHNO':'HTBAN','rCD':6,'schooL_TYPE':1,'SGT':1,'CLCD':3,'NG1':1,'NG2':2,'YCOD':null,'general':10,'special':1,'naT_ID':1,'GDR_ID':1,'sGCOD':8,'sTTY':1,'obNM':'NTF','obID':0,'STYN':'NTHMY','PSNUM':null},{'CD':153,'nID':222,'sNM':'ALIIKK','GDR':'MA','RGN':'MM','reID':1,'status':'RSB','NTY':'EG','CLNUM':1,'CLNM':'THIR','YER':2022,'SCHOD':1718,'STID':2,'THNM':'SYYYYY','SCHNO':'HTBAN','rCD':6,'schooL_TYPE':1,'SGT':1,'CLCD':3,'NG1':1,'NG2':2,'YCOD':null,'general':10,'special':1,'naT_ID':1,'GDR_ID':1,'sGCOD':8,'sTTY':1,'obNM':'NTF','obID':0,'STYN':'NTHMY','PSNUM':null}]"
    sResp = Replace(sResp, "'", Chr(34))
   
    Dim data As Object, rec As Object, key, fields, arData
    Dim n As Long, m As Long, i As Long, j As Long

    Set data = JsonConverter.ParseJson(sResp)
   
    fields = data(1).Keys
    m = UBound(fields)   1
    n = data.Count ' records
    
    ' fill array
    ReDim arData(1 To n, 1 To m)
    For i = 1 To n
         For j = 1 To m
            arData(i, j) = data(i)(fields(j - 1))
         Next
    Next

    ' dump array
    With Sheet1
       .Range("A1").Resize(1, m) = fields ' header
       .Range("A2").Resize(n, m) = arData
    End With

End Sub

CodePudding user response：

I was wrong. The converter also accepts square brackets as the first element, a collection. The suggested additional brackets would not work either, because they create an invalid JSON.

Now CDP1802 has already answered, but I also looked for a correct solution because of my wrong statement (I deleted it so no one else would see it as the truth):

Sub TestJSON()
  
  Dim sResp As String
  Dim json As Collection
  Dim col As Dictionary
  Dim key As Variant
  Dim dicts As Long
  Dim row As String
  
  sResp = "[{""CD"":151,""nID"":111,""sNM"":""PNAME1"",""GDR"":""MA"",""RGN"":""MM"",""reID"":1,""status"":""RSB"",""NTY"":""EG"",""CLNUM"":1,""CLNM"":""THIR"",""YER"":2022,""SCHOD"":1718,""STID"":2,""THNM"":""BRYYY"",""SCHNO"":""HTBAN"",""rCD"":6,""schooL_TYPE"":1,""SGT"":1,""CLCD"":3,""NG1"":1,""NG2"":2,""YCOD"":null,""general"":10,""special"":1,""naT_ID"":1,""GDR_ID"":1,""sGCOD"":8,""sTTY"":1,""obNM"":""NTF"",""obID"":0,""STYN"":""NTHMY"",""PSNUM"":null},{""CD"":153,""nID"":222,""sNM"":""ALIIKK"",""GDR"":""MA"",""RGN"":""MM"",""reID"":1,""status"":""RSB"",""NTY"":""EG"",""CLNUM"":1,""CLNM"":""THIR"",""YER"":2022,""SCHOD"":1718,""STID"":2,""THNM"":""SYYYYY"",""SCHNO"":""HTBAN"",""rCD"":6,""schooL_TYPE"":1,""SGT"":1,""CLCD"":3,""NG1"":1,""NG2"":2,""YCOD"":null,""general"":10,""special"":1,""naT_ID"":1,""GDR_ID"":1,""sGCOD"":8,""sTTY"":1,""obNM"":""NTF"",""obID"":0,""STYN"":""NTHMY"",""PSNUM"":null}]"
  Set json = JsonConverter.ParseJson(sResp)
  
  For dicts = 1 To json.Count
    Set col = json(dicts)
    For Each key In col.Keys()
      'Debug.Print key & ":" & col(key) & ", "
      row = row & col(key) & Chr(9)
    Next key
    Debug.Print row
    row = ""
  Next dicts
End Sub