Json to Excel from Rest endpoint

[bryant...@gmail.com]

Hi,

I am a novice with VBA and JSON  and I have a project as part of my internship that will have to query a rest endpoint containing a collection of dictionaries in a JSON format. I’ve read as much as I can to try to understand how to parse that JSON into Excel, but I am getting confused and I am having trouble making things work.

I think the “Rest to Excel Library” and “Excel JSON conversion” pages seem to describe the process that relates to my task. I’ve downloaded “cDataSet.xlsm and read this other forum post started by Bill called “JSON to Excel with existing JSON file,” but I am still having trouble.

I’ve attached a sample JSON with the vba code bellow:

Sub connecttoDB()

   Dim dSet As New cDataSet, cb As New cBrowser, jo As New cJobject

dSet.populateJSON  _jo.deSerialize(cb.httpGET("bellow.companywebsite.com:5000/food/components")).child("components"), Range("sheet1! $a$1")

End Sub

When I run this I get a “Run-time error ‘-2147012890 (80072ee6)’: System error: -2147012890”

Can this be a result of how the data in the JSON file is formatted?

I would love to learn more about vba and rest. Thank you very much for any insight!

Bryant

[Bruce Mcpherson]

Hi 

this is indeed JSON formatted but it is not an array of key value pairs, as expected for javascript objects, so it will again need some tweaking. 

The error you are getting is because the Url or port you are using is not a valid one, or is perhaps blocked. so its not getting as far as downloading anything. Can you enter directly into the browser and get some results?

Before I show you how to reorganize the JSON, you should first plan what the output should look like. There are various fields you could show here. why dont you layout what your sheet would look like first

bruce

--
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/d/optout.

[bryant...@gmail.com]

Hi Bruce, 

I was able to connect to the url properly and what is printed in the browser is the same as the sample.json. I also did cb.httpGET("bellow.companywebsite.com:5000/food/components") and this part didn't return any errors, so I think it may be fine? It wasn't until I passed it into deserialize that I get the runtime error.

I will get back to you soon with a layout as I am not too sure how it is suppose to look yet.

Thanks Bruce,

Bryant

[Bruce Mcpherson]

interesting .. the error you mentioned is normally generated by a url fetch .

in any case, try this separately and let me have the output

Sub connecttoDB()

Dim dSet As New cDataSet, cb As New cBrowser, jo As New cJobject, s as string

  s= cb.httpGET("bellow.companywebsite.com:5000/food/components"))

debug.print 'fetched', s

set jo = JSONparse(s)

debug.print 'parsed', jo.stringify

End Sub

--

[bryant...@gmail.com]

Hi Bruce,

Sorry about the late reply. I didn't know the jobject needed to be set as a string, thanks for that.

Here is what I now have: 

