Import Data into Excel via API

Use Case

Using the CMW Platform API, you can import the application data to Excel (via Power Query) or BI systems.

In this article, we will import the data from a record template into an Excel file using the CMW Platform API and the Power Query add-in.

Open your Comindware Business Application Platform instance website.
To open the API web interface, type /docs after the domain name in the browser address bar, for instance:
mycompany.com/docs
Go to the Solution API section that lists all application templates.
Find the template you need by its system name and click it.
Select the GET method (without the {id} suffix) that returns all the records from the record template.
Click Try it out.
Copy the value in the Request URL field, for example:
https://mycompany.com/api/public/solution/Vehiclerequests

Obtaining the template GET request URL

Note: The CMW Platform API presents the data in JSON format.

The Power Query add-in is built into Excel 2016 and above. For earlier versions of Excel, you need to install it.

On the Data tab in the ribbon, click From Web
In the URL field, paste the Request URL you copied from the CMW Platform API.

Initializing the Power Query in Excel
In the Access Web content window:
- Select Basic.
- Enter the user name and password of the account with permission to view the selected record template.
- Click Connect.
  
  Configuring the credentials for API access
The received data appears in the Power Query Editor window.

Power Query Editor with the imported data
Click To Table in the ribbon. Click OK in the To Table window .

Configuring the query table
Expand the list of all record template attributes and select the attributes to load in the table.

Configuring the columns
Click Close & Load in the ribbon to import the data into the Excel spreadsheet.

Loading the query table to the Excel spreadsheet
The data will appear in the Excel spreadsheet as shown below.

A query table imported to the Excel spreadsheet