Formated JSON

52 views
Skip to first unread message

pit...@gmail.com

unread,
Oct 6, 2013, 1:08:27 PM10/6/13
to excel-r...@googlegroups.com
Hi,
I try to convert the JSON with the following code
Sub toto()
Dim dSet As New cDataSet, cb As New cBrowser, jo As New cJobject


    dSet
.populateJSON jo.deSerialize( _
        cb
.httpGET( _
           
"http://192.168.0.5:3480/data_request?id=lr_dmData&start=1381078846&stop=1381073153&channel1=50" _
           
) _
       
).child("series"), Range("Feuil1!$a$1")
End Sub


I can get the data "series" but I'd also get the "data" data and I can not do it by changing the code.

Below the JSON file
{"series":[{"label":"Fibaro Plug - Watts","Id":50,"data":[[1380987292000,5],[1380987301000,13],[1380987441000,13],[1380987565000,7],[1380987693000,8],[1380987817000,4],[1380987940000,7],[1380988064000,4],[1380988187000,12],[1380988310000,8],[1380988433000,6],[1380988558000,8],[1380988683000,12],[1380988806000,11],[1380988931000,7],[1380989054000,7],[1380989177000,7],[1380989301000,11],[1380989425000,6],[1380989549000,13],[1380989673000,7],[1380989797000,7],[1380989921000,9],[1380990045000,10],[1380990172000,10],[1380990298000,10],[1380990422000,13],[1380990545000,7],[1380990668000,4],[1380990803000,5],[1380990943000,12],[1380991068000,7]],"pointsRet":425,"pointsTot":7379,"min":0,"max":316}],"procTime":2.96,"min":1380987292,"max":1381073692}


Could you help me?

Bruce Mcpherson

unread,
Oct 8, 2013, 4:56:48 AM10/8/13
to excel-r...@googlegroups.com
populateJSON is only for use with JSON that have been specifically formatted by the cdataset,serialize() method, as described in 

What you need here is a rest/excel query. The code below should do what you need. I'm assuming that there can be more than one label/id so ive made them data fields rather than headings . if not the case then its a small change.

Also im assuming that your first data value is a unix date- the default behavior for numbers that look like unix dates is to convert them to excel dates- which is what will happen here.
bruce

Private Sub ptp()

    Dim sUrl As String, jor As cJobject, joc As cJobject, job As cJobject, r As Range
    With restQuery("Feuil1", , , , sUrl, , , False, , , True)
        Set r = firstCell(.dSet.headingRow.where)
        
        For Each job In .jObject.child("series").children
            For Each jor In job.child("data").children
                ' assume heading becomes additional data fields
                With r.Offset(jor.childIndex - 1)
                    .Offset(, 0).value = job.child("label").value
                    .Offset(, 1).value = job.child("id").value
                    For Each joc In jor.children
                        'data
                        .Offset(, joc.childIndex - 1 + 2).value = joc.value
                    Next joc
                End With
            Next jor
        Next job
        
    End With
End Sub




--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Philippe PIOT

unread,
Oct 15, 2013, 4:14:05 PM10/15/13
to excel-r...@googlegroups.com
Thank you for the code. It works fine :-)


2013/10/8 Bruce Mcpherson <br...@mcpher.com>

--
You received this message because you are subscribed to a topic in the Google Groups "Excel Liberation" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/excel-ramblings/bsrPonYHDGM/unsubscribe.
To unsubscribe from this group and all of its topics, send an email to excel-ramblin...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages