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