Script Help - Parse JSON VB.net

[skarragallagher]

Hi Guys,

I was wondering if anybody could help me write this script.

Essentially I need to parse a JSON response from a web API

The string looks like this:

 
{"energy_month":161651,"energy_week":137805,"system_id":123456,"source":"microinverters","energy_lifetime":297139,"summary_date":"2013-11-09T00:00:00-08:00","energy_today":15613,"current_power":146,"modules":22}

Using hs.GetURLEx I have been able to go out to the website and return and write this JSON text to a local file which is half the battle.

The next step is to parse this string and assign the values to variables and then assign the variables to virtual devices so I can display them in HSTouch.

This is above my paygrade and I was wondering if I could get some help from some of the scripting experts. I have made some attempts with snippets of VB.net code that I have found on the net however nothing seems to run properly within homeseer.

I really don't care if it is the most elegant solution i.e. using the javascriptserializer (as it seems to not be supported in Homeseer2). I just need to extract the data and assign them to variables so that I can set device status/values

Any help on this is greatly appreciated

Thanks

BTW here is how far I have gotten (I know it is a mess)

 
Sub Main(parm as object)
Dim summaryurl
Dim json = {"energy_month":161651,"energy_week":137805,"system_id":123456,"source":"microinverters","energy_lifetime":297139,"summary_date":"2013-11-09T00:00:00-08:00","energy_today":15613,"current_power":146,"modules":22}
Dim ser As New Web.Script.Serialization.JavaScriptSerializer
Dim summary = ser.Deserialize(Of jsonSummaryArray)(json)
dim elapsedtime as string
'const website = "[URL]https://api.enphaseenergy.com/api/systems/123456/summary?key=ca1415287d8daf1988593ceb41de009b[/URL]"
's = hs.URLAction(website, "GET", "", "")
'hs.writelog("Info",s)
summaryurl = hs.GetURLex("[URL="https://api.enphaseenergy.com/"]https://api.enphaseenergy.com[/URL]", "/api/systems/123456/summary?key=apikey1234567890", "", "443", "TRUE", "FALSE", "c:\enphasesummary.txt")
hs.writelog("Info",summaryurl)
hs.writelog("Info",summary)
End Sub

[mrhappy]

HS is compiled for .net 2.0, the JavaScriptSerializer class (I understand) is for .net 3.5 and later which is why it is not supported. There are some third party JSON libraries which support .net 2.0 out there but I managed to avoid them in the end and do what I needed to with XML.

There is an alternative and that is string manipulation. If you only ever anticipate returning that string and it is not going to change then you might be able to get away with it. One method might be to replace {} with space, split the string based on the comma and then split again by the colon. You could then end up with an array of data and as I say, providing it does not change then you could fetch the data out and write it to some HS devices.

[skarragallagher]

Thanks for the reply

I am pretty sure that it will always be in the format and never change so manually parsing it should work however I have never done this.

Any chance you could give me an example or point me in the right direction?

Much apappreciated,
Thanks

HS is compiled for .net 2.0, the JavaScriptSerializer class (I understand) is for .net 3.5 and later which is why it is not supported. There are some third party JSON libraries which support .net 2.0 out there but I managed to avoid them in the end and do what I needed to with XML.

There is an alternative and that is string manipulation. If you only ever anticipate returning that string and it is not going to change then you might be able to get away with it. One method might be to replace {} with space, split the string based on the comma and then split again by the colon. You could then end up with an array of data and as I say, providing it does not change then you could fetch the data out and write it to some HS devices.

[travisdh]

why not do two arrays, one split by , then parse through for the field that you want, when you find the string you want, then do another split (on another variable) where you split : and take the value you are after,

pseudo-code:

Dim Str as String = "{"energy_month":161651,"energy_week":137805,"system_id":123 456,"source":"microinverters","energy_lifetime":297139,"summ ary_date":"2013-11-09T00:00:00-08:00","energy_today":15613,"current_power":146,"modules":22 }"
Dim SplitStr() as String = Str.Split(",")
Dim SecondStr() as String

For Loop = x to Length of Array
if Instring x = wanted field then
SecondStr = SplitStr(x).Split(":")
Set Device String SplitStr(1).ToString
end if
next


Not full code, but may work, probobly not the most efficient though

[travisdh]

It is not complete (e.g. does not fetch the data), but this may or may not help. Bear in mind i am a beginner programmer myself, so this may be way off but hopefully it helps.

The HS.WriteLog outputs the specific value, however you could easily change that to a hs.SetDeviceString("XX",StringToWorkWith.Remove(0,1)) to store in a device string.

Also have not added any error handling

    Sub Main(ByVal parm As String)
        Dim JSON As String = "{""energy_month"":161651,""energy_week"":137805,""system_id"":123456,""source"":""microinverters"",""energy_lifetime"":297139,""summary_date"":""2013-11-09T00:00:00-08:00"",""energy_today"":15613,""current_power"":146,""modules"":22}"
        Dim SplitStr() As String = JSON.Split(",")
        Dim CurrentString As String
        Dim DPosition As Integer
        Dim StringToWorkWith As String

        For iCount As Integer = 0 To SplitStr.Length - 1
            CurrentString = SplitStr(iCount).ToString

            DPosition = CurrentString.IndexOf(":")

            If InStr(CurrentString, "energy_month") > 0 Then
                StringToWorkWith = CurrentString.Substring(DPosition, CurrentString.Length - DPosition)
                hs.WriteLog("Energy This Month", StringToWorkWith.Remove(0, 1))
            ElseIf InStr(CurrentString, "energy_week") > 0 Then
                StringToWorkWith = CurrentString.Substring(DPosition, CurrentString.Length - DPosition)
                hs.WriteLog("Energy This Week", StringToWorkWith.Remove(0, 1))
            ElseIf InStr(CurrentString, "system_id") > 0 Then
                StringToWorkWith = CurrentString.Substring(DPosition, CurrentString.Length - DPosition)
                hs.WriteLog("SystemID", StringToWorkWith.Remove(0, 1))
            ElseIf InStr(CurrentString, "energy_lifetime") > 0 Then
                StringToWorkWith = CurrentString.Substring(DPosition, CurrentString.Length - DPosition)
                hs.WriteLog("Energy LifeTime", StringToWorkWith.Remove(0, 1))
            ElseIf InStr(CurrentString, "summary_date") > 0 Then
                StringToWorkWith = CurrentString.Substring(DPosition, CurrentString.Length - DPosition)
                hs.WriteLog("Summary Date", StringToWorkWith.Remove(0, 1))
            ElseIf InStr(CurrentString, "current_power") > 0 Then
                StringToWorkWith = CurrentString.Substring(DPosition, CurrentString.Length - DPosition)
                hs.WriteLog("Current Power", StringToWorkWith.Remove(0, 1))
            End If
        Next
    End Sub 

[skarragallagher]

Thanks Mr happy (don't know where your post went) and Travis.

I will give it a shot and post back

[skarragallagher]

Travis,

Thanks so much for throwing this together... This works well with the JSON string as you have it however the return that I get doesn't have the double quotes

Your sample

 Dim JSON As String = "{""energy_month"":161651,""energy_week"":137805,""system_id"":123456,""source"":""microinverters"",""energy_lifetime"":297139,""summary_date"":""2013-11-09T00:00:00-08:00"",""energy_today"":15613,""current_power"":146,""modules"":22}"

What I actually get

Dim JSON As String = "{"energy_month":161651,"energy_week":137805,"system_id":123456,"source":"microinverters","energy_lifetime":297139,"summary_date":"2013-11-09T00:00:00-08:00","energy_today":15613,"current_power":146,"modules":22}"

[skarragallagher]

Ok, so I have it working. It seems it didn't like the quotes when you specified the JSON string however when I pull it from the website it works

 
Sub Main(ByVal parm As String) 
  Dim EnphaseSummary = hs.GetURLex("[URL="https://api.enphaseenergy.com/"]https://api.enphaseenergy.com[/URL]", "/api/systems/123456/summary?key=apikey123456789", "", "443", "TRUE", "FALSE", "c:\enphasesummary.txt")
  Dim JSON As String = EnphaseSummary
        Dim SplitStr() As String = JSON.Split(",") 
        Dim CurrentString As String 
        Dim DPosition As Integer 
        Dim StringToWorkWith As String
    
  hs.writelog("Info",EnphaseSummary)
  hs.writelog("Info",JSON)
  
  For iCount As Integer = 0 To SplitStr.Length - 1 
            CurrentString = SplitStr(iCount).ToString 
            DPosition = CurrentString.IndexOf(":") 
            If InStr(CurrentString, "energy_month") > 0 Then 
                StringToWorkWith = CurrentString.Substring(DPosition, CurrentString.Length - DPosition) 
                hs.WriteLog("Energy This Month", StringToWorkWith.Remove(0, 1))
            ElseIf InStr(CurrentString, "energy_week") > 0 Then 
                StringToWorkWith = CurrentString.Substring(DPosition, CurrentString.Length - DPosition) 
                hs.WriteLog("Energy This Week", StringToWorkWith.Remove(0, 1)) 
            ElseIf InStr(CurrentString, "system_id") > 0 Then 
                StringToWorkWith = CurrentString.Substring(DPosition, CurrentString.Length - DPosition) 
                hs.WriteLog("SystemID", StringToWorkWith.Remove(0, 1)) 
            ElseIf InStr(CurrentString, "energy_lifetime") > 0 Then 
                StringToWorkWith = CurrentString.Substring(DPosition, CurrentString.Length - DPosition) 
                hs.WriteLog("Energy LifeTime", StringToWorkWith.Remove(0, 1)) 
            ElseIf InStr(CurrentString, "summary_date") > 0 Then 
                StringToWorkWith = CurrentString.Substring(DPosition, CurrentString.Length - DPosition) 
                hs.WriteLog("Summary Date", StringToWorkWith.Remove(0, 1)) 
            ElseIf InStr(CurrentString, "current_power") > 0 Then 
                StringToWorkWith = CurrentString.Substring(DPosition, CurrentString.Length - DPosition) 
                hs.WriteLog("Current Power", StringToWorkWith.Remove(0, 1))
   ElseIf InStr(CurrentString, "energy_today") > 0 Then 
                StringToWorkWith = CurrentString.Substring(DPosition, CurrentString.Length - DPosition) 
                hs.WriteLog("Energy Today", StringToWorkWith.Remove(0, 1))
    hs.SetDeviceString("E1",StringToWorkWith.Remove(0,1))
            End If 
        Next 
    End Sub

[travisdh]

The double quotes are just so that the VB.Net script does not escape the string, for example

Dim String as String = "This is a test string, I said Hello"

The above works fine, however if i do it like I said "Hello" below it gets confused as it escapes the string.

Dim String as String = "This is a test string, I said "Hello""

This essentially becomes "This is a test string, I said " and Hello and "" and it will then throw syntax and expected errors and the likes.

If you are storing the response from the HTML direct into the string using a hs.GetURL it should not matter, but if you are manually specifying a string and you want to include a " you need to do double so instead ""

Hope that helps.

On a side note, not that i think it really matters but why are you downloading the HTML to a text file, do you do further processing with it, or do you just want to store the results for later use?

[mrhappy]

This should also do what you are after:

Dim strJ As String = "{""energy_month"":161651,""energy_week"":137805,""system_id"":123456,""source"":""microinverters"",""energy_lifetime"":297139,""summary_date"":""2013-11-09T00:00:00-08:00"",""energy_today"":15613,""current_power"":146,""modules"":22}"

Sub Main(ByVal Parms As Object)

    strJ = strJ.Replace("{", "")
    strJ = strJ.Replace("}", "")

    Dim cArr(8, 1) As String
    Dim strLine() As String = strJ.Split(",")

    For x As Integer = 0 To strLine.GetUpperBound(0)
        Dim nxtSplit() As String = strLine(x).Split(":")
        cArr(x, 0) = nxtSplit(0).Replace(Chr(34), "")
        cArr(x, 1) = nxtSplit(1)
    Next

    For y As Integer = 0 To cArr.GetUpperBound(0)
        hs.writelog("JSON", "Return: " & cArr(y, 0) & " Value: " & cArr(y, 1))
    Next

End Sub

Albeit via a slightly different method of a two dimensional array. You will usually find multiple ways of doing things in VB, just thought I would post it for comparison.

[travisdh]

The 2D array is the best way of doing it, I must admit my knowledge on arrays is very limited so I might have to do some reading up as it could be very useful generally moving past single dimension arrays.

Thanks for posting another alternative, that is one of the things that is great about this community, the fact that there are different ways about achieving a function, and others are happy to post which means we can learn more than we normally would have if inly one person ever posted code examples!

[mrhappy]

One thing to keep in mind is this is only ever suitable for very simple returns, anything more complex either won't work or become a complete nightmare to decode and work out what is where. Another option might be a hashtable but would need to be careful with it needing unique keys.

[skarragallagher]

Thanks for posting this. This is a great learning experience for me as well. I'll play with this one as well to see if I can get it to work properly and I'll let you know if I have any questions

Thanks again guys for your help with this. I was banging my head trying to get JSON deserialized.

The summary data is mainly what I was after. Some of the other data that is available through the API looks something like this

 
 {"interval_length":300,"production":[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2288,2239,2186,2137,2091,2043,1996,1949,1893,1836,1786,1735,1687,1633,1580,1530,1473,1414,1360,1301,1240,1182,1119,1058,999,938,883,828,783,727,657,569,485,432,388,331,270,206,143,62,18,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],"first_interval_end_date":"2013-11-04T00:05:00-08:00"}

I am thinking that having this data in a graph would might be pretty cool. Might be to much work though extracting this and finding some graphing method.

Do you guys have any thoughts on how you would accomplish this and if it is worthit?

I may just explore using the Jon00 HTML to image script to take screen scrapes.

[mrhappy]

Thanks for posting this. This is a great learning experience for me as well. I'll play with this one as well to see if I can get it to work properly and I'll let you know if I have any questions

Thanks again guys for your help with this. I was banging my head trying to get JSON deserialized.

The summary data is mainly what I was after. Some of the other data that is available through the API looks something like this

 
 {"interval_length":300,"production":[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2288,2239,2186,2137,2091,2043,1996,1949,1893,1836,1786,1735,1687,1633,1580,1530,1473,1414,1360,1301,1240,1182,1119,1058,999,938,883,828,783,727,657,569,485,432,388,331,270,206,143,62,18,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],"first_interval_end_date":"2013-11-04T00:05:00-08:00"}

I am thinking that having this data in a graph would might be pretty cool. Might be to much work though extracting this and finding some graphing method.

Do you guys have any thoughts on how you would accomplish this and if it is worthit?

I may just explore using the Jon00 HTML to image script to take screen scrapes.

I've seen your posts about Jon00's utility and I think you have got this to work.

If not and you wanted to learn some programming then Google Image Charts are a start (until they get deprecated in a few years anyway). You can simply pass a specially coded string to it and it will produce you a chart you can use in HSTouch.

In this example things are not exactly straightforward due to the integer array in the middle (this is where a JSON parser would do it fine), you can't split on the comma as in the previous example because that also separates the numbers. If you need an example I can try and throw one together but the image charts are probably not as good looking as the ones your system probably produces.

[skarragallagher]

I've seen your posts about Jon00's utility and I think you have got this to work.

If not and you wanted to learn some programming then Google Image Charts are a start (until they get deprecated in a few years anyway). You can simply pass a specially coded string to it and it will produce you a chart you can use in HSTouch.

In this example things are not exactly straightforward due to the integer array in the middle (this is where a JSON parser would do it fine), you can't split on the comma as in the previous example because that also separates the numbers. If you need an example I can try and throw one together but the image charts are probably not as good looking as the ones your system probably produces.

I am still trying to get Jon00's script working. It works as long as I have the RDP window open but fails when I close it.

I have his HTML to image working unattended however since you have to pass credentials it never gets to the page. Still trying to figure something out