Using Microsoft Excel

Edit online

You can load data from several reports into Microsoft Excel by using the IBM® ESG Suite APIs.

About this task

The procedure in this topic is an example of how you might use Microsoft Excel with the Envizi ESG Suite APIs. Refer to the Microsoft Excel documentation for the procedure for your version.

Data is returned in string format from the Envizi ESG Suite API. When you load data into Microsoft Excel from Envizi ESG Suite, perform the following updates as a minimum:
  • Change the data type of all date related columns to the date type.
  • Change the data type of all number columns to the numeric data type, which represents, for example, decimal numbers, whole numbers.
Note: It might take some time for the data from the Envizi ESG Suite API to be populated in the spreadsheet. It depends on the amount of data that you are loading.

In this procedure, you provide the request URL and a JWT token. For information about forming the request URL, see Request APIs. For information about the JWT token, see Generating a JWT token.

Note: If you use the same login user name on multiple production servers, you will likely encounter a 401 Unauthorized error when using API tokens to connect to the platform. This is a security feature and you require a separate login with a unique user name to generate API tokens.

Procedure

  1. Open Microsoft Excel.
  2. From the main menu, click Data > From Web.
  3. Click Advanced and then click OK.
  4. In the URL preview field, add your request URL.
  5. Provider your JWT bearer token. In the HTTP request header parameters field, select Authorization and enter Bearer followed by a space and then your access token. For example:
    Bearer bd516f6f775a5f17a2b75db851ec
  6. Access web content anonymously. In the Access Web content dialog, choose Anonymous and click Connect.
    The Query Editor opens and the data loads.
    If you do not see this dialog, you might have previously used your username and password to access the request URL. You must remove any credentials that you have save previously in Microsoft Excel before you can use the JWT tokens to access the APIs. Go to Get data > Data Source Settings > Global permissions. Select the request URL that you will use to access the Envizi ESG Suite APIs, and click Clear permissions. Then, reconnect to the API URL by using your JWT token.
  7. On the Transform tab, click To Table.
  8. In the To Table dialog, click OK.
    Your data is currently organized in a single column.
  9. Click the two-way expansion arrow in the column header.
  10. Deselect the Use original column name as prefix checkbox and click OK.
    The data displays in tabular format in the Query Editor.
  11. Use Power Query transform operations to arrange your data, for example, rename columns, reorder columns, or change the data types of columns.
    For more information about these changes, see the About this task section.
  12. Click Close & Load to save the query and populated the Microsoft Excel spreadsheet with the data.
  13. Load the data into a Power Pivot model, and use multiple API queries to build your own Power Pivot model, just as you would do for any Power BI project.