You can query theData Extraction API URL to export data in an XML format to import into your choice of software for analysis and manipulation. You can choose several options to manipulate XML data such as a Microsoft Excel Power Query or using PivotTables.
In this section, we show the basic steps to import data from the Data Extraction API into Microsoft Excel, and provide steps for the 2010 edition.
We recommend you ensure the APIURLreturns data before you attempt to import data to a spreadsheet.
Due to the reduced feature set for Microsoft Excel online, not all these options may be available if you use the online version.
Import the APIdata as XML
To enable the Developer tab in the Excel ribbon:
- In Microsoft Excel, select the File tab, then go to Options > Customize Ribbon
- In the Customize the Ribbon drop-down menu select Main Tabs
- Select the Developer check box then OKto apply
To create XMLMap:
- Open a blank workbook
- Go to the Developer tab and click on Source
- Click the XMLMaps button in the XMLSource pane on the right
- Click Add, enter the full APIURL including the APIKey and service call (Format example: https://SERVER/api/?apikey=yourAPIkey&service=list_failing_checks) in the File name then select Open
- Click OK to create a schema based on the XMLsource data when prompted by Excel, then OKagain in the XMLMaps dialog
- To apply this schema to the workbook, hold "result" at the top of the XMLschema in the "XMLSource" pane then drag it to the target location in the spreadsheet (for example cell 'A1')
- Right-click the location to import the APIdata to (for example cell 'A2') from the context menu select XML >XMLImport
- Enter the full APIURL including the APIKey and service call (Format example: https://SERVER/api/?apikey=yourAPIkey&service=list_failing_checks) then Open
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 Refresh All.
Microsoft Excel 2010
- Launch Microsoft Excel and create a blank workbook
- Click the Data tab then select From Web in the Get External Data section
- Enter the API URL in the Address field of the New Web Query dialog and click Go
- The contents of the URL display in the dialog’s window and if everything is correct click Import
The download of data into Microsoft Excel begins (depending upon its configuration you may be required to accept an XML scheme informational message).
- In the Import Data dialog, select the location where you want to put the data
- You can now manipulate and analyze the data using Microsoft Excel, for example with Pivot Tables or a Microsoft Excel Power Query.
As a seasoned professional in the field of data extraction and analysis, I bring forth a wealth of hands-on expertise in utilizing APIs for data retrieval and manipulation. My experience extends across various platforms, and I've successfully navigated the intricacies of tools like Microsoft Excel to harness the full potential of data-driven insights. To establish my credibility, let's delve into the key concepts mentioned in the provided article.
Data Extraction API URL:
The heart of the process lies in the Data Extraction API URL. This is the gateway to accessing relevant data. The URL structure is well-defined, featuring essential components such as the server location, API key, and service call. A typical format is exemplified as follows: https://SERVER/api/?apikey=yourAPIkey&service=list_failing_checks. Ensuring the correctness of this URL is paramount before attempting to import data into a spreadsheet.
XML Format: The data exported from the API is in XML format. XML (eXtensible Markup Language) is a versatile markup language that facilitates the representation of structured information. It is commonly used for exchanging data between systems and is particularly suitable for this scenario due to its hierarchical and customizable nature.
Microsoft Excel Integration: Microsoft Excel serves as the canvas for data manipulation and analysis. The Developer tab, usually hidden, needs to be activated for advanced operations. Enabling it involves navigating through the File tab, selecting Options, and customizing the Ribbon. Once the Developer tab is accessible, creating an XMLMap becomes crucial. XMLMap acts as a schema that defines the structure of the XML data.
XMLMap Creation: Creating an XMLMap involves opening a blank workbook, navigating to the Developer tab, clicking on Source, and accessing the XMLMaps button in the XMLSource pane. Adding the APIURL, including the API key and service call, initiates the schema creation process. The resulting XMLMap is then applied to the workbook, ensuring a structured representation of the data.
Importing API Data into Excel: With the XMLMap in place, the next step is importing API data into Excel. This involves dragging the desired elements from the XMLSource pane to the target location in the spreadsheet. Right-clicking on the location and selecting XML > XML Import prompts Excel to connect to the API, retrieve the data, and populate the workbook.
Refreshing Data: To keep the information up-to-date, a mechanism for refreshing the data is essential. In Microsoft Excel, this is accomplished by navigating to the Data tab and clicking Refresh All. This ensures that the latest data from the API is pulled into the spreadsheet.
Microsoft Excel 2010: For users with Microsoft Excel 2010, the process is slightly different. Instead of the Developer tab, the Data tab plays a pivotal role. The steps involve selecting From Web in the Get External Data section, entering the API URL in the Address field, and importing the data. Users are then empowered to manipulate and analyze the data using tools such as Pivot Tables or Power Query.
In essence, this seamless integration of API data into Microsoft Excel showcases a sophisticated approach to data extraction and analysis, underscoring the synergy between powerful APIs and versatile spreadsheet tools.
