How to use Data Explorer for Excel to extract data from Statistics Denmark

StatBank Denmark contains detailed statistical information on the Danish society, and they are exposing a lot of interesting statistics about Denmark.

Unfortunately they do not expose these data in oData feeds, public web services but only via an interface where you manually export the tables in excel, csv, html or similar formats. But then I noticed the other day that you have the possibility to access the your saved tables as XML and as Microsoft “Data Explorer” Preview for Excel handles this perfectly – I thought that I would give it and try so let me take you through the process.

Step 1 – Log on to Stat bank Denmark

To select the data you need to logon to StatBank Data – http://www.statbank.dk/statbank5a/default.asp?w=1920 and you should create a use account so you can extract large tables and most important – you can save your queries and that is important.

Now you have access to all kind of interesting things about Denmark.

Step 2 – Create a query

Let try and analyze the “Live births by age of mother and sex of child”.

I choose all the elements in the different available filters and click Show table. The result is this table

And if I choose to export this to CSV or Excel I get a table that is not very useful for further analysis.

Step 3 Save the query

At the bottom of the page you can select to save your table for future use and also specify whether the timeperiods should rolling, fixed or only latest updated periods.

Give the report a name and click “Add as new saved table”.

Then you will get a id for the table in the next picture and this ID is important for that will be used to generate the xml file later on.

You can also find the ID in the link info at the status bar.

Step 4 – Test the XML feed

Now can get the XML via calling

http://www.statistikbanken.dk/xml/167000

Step 5 Get the data into Excel

So choose Import XML from the Data Explorer tab and enter the http://www.statistikbanken.dk/xml/167000

This takes 10 – 15 seconds for the file to be loaded and data explorer to appear.

Then you can click the Data – in the navigator and see the table data.

Now we can format and rename the columns.

So final result

Click Done and the data will be returned to Excel

Step 6 – Load the data to Power Pivot

In Query Setting Task pane – Select not to Load to worksheet and click load to data model

And now I can refresh data directly from PowerPivot

Step 7 Analyze the data

And now that you have the data in Power Pivot you can start to analyze and visualize the data using Power View or just the standard Excel charting tool

Or

Step 8 – More data

And now you can import other relevant data from Statbank to see what might have an impact on the birth rates.

You try

You can find the example file here – and the cool thing is that you can use the same query that I have created in my user profile on Statbank.

Links

Download Data Explorer

Stat bank Denmark

The xml data