Formated JSON

[pit...@gmail.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]

populateJSON is only for use with JSON that have been specifically formatted by the cdataset,serialize() method, as described in 

http://ramblings.mcpher.com/Home/excelquirks/json

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

    sUrl = "http://192.168.0.5:3480/data_request?id=lr_dmData&start=1381078846&stop=1381073153&channel1=50"

    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]

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.