Reputation: 27
how to display my JSON object in good format in VBA Excel
I know display my Parse Json in cell Excel when the Json is "simple" (when it's just string inside) but now i have String, object and array and i'm a little lost.. my json is following:
[
{
"name": null,
"type": null,
"actions": [],
"screen": null,
"container": null,
"sysid": 5,
"uftitem": null
},
{
"name": null,
"type": null,
"actions": [],
"screen": null,
"container": null,
"sysid": 6,
"uftitem": null
},
{
"name": "UTProject5",
"type": "type",
"actions": [
{
"name": "UTProject",
"description": "UTProject",
"pattern": "UTProject",
"isCheck": true,
"sysid": 1,
"uftaction": {
"sysid_uftAction": 2,
"code": "code uft",
"maxTime": 10,
"nbCycle": 20
}
},
{
"name": "UTProject2",
"description": "UTProject",
"pattern": "UTProject",
"isCheck": true,
"sysid": 3,
"uftaction": {
"sysid_uftAction": 4,
"code": "code uft",
"maxTime": 10,
"nbCycle": 20
}
}
],
"screen": {
"name": null,
"type": null,
"actions": [],
"screen": null,
"container": null,
"sysid": 5,
"uftitem": null
},
"container": {
"name": null,
"type": null,
"actions": [],
"screen": null,
"container": null,
"sysid": 6,
"uftitem": null
},
"sysid": 7,
"uftitem": {
"code": "code",
"parentCode": "tooooz",
"sysid": 8
}
},
{
"name": "UTProject6",
"type": "type",
"actions": [
{
"name": "UTProject",
"description": "UTProject",
"pattern": "UTProject",
"isCheck": true,
"sysid": 1,
"uftaction": {
"sysid_uftAction": 2,
"code": "code uft",
"maxTime": 10,
"nbCycle": 20
}
},
{
"name": "UTProject2",
"description": "UTProject",
"pattern": "UTProject",
"isCheck": true,
"sysid": 3,
"uftaction": {
"sysid_uftAction": 4,
"code": "code uft",
"maxTime": 10,
"nbCycle": 20
}
}
],
"screen": {
"name": null,
"type": null,
"actions": [],
"screen": null,
"container": null,
"sysid": 5,
"uftitem": null
},
"container": {
"name": null,
"type": null,
"actions": [],
"screen": null,
"container": null,
"sysid": 6,
"uftitem": null
},
"sysid": 9,
"uftitem": {
"code": null,
"parentCode": null,
"sysid": 10
}
}
]
I would like to access to what I want and display it in cell, but I don't know access in array and object.
Thanks every one!
Upvotes: 1
Views: 13286
Answers (4)
Reputation: 11
Here's how I do it:
Public Function indentJsonV2(ByVal jsonIn As String) As String
Dim positionIndent As Double: positionIndent = -1
Dim counter As Double: counter = 1
Dim theLength As Double: theLength = Len(jsonIn)
Dim JsonOut As String: JsonOut = vbNullString
Dim i As Double: i = 0
Dim theChar As String: theChar = vbNullString
Dim twoChars As String: twoChars = vbNullString
While counter <> theLength + 1
theChar = Mid(jsonIn, counter, 1)
twoChars = Mid(jsonIn, counter, 2)
If theChar = "{" Or theChar = "[" Then
JsonOut = JsonOut & theChar
JsonOut = JsonOut & Chr(13) & Chr(10)
positionIndent = positionIndent + 1
For i = 0 To positionIndent
JsonOut = JsonOut & Space(5)
Next i
ElseIf theChar = "}" Or theChar = "]" Then
JsonOut = JsonOut & Chr(13) & Chr(10)
positionIndent = positionIndent - 1
For i = 0 To positionIndent
JsonOut = JsonOut + Space(5)
Next i
JsonOut = JsonOut + theChar
'For i = 0 To positionIndent
' JsonOut = JsonOut + Space(5)
'Next i
ElseIf twoChars = "]," Or twoChars = "}," Or twoChars = ",""" Then
JsonOut = JsonOut & theChar
JsonOut = JsonOut & Chr(13) & Chr(10)
For i = 0 To positionIndent
JsonOut = JsonOut + Space(5)
Next i
Else
JsonOut = JsonOut + theChar
End If
counter = counter + 1
Wend
indentJsonV2 = JsonOut
End Function
Upvotes: 1
Reputation: 41
I create this simple function to formatt the Json string, if someone needed. The code is very simple.
Public Function indentJson(ByVal jsonIn As String) As String
Dim posizioneIndent As Integer
Dim contatore As Integer
Dim lungheza As Integer
Dim JsonOut As String
Dim i As Integer
Dim carattere As String
JsonOut = ""
lunghezza = Len(jsonIn)
contatore = 1
posizioneIndent = -1
carattere = ""
While contatore <> lunghezza + 1
carattere = Mid(jsonIn, contatore, 1)
If carattere = "{" Or carattere = "[" Then
JsonOut = JsonOut & carattere
JsonOut = JsonOut & Chr(13) & Chr(10)
posizioneIndent = posizioneIndent + 1
For i = 0 To posizioneIndent
JsonOut = JsonOut & Space(5)
Next i
ElseIf carattere = "}" Or carattere = "]" Then
JsonOut = JsonOut & Chr(13) & Chr(10)
posizioneIndent = posizioneIndent - 1
For i = 0 To posizioneIndent
JsonOut = JsonOut + Space(5)
Next i
JsonOut = JsonOut + carattere
'For i = 0 To posizioneIndent
' JsonOut = JsonOut + Space(5)
'Next i
ElseIf carattere = "," Then
JsonOut = JsonOut & carattere
JsonOut = JsonOut & Chr(13) & Chr(10)
For i = 0 To posizioneIndent
JsonOut = JsonOut + Space(5)
Next i
Else
JsonOut = JsonOut + carattere
End If
contatore = contatore + 1
Wend
indentJson = JsonOut
End Function
This is the result , in my case i put it in a textbox, If someone have a advice to improve, please tell me.
{
"routingResponse":{
"currentTimeUTC":"2022-10-16-09.16.48.012243+02:00",
"executionMessage":{
"code":0,
"severity":"INFO",
"codeDesc":"",
"message":""
},
"arrivalTerminal":"031",
"arrivalDepot":"031",
"deliveryZone":"20",
"consigneeZIPCode":"95024",
"consigneeCity":"ACIREALE",
"consigneeProvinceAbbreviation":"CT"
}
}
Upvotes: 1
Reputation: 12612
Take a look at the below example. Import JSON.bas module into the VBA project for JSON processing.
Option Explicit
Sub Test()
Dim sJSONString As String
Dim vJSON
Dim sState As String
Dim aData()
Dim aHeader()
Dim vResult
' Read JSON sample from file C:\Test\sample.json
sJSONString = ReadTextFile("C:\Test\sample.json", 0)
' Parse JSON sample
JSON.Parse sJSONString, vJSON, sState
If sState = "Error" Then
MsgBox "Invalid JSON"
End
End If
' Convert raw JSON to 2d array and output to worksheet #1
JSON.ToArray vJSON, aData, aHeader
With Sheets(1)
.Cells.Delete
.Cells.WrapText = False
OutputArray .Cells(1, 1), aHeader
Output2DArray .Cells(2, 1), aData
.Columns.AutoFit
End With
' Flatten JSON
JSON.Flatten vJSON, vResult
' Convert flattened JSON to 2d array and output to worksheet #2
JSON.ToArray vResult, aData, aHeader
With Sheets(2)
.Cells.Delete
.Cells.WrapText = False
OutputArray .Cells(1, 1), aHeader
Output2DArray .Cells(2, 1), aData
.Columns.AutoFit
End With
MsgBox "Completed"
End Sub
Sub OutputArray(oDstRng As Range, aCells As Variant)
With oDstRng
.Parent.Select
With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
.NumberFormat = "@"
.Value = aCells
End With
End With
End Sub
Sub Output2DArray(oDstRng As Range, aCells As Variant)
With oDstRng
.Parent.Select
With .Resize( _
UBound(aCells, 1) - LBound(aCells, 1) + 1, _
UBound(aCells, 2) - LBound(aCells, 2) + 1)
.NumberFormat = "@"
.Value = aCells
End With
End With
End Sub
Function ReadTextFile(sPath As String, lFormat As Long) As String
' lFormat -2 - System default, -1 - Unicode, 0 - ASCII
With CreateObject("Scripting.FileSystemObject").OpenTextFile(sPath, 1, False, lFormat)
ReadTextFile = ""
If Not .AtEndOfStream Then ReadTextFile = .ReadAll
.Close
End With
End Function
BTW, the similar approach applied in other answers.
Upvotes: 0
Reputation: 84465
General:
You can empty the whole thing with the following code which makes use of JSON converter:
Note:
I am reading JSON string in from sheet and storing in object via JSONConverter. The initial object is a collection. I loop that collection and every nested level within using TypeName function* to determine what object(s) are stored at each level. I then use Select Case to handle these objects appropriately.
More efficient would be to design a re-usuable class to handle this. I have seen some other questions on SO where this is done.
* VarType is actually more robust
Example JSON
Example code output to immediate window:
You can choose how you write to the cell by replacing the Debug.Print statements with assignments to sheet ranges.
VBA:
Option Explicit
Public Sub GetInfoFromSheet()
Dim jsonStr As String
jsonStr = [A1] '<== read in from sheet
Dim json As Object
Set json = JsonConverter.ParseJson(jsonStr)
Dim i As Long, j As Long, key As Variant
For i = 1 To json.Count
For Each key In json(i).keys
Select Case key
Case "name", "type"
Debug.Print key & " " & json(i)(key)
Case Else
Select Case TypeName(json(i)(key))
Case "Dictionary"
Dim key2 As Variant
For Each key2 In json(i)(key)
Select Case TypeName(json(i)(key)(key2))
Case "Collection"
Dim k As Long
For k = 1 To json(i)(key)(key2).Count
Debug.Print key & " " & key2 & " " & json(i)(key)(key2)(k)
Next k
Case Else
Debug.Print key & " " & key2 & " " & json(i)(key)(key2)
End Select
Next key2
Case "Collection"
For j = 1 To json(i)(key).Count '<== "actions"
Dim key3 As Variant
For Each key3 In json(i)(key)(j).keys
Select Case TypeName(json(i)(key)(j)(key3))
Case "String", "Boolean", "Double"
Debug.Print key & " " & key3 & " " & json(i)(key)(j)(key3)
Case Else
Dim key4 As Variant
For Each key4 In json(i)(key)(j)(key3).keys
Debug.Print key & " " & key3 & " " & key4 & " " & json(i)(key)(j)(key3)(key4)
Next key4
End Select
Next key3
Next j
Case Else
Debug.Print key & " " & json(i)(key)
End Select
End Select
Next key
Next i
End Sub
tl;dr; Tutorial spot:
So the above might have been a bit full on as it gets everything without lots of explanation. Below, we take a more detailed look at how to target some of that JSON and "talk" through the associated VBA.
For this you can use an online JSON parser to view the structure of your JSON more clearly. I posted your JSON string into Json Parser Online, and then examined the structure in the String/parseJS eval; left hand side section. There are other tools available.
The initial thing to note is the beginning "[". The very first one you can see below.
This denotes the Collection object, which is your JSON string when converted with JsonConverter. Everything else is nested between this opening "[" bracket and its closing counterpart at the very end.
The next thing to note is that this is a collection of dictionaries, so everything that forms a "group" within, is a dictionary.
See the "{" denoting the start of the dictionary?
The dictionary has keys of "name","type","actions" etc.
An initial observation is that lots of this info is empty ie. null.
We can ignore these with an IsNull test (I choose to do this based on "name" field):
If Not IsNull(json(i)("name"))
We can also see that "actions", in dictionaries where the "name" is not null, contains another collection of dictionaries. You see we have the "[" followed by the "{" as described before.
We can see that each inner dictionary has keys of "name", "description" etc. We can also see that their values are of different datatypes.
Observing "actions" in the JSON structure, you can see these are (using an example dictionary):
- String
"name":"UTProject" - String
"description":"UTProject" - String "
pattern":"UTProject" - Boolean
"isCheck":true - Double
"sysid":1 - Dictionary
"uftaction" 'keys of ==> "sysid_uftAction":2,"code":"code uft","maxTime":10,"nbCycle":20
So, we can use Select Case to handle the data type by testing with TypeName
For the primitive boolean, string and double data types we can simply print them by using the key e.g.
json(i)("actions")(j)("isCheck")
That will be a boolean result of True or False. i and j being indices of current position in loops of both outer and inner collections.
For the dictionary "uftaction", we can loop over its keys:
For Each key2 In json(i)("actions")(j)(key).keys
Debug.Print "actions " & key & " " & key2 & " " & json(i)("actions")(j)(key)(key2)
Next key2
You could of course access with the name of the key without the loop over the keys at the end, e.g.:
json(i)("actions")(j)(key)("maxTime")
And throughout you could access specific positions via index rather than looping such that i and j would be replaced directly with a numeric value. And key, key2 etc could be replaced by the actual literal string for any given key.
Hopefully that has given you some more insight.
VBA:
Option Explicit
Public Sub GetInfoFromJSON()
Dim jsonStr As String
jsonStr = [A1] '<== read in from sheet
Dim json As Object, i As Long
Set json = JsonConverter.ParseJson(jsonStr) '<==This is a collection verified by Debug.Print TypeName(JSON)
For i = 1 To json.Count
If Not IsNull(json(i)("name")) Then
'ignore the null names which have sys id only populated
Debug.Print "name" & " " & json(i)("name")
Debug.Print "type" & " " & json(i)("type")
Dim j As Long
For j = 1 To json(i)("actions").Count 'actions are a collection of dictionaries
Dim key As Variant
For Each key In json(i)("actions")(j).keys 'dictionary
'observing actions in the JSON structure you can see there are:
' String "name":"UTProject"
'String "description":"UTProject",
'String "pattern":"UTProject",
'Boolean "isCheck":true,
'Double "sysid":1,
'Dictionary "uftaction" '==> "sysid_uftAction":2,"code":"code uft","maxTime":10,"nbCycle":20
'So we can use Select Case to handle the data type by testing with TypeName
Select Case TypeName(json(i)("actions")(j)(key))
Case "String", "Boolean", "Double" '<==good to go nothing extra needed
Debug.Print "actions " & key & " " & json(i)("actions")(j)(key)
Case Else ' we are dealing with uftaction which we know is a dictionary
Dim key2 As Variant
For Each key2 In json(i)("actions")(j)(key).keys '<==We know now we are looping the uftaction dictionary which has keys "sysid_uftAction","code","maxTime","nbCycle"
Debug.Print "actions " & key & " " & key2 & " " & json(i)("actions")(j)(key)(key2)
Next key2
End Select
Next key
Next j
End If
Next i
End Sub
Upvotes: 6
Related Questions
- Parsing JSON in Excel VBA
- Parsing JSON to Excel using VBA
- How to generate multiple json in vertical column in Excel VBA?
- Trying to Convert Excel Format into JSON
- Need to convert an Excel Sheet to its Json format in VBA
- Print value from JSON object to excel sheet (Parse JSON to VBA)
- Output array to excel with VBA
- Parsing JSON data into Excel sheet
- How to import JSON to Excel correctly
- In Excel VBA on Windows, how to get stringified JSON respresentation instead of "[object Object]" for parsed JSON variables?