Sub ConnecttoDB()

    Dim dSet As New cDataSet, cb As New cBrowser, jo As New cJobject, data As String

    

    data = cb.httpGET("http://bellow.website:5000/food/components

    

    dSet.populateJSON jo.deSerialize(data).child("components"), Range("sheet1!$a$1")

End Sub

It appears to spit everything out correctly and populate the designated sheet, but when I tried it on another json file it only prints to the excel sheet one line.  I've dont have the json file on hand, but I've attached a text document showing the two branches of "assemblies" - "attributes" and "children" 

Can you give show me the best way to correct this or what I may be doing wrong? 

I'm learning a lot from the resources you have provided. Thanks a bunch!

[Bruce Mcpherson]

actually the jobject needs to be set to an object, not a string. JSONParse() converts a string to an object, hence

set jo = JSONparse(s)

In your JSON file you have two competing arrays, with different properties. By default, each element of an array is a row, and each property is a column, but here you have 2 kinds of rows and 2 kinds of columns. can you show how your data should be represented in an excel table?

--

[bryant...@gmail.com]

Oh I see.

I have attached a copy of the excel sheet "prop_wb_e.xlsm" that has the layout of where the data needs to go. I was trying out the operation of the JSON file as an example to see what I can learn from it, but I just got the real JSON files now which I've attached above. This is the big picture that I need help on. 

The excel spreadsheet will be blank except for the names of the existing tabs. I need to populate the sheets with the "reference components"  - the things that are currently in dark blue and provide the layout of the sheet. The names that currently fill the cells correspond to the "field_programmatic_name" in the  reference component (refcomp) JSON. Then the two attached json files containing "component" data (attributes) will each populate a row and needs to conform to the layout. 

So far I've been using many copies of your script just to display the data in different excel sheets because each component*.json comes from a unique URL mainly website.com:5/DB/components/"component id"  But I don't know how to put them all together.

So I have to iterate through a list of components with their corresponding ID (found in componentlist.json) and then populate the spreadsheet following the same format found in attached workbook, but I don't know how to write the function that would pull all this together .

Can you please show me how to populate the JSON according to the format that is currently laid out in the excel workbook? Once you teach me that I think I should be able to iterate over the next 700+ components found in the list and similar workbooks.

Thank you so much once again for your insight Bruce!

Bryant

[Bruce Mcpherson]

Hey Bryant - that's way too much - I don't have the time to play around with all that formatting. However I will show you how to get your data into sheets. For this test I've read the json from a file. instead of that , you would change it to pick up the data directly from your url.. you just need to make a couple of small changes to this code for that. file is attached - this is in the module called comp

Option Explicit

Private Function getComponentList()

    Dim comps As String, job As cJobject, ds As cDataSet, dr As cDataRow

    ' get from file .. could be picked up directly from api

    comps = readFromFile("componentlist.json")

    

    ' this populates the componentlist tab

    With restQuery("componentlist", , , , "http://foo", "components", , , , , True, comps)

        Set ds = makeSheetFromJob(.datajObject, "componentlist")

        .tearDown

    End With

    

    

    ' now use that to generate further queries

    Dim append As Boolean

    append = False

    For Each dr In ds.rows

        ' the id is used to construct the query

        ' since i dont have the url, i'll just read a couple from files for now

        If (dr.cell("id").value = 205 Or dr.cell("id").value = 60) Then

            comps = readFromFile("component" & dr.toString("id") & ".json")

            With restQuery("data", , dr.toString("id"), , _

                "website.com:5/DB/components/", "attributes", _

                , , , , , comps, , , append)

                .tearDown

            End With

            append = True

        End If

    Next dr

    

    ds.tearDown

End Function

you can take care of the formatting etc yourself with standard excel tools. 

[bryant...@gmail.com]

Hi Bruce,

Thank you for doing so much. I'm sorry I didn't mean to overwhelm you, I thought the detail may help but I now think it just confused things. 

I do have a few more questions because I don't fully understand everything I read from your posts.. But let me first show you a little more clearly what I'm trying to achieve 

I have component 203 here:  https://www.dropbox.com/s/jitd7pewq8anoba/203.json?dl=0 

and I'm trying to get the field values into the corresponding columns displayed in this screenshot: https://www.dropbox.com/s/9jsqw9zjrqyn41w/Screen%20Shot%202014-08-24%20at%205.07.52%20PM.png?dl=0

I've read your stuff on "Adding to the Rest Library", cRest and the restQuery and I understand some of it, but can you show me how to implement the filling of corresponding data fields for this case please?  I don't know how to filter out what I don't need and how to access the metadata or stuff outside of the array (but it is in the json.) even though I've been trying to dissect your code above and them many examples you provided.

Thank you again for all your help!

Bryant

[bruce]

I had a look at the file you sent me. Is there a reason you commented out this code in restlibrary - this is what actually executes the query .. and why you are getting an error when you run it.

    Else

        Set cr = cr.execute(sQuery, sFix, complain)

        If cr Is Nothing Then

            If complain Then MsgBox ("failed to execute " & sQuery)

        Else

            Set restQuery = cr

        End If

    End If

secondly, that url you are using with dropbox .. it will not provide a json file - instead its some kind of prettified version. I think the correct parameter with dropbox would be ?dl=1

[bruce]

I recommend you do this in two steps - firstly get all the data into an excel as in the example I provided, then map the data from the rows into the various formatting you need. It'll be easier to debug that way, and you'll have a clearer view of your input data.

On Monday, 25 August 2014 16:32:20 UTC+1, bryant...@gmail.com wrote:

[bryant...@gmail.com]

Thanks Bruce, 

I will keep trying this out. I'm not sure what the right question to ask is ..  but what do the commas and spaces found between the () of restQuery mean? 

Best regards,

Bryant

[bryant...@gmail.com]

I think the issue that I am experiencing is that the url of the json on the company's server is just displaying the contents of the json but it isn't downloading the json into the restQuery function, so that is why I tried  commenting out code that the debugger said had issue - I realize this may not be the proper way to debug things.