Download the content of a JSON-stat dataset as an Excel file

[Xavier Badosa]

I developed a tool to download as an Excel file the response of a JSON-stat dataset (GET) API query.

https://jsonstat.com/excel/

X.

[Jan Bruusgaard]

This is very nice.

I belive that the Excel file would be more user friendly if the data tab had labels instead of IDs. It could also be an option.

Kind regards

Jan

[Xavier Badosa]

Thanks for your feedback, Jan!

I actually pondered such option. In fact, in https://jsonstat.com/api/ (CSV & JSON) it is possible to choose between ids or labels. 

But I discarded it in https://jsonstat.com/excel/ because, in Excel, it is possible to merge (join) different tables in such a way that the user can independently choose whether to use ids or labels, or even apply this choice selectively to certain dimensions while excluding others. 

That's why, instead, I decided to benefit from the fact that Excel books are a way to pack different data structures (thanks to sheets). Including a sheet for id/label translation for each dimension in the Excel book allows the user to merge the information as she likes while avoiding repeating (usually long) labels attached to each value in the data tab.

I'm aware that this makes it a little more complicated for a non-advanced user that wants to end up with a pivotable table using labels, particularly because, in Excel, you cannot merge (join) several tables in a single step (you need a step for each merge). In this first version, though, I decided efficiency, flexibility and generality (and a simpler interface with less options) was more important than non-advanced user friendliness (I assumed -probably incorrectly- that a user that knows how to deal with pivotable tables know how to deal with table merges). So probably I'll need at some point to add the functionality you request!

BTW, I added support for POST-based APIs (like Statistics Norway's):

https://jsonstat.com/excel/

X.

[Xavier Badosa]

>  I'm aware that this makes it a little more complicated for a non-advanced user that wants to end up with a pivotable table using labels, particularly because, in Excel, you cannot merge (join) several tables in a single step (you need a step for each merge). 

I'm exploring a different route, let me know what you think.

The Excel structure returned by https://jsonstat.com/excel/ is very general. I've developed an M script (Power Query) that will add the necessary joins to the Excel file automatically solely based on the info on the sheets. I'm planning to include such a code as a downloadable file or inside the same Excel file. There's a catch, though. To work correctly cell ranges must be converted to tables (Ctrl-T, data includes headers) and named as the tabs. I've developed a Visual Basic script to also automate this step. I'll need to develop a video to explain this because, even though it's conceptually simple, it requires a deep familiarity with VB and Power Query editor options.

PS: I'm planning to develop a similar approach to convert CSV-stat files (https://github.com/jsonstat/csv) to Excel/PowerBI.

X.

[Xavier Badosa]

I've updated https://jsonstat.com/excel/ to include instructions and Visual Basic code to automatically convert ranges to Excel tables (so they can be seen as queries by Power Query) and Power Query code to join the different queries.

X.