Extracting datapoints from a JSON string

[ErikJan]

I have a JSON string with many measurements and I want to extract all X, Y datapairs (timestamp, newvalue). I have a JSON parser as part of my code and that works, but I'm still failing to fill an X,Y array.

(of course if I skip the JSON parser and do this all tailored myself, I can make it work... but I wanted to used the power of the pre-built JSON code that I can call).

Code: Select all

Debug.Print json("data")("newValue")

gives the first Y value (345), but how to I also get the second (338)?
Same for the matching "timestamp"

Code: Select all

Debug.Print json("timeline")

(the example below has two data points, so I'm looking to get out an X1, Y1 and X2, Y2)

Code: Select all

[
	{
		"data": {
			"id": 257,
			"newValue": 345,
			"oldValue": 338,
			"property": "value"
		},
		"id": 930809,
		"objects": [
			{
				"id": 257,
				"type": "device"
			}
		],
		"sourceId": 0,
		"sourceType": "system",
		"timestamp": 1718632500,
		"type": "DevicePropertyUpdatedEvent"
	},
	{
		"data": {
			"id": 257,
			"newValue": 338,
			"oldValue": 344,
			"property": "value"
		},
		"id": 930801,
		"objects": [
			{
				"id": 257,
				"type": "device"
			}
		],
		"sourceId": 0,
		"sourceType": "system",
		"timestamp": 1718632440,
		"type": "DevicePropertyUpdatedEvent"
	}
	]

[SpeakEasy]

Actually, you can parse (simpleish) JSON without needing a 3rd party control., e.g

Code: Select all

Option Explicit

 Public Sub Main()
    Dim JsonFile As String
    JsonFile = "d:\downloads\deleteme\json1.txt" ' contains the example json that was provided
    ' Note that VBA reserved words used as Key names in JSON will cause problems for the decode, so replace any we are aware of
    ' with a safe word. In this case we will replace 'data' with 'mydata' before sending the json string off to be parsed
    JSONDecode Replace(CreateObject("Scripting.FileSystemObject").OpenTextFile(JsonFile, 1).ReadAll, "data", "mydata")
 End Sub

Public Sub JSONDecode(JSONString)
    Dim jsondata As Object
    Dim jsonitem As Object
    With CreateObject("ScriptControl")
        .Language = "JScript"
        Set jsondata = .Eval("(" + JSONString + ")")
        For Each jsonitem In jsondata
            Debug.Print "Timestamp:" & jsonitem.timestamp
            Debug.Print "New value:" & jsonitem.mydata.newValue
        Next
    End With
End Sub

[ErikJan]

Oh, that would be cool. But I get "Class not registered" and that same if I register "Microsoft Script Control 1.0".
I see more of that if I google, all related to 64 bit Excel (which is what I have...)

[SpeakEasy]

Ah. yes, sadly MS in their wisdom chose not to port the script control to 64bit. And I keep forgetting that, as I continue to use 32bit Office (I don't have any requirements that result in the benfits of 64bit Office outweighing the drawbacks).

There are (somewhat convoluted) workarounds (e.g using the iActiveScript OLE interface) but this will be more effort than whatever 3rd part json parser object you are currently using. So stick with that.

Unfortunately there are a LOT of these 3rd party JSON libraries, and they all work slightly differently from each other. So right now it is difficult to say why your particular library is not getting the results you want. Tell us which one you are using ...

[ErikJan]

Yeah, I hear you on 32 vs 64 bit and you're not wrong of course. I considered staying at 32 bit but decided that as eventually everything would go to 64 bit anyway, I'd better 'give in' and deal with it now...

My VBA JSON code is this:

' VBA-JSON v2.3.1
' (c) Tim Hall - https://github.com/VBA-tools/VBA-JSON

[SpeakEasy]

Ok, yep. So that's working with dictionaries ... so

Code: Select all

Public Sub Example()
    Dim jsncnv As New JsonConverter
    Dim jsondata As Object
    Dim json As Object
    Dim JsonFile As String
    Dim jsonitem As Object
     
    JsonFile = "d:\downloads\deleteme\json1.txt" ' contains the example json that was provided

    Set jsondata = jsncnv.ParseJson(CreateObject("Scripting.FileSystemObject").OpenTextFile(JsonFile, 1).ReadAll)
    
    For Each jsonitem In jsondata
        Debug.Print "Timestamp:" & jsonitem("timestamp")
        Debug.Print "New value:" & jsonitem("data")("newValue")
    Next
End Sub

[ErikJan]

Works... Sooo simple. If you knew the things I tried to get this working... I feel a bit stupid seeing how simple this is. Thank you for helping me out here. Very much appreciated :-)

Now up to the next problem (maybe in a new thread if I get stuck again)

[p45cal]

Power Query (included in Excel from Excel 2013) can produce this straight out of the box:

2024-06-19_200906.jpg

I'll go into more detail if you think this could be useful.

[ErikJan]

Thanks, I had seen that before yes. The JSON file is retrieved via a call to a URL and there's a conversion needed for the timestamp plus I needed some more functionality, so I decided to try and do all of this in VBA.

[p45cal]

Thanks, I had seen that before yes. The JSON file is retrieved via a call to a URL and there's a conversion needed for the timestamp plus I needed some more functionality, so I decided to try and do all of this in VBA.

Conversion of the time stamp should be easy (what's the process?) and if it's a call to a url that should be easy too (can you supply the url?). What other functionality is needed? Shouldn't be difficult; that's what Power Query is for.

[ErikJan]

Apologies for the delay... just returned from a short holiday.

The time stamp conversion is easy (in VBA or Excel), it's the number of seconds since 01/01/1970. As that's UTC, I also need to correct for Local time.
(but as say, I have that covered in VBA, NP).
The URL is local (in my home system) so I can't share even if I wanted to (to provide some more background: I have some sensors in my house which are logged on a Smart Home / Home Automation system. I'm trying to read the logged datapoints and store them in Excel)