JSON Converter

[lorento]

Hello people with a greater mind than my own.

I'm in need of serious help.

I have an excel data structure:

Path_a1		Field_1	1
Path_a1		Field_2	2
Path_a2[1]		Field_1	3
Path_a2[1]		Field_2	4
Path_a2[2]		Field_1	5
Path_a2[2]		Field_2	6
Path_a2[2]	Path_b2[1]	Field_1	7
Path_a2[2]	Path_b2[1]	Field_2	8
Path_a2[2]	Path_b2[2]	Field_1	9
Path_a2[2]	Path_b2[2]	Field_2	10

That I'm trying to turn into a JSON body structure that looks like this:

{
"Path_a1": {
"Field_1": 1,
"Field_2": 2
},
"Path_a2": [
{
"Field_1": 3,
"Field_2": 4
},
{
"Field_1": 5,
"Field_2": 6,
"Path_b2": [
{
"Field_1": 7,
"Field_2": 8
},
{
"Field_1": 9,
"Field_2": 10
}
]
}
]
}

I'm aware of the JSON convertor module and I have some experience with Dictionaries but it's the finding, and adding additional array elements when I come to them that's causing me an issue.

At the moment I'm looping through the row(s) then column(s) and extracting the Paths and Array sizes taking in "Path_a2[1]" and getting "Path_a2" , 1 but then when it comes to adding "Path_a2" , 2 everything falls over and I cannot for the life of me figure out how to do this. I've tried several different approaches to coding this which I won't put here because simply they don't work and anything I have will likely be pointless.

If anyone can help I'd be eternally grateful!

 

[lorento]

Hello people with a greater mind than my own.

I'm in need of serious help.

I have an excel data structure:

Path_a1		Field_1	1
Path_a1		Field_2	2
Path_a2[1]		Field_1	3
Path_a2[1]		Field_2	4
Path_a2[2]		Field_1	5
Path_a2[2]		Field_2	6
Path_a2[2]	Path_b2[1]	Field_1	7
Path_a2[2]	Path_b2[1]	Field_2	8
Path_a2[2]	Path_b2[2]	Field_1	9
Path_a2[2]	Path_b2[2]	Field_2	10

That I'm trying to turn into a JSON body structure that looks like this:

{
"Path_a1": {
"Field_1": 1,
"Field_2": 2
},
"Path_a2": [
{
"Field_1": 3,
"Field_2": 4
},
{
"Field_1": 5,
"Field_2": 6,
"Path_b2": [
{
"Field_1": 7,
"Field_2": 8
},
{
"Field_1": 9,
"Field_2": 10
}
]
}
]
}

I'm aware of the JSON convertor module and I have some experience with Dictionaries but it's the finding, and adding additional array elements when I come to them that's causing me an issue.

At the moment I'm looping through the row(s) then column(s) and extracting the Paths and Array sizes taking in "Path_a2[1]" and getting "Path_a2" , 1 but then when it comes to adding "Path_a2" , 2 everything falls over and I cannot for the life of me figure out how to do this. I've tried several different approaches to coding this which I won't put here because simply they don't work and anything I have will likely be pointless.

If anyone can help I'd be eternally grateful!

Just in case here's what I have already anyway:

Option Explicit

Public jInput As Dictionary

Public Function Test_v6()

Dim iRow As Long
Dim iRow_Max As Long

Dim iCol As Long
Dim iCol_Max As Long

Dim sPath(4) As String
Dim sPath_Array_Ind(4) As Long
Dim sPath_Len As Long
Dim sField As String
Dim iArray_Pos(4) As Long
Dim rJSON_Input As Range

'Reset the Previous Input Dictionary
Set jInput = New Dictionary
Set jInput = CreateObject("Scripting.Dictionary")

'Using the Named Range "JSON_Input_Structure which covers Columns A1 to E200 at the moment
Set rJSON_Input = Application.Names("JSON_Input_Structure").RefersToRange

'Find out how big the named range is
iRow_Max = rJSON_Input.Rows.Count
iCol_Max = 4

'Going through each Row read the path(s), array position(s), and fields using the format:
' By Column:
' A B C D E
' Path 1[Array] Path 2[Array] Path 3[Array] Path4[Array] Field
For iRow = 1 To iRow_Max

'Set the Path Length to 0
sPath_Len = 0

'Now move through the Columns
For iCol = 1 To iCol_Max

'Set the values to blank
sPath(iCol) = ""
sPath_Array_Ind(iCol) = 0

'If we find something
If rJSON_Input.Cells(iRow, iCol).Value <> "" Then

'Save the Path
sPath(iCol) = rJSON_Input.Cells(iRow, iCol).Value

'Now see if it has an array indicator and if so extract and clean
If InStr(1, sPath(iCol), "[") > 0 Then
sPath_Array_Ind(iCol) = CLng(Mid(sPath(iCol), InStr(1, sPath(iCol), "[") + 1, 1))

sPath(iCol) = Mid(sPath(iCol), 1, InStr(1, sPath(iCol), "[") - 1)
End If

'Add one to the Path Length
sPath_Len = sPath_Len + 1
End If
Next iCol

'Clear out any previously held Field Name
sField = ""

'Now if the we have a set of Paths
If sPath_Len > 0 Then

'Get the field
sField = rJSON_Input.Cells(iRow, 5).Value

'Call a function to update the Input Dictionary
Call Update_Input(sPath, sPath_Array_Ind, sPath_Len, sField)
End If

Next iRow

End Function

Public Function Update_Input(sPath() As String, sPath_Array_Ind() As Long, sPath_Len As Long, sField As String)

Debug.Print "Found: " & sPath_Len & " - " & sPath(1) & "(" & sPath_Array_Ind(1) & "), " & sPath(2) & "(" & sPath_Array_Ind(2) & "), " & sPath(3) & "(" & sPath_Array_Ind(3) & "), " & sPath(4) & "(" & sPath_Array_Ind(4) & "): " & sField

End Function