Set up Microsoft Excel to import data from WorkZone

WorkZone uses the Microsoft Excel Power Query to manage data transfer from WorkZone to Excel and this means that when exporting data from WorkZone to Microsoft Excel, either exporting the results of a query from WorkZone QueryBuilder or exporting a list, chat or notes from WorkZone Client, each user must first set up the Power Query connection in Microsoft Excel.

Define authentication procedure and OData-feed settings

After privacy levels have been defined for the data, the user must define how to authenticate access to the data source.

  1. In the Access Web content form, select Windows and thenUse my current credentials as the authentication procedure
  2. Click Connect to create the connection and open the the OData-feed form
  3. In the OData-feed form, select Windows and thenUse my current credentials as the authentication procedure
  4. Click Connect to create the connection and import WorkZone data.

Updating the spreadsheet

To update refresh an Excel spreadsheet with the latest data from WorkZone , in Excel, click Data > Queries and Connections group > Update all.

Viewing data sources

In Excel, click Data > Queries and Connections to open the Queries and Connections panel and see the data sources used for the export:

  • OdataMetadataAndType: The spreadsheet's own data source - more specifically the data in the hidden Connection (Do Not Delete) worksheet.
  • Source: The actual results of the query.
  • Schema: The Odata metadata specifications, for example the localized labels and data types of the columns (fields).
  • WorkZone OData: The output that is displayed in the spreadsheet once the export has completed. You can right-click the WorkZone OData data source and select Edit to edit and manipulate the displayed columns.

Tip: When exporting query results from WorkZone QueryBuilder, the query itself as well as other general query information (for example the language settings used in Querybuilder and the type name used) is stored in the spreadsheet in a hidden worksheet called Connection (Do Not Delete). You can display the worksheet to view the query information.

Deleting or editing the connection settings

Users can edit or delete the connection privacy levels as well as authentication settings in the Data source settings form which is opened in Excel > Data > Get & Transform Data group > Get Data > Data source settings. Note that the WorkZone data sources are Global permissions.