Difference between revisions of "Refreshable Excel data spreadsheets"
(→Generating the data URLs) |
(→Steps) |
||
| Line 9: | Line 9: | ||
<strong>Example</strong> | <strong>Example</strong> | ||
<i>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv</i> | <i>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv</i> | ||
| − | ====Steps==== | + | ====Basic Steps==== |
# Open a new Excel workbook and choose the Data menu. | # Open a new Excel workbook and choose the Data menu. | ||
# From the ribbon bar choose: New Query > From Other Sources > From Web | # From the ribbon bar choose: New Query > From Other Sources > From Web | ||
Revision as of 02:49, 24 June 2020
Contents
Overview
Refreshable data spreadsheets can be created using Excel's standard data management features with the data being retrieved directly from Fusion Registry's CSV REST web services.
Principles
Excel has built-in functionality for loading data from a range of sources including the Web which allows it to load data directly from Fusion Registry's REST API. The REST API can provide data in a number of different formats including XML and JSON, but CSV is the best choice being easily interpreted and processed by Excel.
A simple refreshable sheet can be created with an Excel Web data query and a Fusion Registry REST API data query URL.
Example
https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv
Basic Steps
- Open a new Excel workbook and choose the Data menu.
- From the ribbon bar choose: New Query > From Other Sources > From Web
- Enter the Fusion Registry REST URL for the data you are interested in
Excel should connect to the URL and download the data into the worksheet which can be saved and used as normal. The data can be refreshed as needed using the Refresh All on the Data ribbon bar. The Show Queries option, also on the Data ribbon bar will open the query sidebar with more information.
Generating the data URLs
Fusion Registry's REST API follows the SDMX REST API syntax, with some additional parameters and CSV extensions which are useful here.
URLs can be crafted by hand if you understand the SDMX REST syntax and know what datasets you need. However, there are a couple of other ways to discover data and generate the URLs automatically.
Fusion Data Browser
- Launch the Fusion Data Browser
- Choose a data set and use the quick filters to select the data of interest.
- Choose Export to CSV, but rather than exporting the file use the Query Syntax function to get the URL
The URl for the query can be copied and pasted into Excel.
Fusion Registry Web Services Query Builder
- From the Fusion Registry user interface, choose Web Service > Data from the left-hand menu bar
- Choose the Dataflow and the sub-cube using the query builder
- Choose the CSV data format
The URL for the query can be copied and pasted into Excel.
