Directly import API information into a Spreadsheet

Depending on the program used, it may be possible to query the Data Extraction API URL and import the data directly into your spreadsheet software for analysis and manipulation.

In line with best practice we suggest checking the API URL to ensure it returns data before attempting to use it to import data.

The following example covers the basic steps involved in importing data from an API URL into Microsoft Excel, with a section for the 2010 edition.

Due to the reduced feature set these options may not be available in Excel online.

Import the API data as XML

Enable the Developer tab

Creating an XML Map requires access to the Developer tab in the Excel ribbon.

  1. Launch Excel
  2. Click the File tab, then go to Options > Customize Ribbon
  3. In the "Customize the Ribbon" dropdown choose Main Tabs
  4. Tick the Developer check box then OK to apply

Create XML Map,

  1. Open a blank workbook
  2. Go to the "Developer" tab and click on Source
  3. Click the XML Maps button in the "XML Source" pane on the right
  4. Click Add, enter the full API URL including the API Key and service call (for example, https://SERVER/api/?apikey=yourAPItoken&service=list_failing_checks) in the "File name" then Open
  5. Click OK to create a schema based on the XML source data when prompted by Excel, then OK again in the "XML Maps" dialog
  6. To apply this schema to the workbook, hold "result" at the top of the XML schema in the "XML Source" pane then drag it to the target location in the spreadsheet (for example A1)
  7. Right-click the location to import the API data to (for example A2) from the context menu select XML > XML Import enter the full API URL including the API Key and service call (for example, https://SERVER/api/?apikey=yourAPItoken&service=list_failing_checks) then Open
  8. Excel connects to the API, retrieves the data and populates the workbook

To refresh the information in the workbook go to the "Data" tab, and click on Refresh All.

Microsoft Excel 2010

api_excel_imported

  1. Launch Microsoft Excel and create a blank workbook
  2. Click on the Data tab then select From Web in the Get External Data section.
  3. api_excel_get_data

  4. Enter the API URL in the Address field of the New Web Query dialog and click Go
  5. The contents of the URL are displayed in the dialog’s window and if everything is in order click Import.
  6. This initiates the download of the data into Microsoft Excel (depending upon its configuration you may be required to accept an XML scheme informational message).
  7. In the Import Data dialog decide on the location Where you want to put the data? to complete the process.
  8. You can now manipulate and analyze the data as required from within Microsoft Excel, for example with Pivot Tables.

api_excel_new_query api_excel_import_data