Json to excel from StatsWales website

[Robert de Vries]

Hi,

I'm trying to retrieve school performance data from the StatsWales My Local School website. I've gotten a little way towards this but have hit a brick wall. Please excuse my ignorance as I'm a complete newbie to this stuff.

Basically I've managed to populate an excel sheet with data from the following json: http://mylocalschool.wales.gov.uk/schools.json using the following code (cribbed from your excellent website):

Sub test4()

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

    dSet.populateJSON jo.deSerialize( _

        cb.httpGET( _

            "http://mylocalschool.wales.gov.uk/schools.json" _

            ) _

        ).child("schools"), Range("test4!$a$1")

End Sub

This is the overall list of schools with simple data like school type, school name, and so on. However, what I eventually want is to retrieve more complete data for each school. These are stored in separate jsons identified by a code number (e.g. http://mylocalschool.wales.gov.uk/schools/6664000.json). 

I initially tried to retrieve this data using the same code:

Sub test5()

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

    dSet.populateJSON jo.deSerialize( _

        cb.httpGET( _

            "http://mylocalschool.wales.gov.uk/schools/6664000.json" _

            ) _

        ).child("school"), Range("test5!$a$1")

End Sub

However, this returned the error "school must be a rowise array object".

From reading this forum. I guessed that this was due to 'school' being a higher level array so I tried adding .child("basicDetails") immediately after .child("school"). However, this produced the identical error.

Next I moved on to your suggestion to add an entry to the rest library, and progress that way. However, I can't see that the mylocalschools website has a Rest API that I can use, so I'm a bit stuck.

Again, apologies for my massive ignorance, but hoping someone can help.

[Rob de Vries]

Hi Gustav, thanks for replying. 

So that first JSON (the one that starts ....Abbots lane etc etc.) does actually work for me. Even though (as you say) it's missing the closing square bracket, it actually imports into excel OK. However, the second one (this one) is the one that gives me the 'rowwise' error. I therefore think it must have something to do with the hierarchy, rather than a problem with the final bracket. What do you think?

By the way I have no control over the data itself - this is from a Welsh government website.

I've since also discovered that they don't have a rest API so unfortunately I can't take that route...

Cheers,

Rob

On 27 September 2014 10:58, Gustav Gans <hugf...@gmail.com> wrote:

Hi Robert,

have you checked that your data is syntactically correct?

Looking at this example

"employees":[
    {"firstName":"John", "lastName":"Doe"}, 
    {"firstName":"Anna", "lastName":"Smith"}, 
    {"firstName":"Peter", "lastName":"Jones"}
]

and checking your JSON, I missed the last square bracket at the end to make the array complete. {"schools":[{"name":"Abbots Lane ...........a lot of data......{"name":"Ystruth Primary","estabNumber":"2311","schoolCode":null,"lea":677,"schoolType":1,"lat":51.7669,"lng":-3.15422}],"count":1667} <-- this is the last I can see, without a "]".

Have a look, cheers

--
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/nEP9QrgBVSU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Bruce Mcpherson]

there's many  different dimensions in that data - have you planned what your expected table(s) would look like ? 

btw, as of today - i'm migrating this forum to a google plus community -  https://plus.google.com/communities/103319333735778985706

--
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.

[Familie Hug]

Hi Rob,

I was too quick with my first reply. You are right, the JSON in your first link is Ok. I deleted my post when I discovered it but it seems to have found it's way regardless. Haven't really looked at the second one. I just copy/pasted it into http://jsonfiddle.net/ and syntactically it's ok but I didn't had time to get more into it, sorry.

Cheers

[Rob de Vries]

Thanks both,

I wanted the eventual tables to have the following columns:

SCHOOL NAME | SCHOOL CODE | % FSM | A-LEVEL POINT SCORE

This would be filled in with a single row from each school's individual JSON file. I really couldn't get it to work in excel so eventually switched to Stata and got it to work there (turned out to be much easier). Can share the method if anyone is interested.

Cheers