Import Data into Excel
- Prerequisites
- Excel 2013
- Excel 2016
- Office 365 subscription
- Gather API Details
- Your subscription key.
- The request URL for the API to which you wish to connect, with "csv" selected as the "format" parameter.
- Import Data into Excel
-
Open Excel and access the Data ribbon. Select New Query > From Other Source > From Web.
-
As shown below, choose the Advanced option (1).
Paste the URL copied from the "Try It" page of your chosen API, with "csv" selected as the "format" parameter, into the first URL part (2).
Enter the header name: Gen-Api-Key (3).
Paste your subscription key in the parameter field (4).
Click OK (5).
-
After a brief pause, you should see data from the API in your Excel workbook.
To get started, you must have one of the following versions of Microsoft Excel installed:
In Excel 2016, the Power Query technology is integrated into the Data ribbon under the Get & Transform section.
If you are using Excel 2013, install Microsoft Power Query for Excel. Microsoft Power Query for Excel is an add-in that provides a seamless experience for data discovery and data transformation.
Full instructions for downloading and installing Power Query for Excel 2013 can be found here.
Please note that user interfaces may differ between versions of Excel. This tutorial is based on Excel 2016.
If you haven't already, please visit our Get Started page for instructions on accessing:
Here are the steps to import the data into Excel. In this example, we'll use the Region Oil Storage API.
A preview of the data will open in a dialogue window.
Click Load.