Json to Excel from Rest endpoint

193 views
Skip to first unread message

bryant...@gmail.com

unread,
Aug 3, 2014, 7:50:21 PM8/3/14
to excel-r...@googlegroups.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


sample.json

Bruce Mcpherson

unread,
Aug 4, 2014, 7:05:37 AM8/4/14
to excel-r...@googlegroups.com
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.

Message has been deleted

bryant...@gmail.com

unread,
Aug 5, 2014, 2:25:55 AM8/5/14
to excel-r...@googlegroups.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

unread,
Aug 5, 2014, 4:05:01 AM8/5/14
to excel-r...@googlegroups.com
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

unread,
Aug 19, 2014, 3:43:27 AM8/19/14
to excel-r...@googlegroups.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
    
    
    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!
json b.txt

Bruce Mcpherson

unread,
Aug 19, 2014, 4:10:33 AM8/19/14
to excel-r...@googlegroups.com
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

unread,
Aug 21, 2014, 6:20:27 AM8/21/14
to excel-r...@googlegroups.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

unread,
Aug 21, 2014, 11:22:31 AM8/21/14
to excel-r...@googlegroups.com
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. 






emptycdataset.xlsm
Message has been deleted
Message has been deleted

bryant...@gmail.com

unread,
Aug 25, 2014, 11:32:20 AM8/25/14
to excel-r...@googlegroups.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 

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

unread,
Aug 26, 2014, 4:18:43 AM8/26/14
to excel-r...@googlegroups.com, bryant...@gmail.com
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

unread,
Aug 26, 2014, 4:39:44 AM8/26/14
to excel-r...@googlegroups.com, bryant...@gmail.com
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

unread,
Aug 27, 2014, 4:27:15 AM8/27/14
to excel-r...@googlegroups.com, 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

unread,
Aug 27, 2014, 4:36:52 AM8/27/14
to excel-r...@googlegroups.com, 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.
Reply all
Reply to author
Forward
0 new messages