Parsing JSON

[peskywinnets]

I'm trying to parse a bit of JSON like this...

...I'm interested in orderIDs (50 of them) ......the path to this field is showing at the top of the jpg.

I've clearly got my code set wrong, as after 6 loops it exits (if you look at the top of the jpg there are 6 objects), so I need to set the Json parser up to do a level down.

here's what I've done...

Set json = JsonConverter.ParseJson(XMLHTTP.responseText)

For Each OrderId In json
orderID = json("orders")(i)("orderId")
next OrderId

It works, but only loops 6 times.

Surely something embarrassingly basic (I'm not that well versed in nested JSON)

top tips welcome

 

[arnelgp]

isladog has one (free/commercial).
there is also json.bas here
how to display my JSON object in good format in VBA Excel - Stack Overflow

 

[isladogs]

Hers a link to both my commercial JSON app and the free cut down version with fewer features
JSON Analyse & Transform - Mendip Data Systems

 

[gemma-the-husky]

I used some code I found here to get started. I found it similar in concept to xml, to be honest.

' VBA JSON Parser
'https://medium.com/swlh/excel-vba-parse-json-easily-c2213f4d8e7a

 

[theDBguy]

I used some code I found here to get started. I found it similar in concept to xml, to be honest.

' VBA JSON Parser
'https://medium.com/swlh/excel-vba-parse-json-easily-c2213f4d8e7a

I've used VBA-JSON from Github before and found it slow. I switched to using Script Control because it's fast, but I'm aware of the security risk. I will give this one a try. Thanks!

 

[peskywinnets]

I used some code I found here to get started. I found it similar in concept to xml, to be honest.

' VBA JSON Parser
'https://medium.com/swlh/excel-vba-parse-json-easily-c2213f4d8e7a

Thanks...yes, that approach is very XML-esque (which I find painful ...JSON was meant to make things less ugly to pick the bones out of!)

I've managed to workaround, but it's a real kludge (which I excel in!), but until I'm more JSON Savvy it'll get me away...

Basically I search for a unique string in the data (to establish how many occurences)...so in my case legacyOrderId is unique. This results in a Count, I then loop using that count number. Ugly, but works (there must be a more graceful way, if I could figure out the intricacies of using JSON.bas)...

OrderCount = 0
'find how many occurences of the string  "legacyOrderId" in the data being analysed
countTxt = """legacyOrderId"""
myTxt = XMLHTTP.responseText
OrderCount = (Len(myTxt) - Len(Replace(myTxt, countTxt, ""))) / Len(countTxt)
If OrderCount = 0 Then GoTo Finished

' in this case, the above yields a count of 50 (i.e 50 orders), so now I just use a loop to extract the OrderIDs from the JSON...
i = 1
Do While i <= OrderCount
orderID = json("orders")(i)("orderId")
debug.print  orderID '  <<yay
i = i + 1
Loop

Finished:

 

[gemma-the-husky]

For the moment all I did was took a portion of an order file, and exported it both XML and JSON (just creating and printing strings - nothing automatic), until I was able to parse the JSON version with that code. Nothing too complex just now - Once I knew I could do it in principle I left it at that. XML is easy because of the MSXML library.

 

[Uncle Gizmo]

I used some code I found here to get started. I found it similar in concept to xml, to be honest.

Thanks for that link Dave. I've added it to my ever growing Json notes file. One day I hope to do a blog about it!

 

[jdraw]

Pesky,

Here's a link to some recent vba substring if it's of any value. Colin's json stuff is good json - vba material.
I don't use/do much json, but appreciate the other references provided.

 

[isladogs]

If the contents of the JSON file aren't confidential, feel free to email it to me using the link below and I'll look into it.
If you want to do that, let me know which fields you want to return ...or do you just want all the orderID values?

 

[peskywinnets]

If the contents of the JSON file aren't confidential, feel free to email it to me using the link below and I'll look into it.
If you want to do that, let me know which fields you want to return ...or do you just want all the orderID values?

I appreciate that Colin...but the JSON data is full of confidential information....yes, it's just the OrderID info I need.

Ebay have two APIs ...legacy API which uses XML (& contains Legacy OrderIDs - which I've always used to date) & their new REST API which uses JSON (and contains Ebay's latest OrderID format) ..since my legacy API pulls in all the info I need, the only bit I haven't got is the new Ebay OrderID format (which is useful to me) - so, I only need to extract the orderID (the path as per the screenshot in my opneing post) & pipe them into a field in access.

As I say., I have now deployed a working kludge, but it be nice to know how to approach this in a more elegant manner!

 

[isladogs]

I can't offer a solution based on a screenshot. I would need to test the file ...or an anonymised equivalent.
I would have thought you could easily anonymise the data or indeed you could try my app. Its up to you.

 

[peskywinnets]

I've replicated the JSON structure with test data....

{
"href":"https://api.ebay.com",
"total":"30",
"next":"https://api.ebay.com/next",
"limit":"50",
"offset":"0",
"orders":{
    "0":{"OrderID":"Order1"},
    "1":{"OrderID":"Order2"},
    "2":{"OrderID":"Order3"}

  }
}

Which when dropped into a JSON 'beautifier', yields this...

It's Order1, Order2, Order3 text that I need

 

[gemma-the-husky]

that code that i posted had this sub

Function ListPaths(dic) As String
    Dim s$, v
    For Each v In dic
        s = s & v & " --> " & dic(v) & vbLf
    Next
    Debug.Print s
    ListPaths = s
End Function

Without checking further, all you would need to do is an instr on the v & " --> " & dic(v) to find the bits you want, I imagine you could go back a step and use the dic(v) bit directly. I would look further, but I have to rush out now.