Import Data into Excel
DMI’s open data is retrieved in JSON format (”JavaScript Object Notation”).
You are able to import the JSON response of an API query directly into newer versions of Excel by following the guide below.
From Excel data can easily be saved in .csv format.
If you want to convert JSON directly into a .csv file several "JSON to CSV converters" can be found online.
How to query the DMI Open Data API directly from Excel
Open Excel and click the Data tab
Click New Query > From Other Sources > From Web
Enter your DMI open data query in the URL text field and press OK
(Remember to include your api-key in the query)
Click Into Table (on the convert tab)
Right click the value of "features" (List)
Click Drill Down
Click To Table
Click OK
Click the button with left and right arrows
Uncheck "use original column name as prefix"
Click OK
Click the button with left and right arrows at the top of the "geometry" column.
Click OK
Click the button with left and right arrows at the top of the "coordinates" column.
Click Extract Values
(You can also choose Expand to New Rows. This will give you two identical rows for each observation. One row for latitude coordinates and one for longitude).
Select a delimiter from list for concatenating the coordinates.
Press OK
(In this example we chose semicolon as the delimiter)
Click the button with left and right arrows at the top of the "properties" column
Press OK in the the window that appears
Click Close and Load to finalise the import process.
