1. Business Intelligence Connector
  2. Connecting BI Tools to Your OpenAir OData Feed
  3. Importing Data from Your OpenAir OData Feed to Microsoft Excel

Importing Data from Your OpenAir OData Feed to Microsoft Excel

The steps described in this topic are provided for illustration purposes only. Refer to the vendor documentation for detailed instructions about connecting Microsoft Excel with an OData feed. In Excel 2016 for Windows and later versions, you can use Get & Transform (Power Query) to connect to an OData feed and perform advanced queries. See Import data from external data sources (Power Query) on the Microsoft website for more information .

Use the following steps to connect Microsoft Excel 2016, Excel 2019 or Excel for Office 365 for Windows with data from your OpenAir OData feed.

Note:

Other versions of Microsoft Excel may not support this functionality natively . In particular:

  • Microsoft Excel for Mac — You must install an Open Database Connectivity (ODBC) driver for OData to import data from an OData feed into Excel for Mac. See ODBC drivers that are compatible with Excel for Mac on the Microsoft website or search “ODBC driver for OData for Mac” for information about available third-party plug-ins.

  • Microsoft Excel 2013 for Windows — You must activate the Power Query add-in to connect to external data sources. See Import data from external data sources (Power Query) on the Microsoft website for more information .

To import data from your OpenAir OData to Microsoft Excel for Windows:

  1. In Microsoft Excel, click the Data tab.

  2. Do one of the following

    1. In Microsoft Excel 2019 for Windows or later versions:

      Click Get Data, then point to From Other Sources and click From OData Feed. If you don't see the Get Data button, then click New Query, then point to From Other Sources and click From OData Feed.

      GetData from OData Feed in Microsoft Excel 2019 for Windows.

    2. In Microsoft Excel 2016 for Windows:

      Click New Query, then point to From Other Sources and click From OData Feed.

      Note:

      You cannot import your data using the From Other Sources option in the Get External Data group in Microsoft Excel 2016 for Windows.

      GetData from OData Feed in Microsoft Excel 2016 for Windows.

  3. In the OData feed popup window, select Basic from the radio button options box, and enter the URL for the reports or list views resource collections in your OpenAir OData feed — for more information about your OpenAir OData feed URL, see Your OpenAir OData Feed URL and Connection Details.

    OData feed dialog box in Microsoft Excel 2019 for Windows.

  4. Click OK.

  5. In the OData feed popup window In the OData feed window:

    1. Click Basic.

    2. Enter your OpenAir OData feed User name. Remember, your OpenAir OData feed user name contains your OpenAir Company ID and User ID, separated by a back slash: <CompanyID>\<UserID>

    3. Enter your OpenAir Password.

    4. Keep the default option for Select which level to apply your login settings to or select one of the dropdown options.

    Basic connection settings on the OData feed dialog box in Microsoft Excel 2019 for Windows.

  6. Click Connect. The Navigator popup window appears.

  7. The Navigator popup window lists the published reports or list views available in your OpenAir OData feed by their OData resource names.

    • Select the resource name for the published report or list view you want to import. A preview of the selected resource data appears in the right pane of the Navigator window.

    • To load multiple resources in your Excel workbook , check the Select multiple items box then check the box next to the resources you want to import.

    Navigator dialog box in Microsoft Excel 2019 for Windows.
    Tip:

    Disable previews in the Navigator popup window to reduce the number of OData requests being made to retrieve these previews. Preview requests count toward the OData request limits allowed for your account. See BI Connector Requirements and Limits. To disable the preview click Display Options in the Navigator popup window and clear the Enable data previews option.

  8. Click Transform Data (or Edit depending on the Excel version). The Power Query Editor appears. You can use the Excel Power Query Editor to shape and transform the report or list view data. See Shape data (Power Query) and Edit query step settings (Power Query) on the Microsoft website for more information.

    Tip:

    Rename, remove and reorder columns or filter, sort and reorder your data using the Power Query Editor instead of transforming your report or list view data in the spreadsheet. The Get & Transform (Power Query) functionality in Excel lets you apply the same changes to your data every time you refresh your queries and connections. Changes made directly in the spreadsheet will be lost when refreshing the queries and connections.

    Note:

    If you are loading multiple resources into your Excel workbook, Excel creates a separate query for each resource you selected. The queries are listed in the Queries pane on the left of the Power Query Editor window. Click a query to select it for editing, shape and transform your data, and repeat for other queries. See View and Manage Queries in a Workbook (Power Query) on the Microsoft website for more information.

    You can edit your queries at any time. Click Queries & Connections in the Data tab to view the list of queries. Double click any query listed in the Queries & Connections pane on the right to open it for editing in the Power Query Editor.

  9. Click Close & Load (or Apply & Close depending on the Excel version). The transformed data loads in the Excel spreadsheet.

    Note:

    If you are loading multiple resources into your Excel workbook, Excel loads your data into as many separate sheets as there are queries.

  10. (Optional) Configure Excel to refresh your data as required:

    1. On the Excel Data tab, click the Refresh All dropdown and click Connection Properties .... The Query Properties popup window appears.

      Refresh Connection Properties in Microsoft Excel 2019 for Windows.
      Note:

      If you are loading multiple resources into your Excel workbook, click Queries & Connections to view the list of queries. The queries are l. Right click any query listed in the Queries & Connections pane and click Properties ... to configure its properties.

    2. Check the refresh options as required. You can configure Excel to refresh your data using any of the following options:

      • Refresh the data automatically at regular intervals. You can specify the refresh interval in minutes.

      • Refresh the data automatically when opening the Excel workbook.

      • Refresh the data manually by clicking Refresh All in the Data tab.

      Query Properties window in Microsoft Excel 2019 for Windows.
Note:

Microsoft Excel caches data from external data sources. You must refresh the data manually or configure the data connection to refresh the data automatically to get the latest data from your OpenAir OData resource.

In the Excel Data tab, click Refresh All Refresh all icon in Microsoft Excel 2019 for Windows. to get the latest data from your OpenAir OData feed and other sources in the entire workbook or click Refresh Refresh icon in Microsoft Excel 2019 for Windows. to get the latest data only from the OData resource associated with the worksheet you are currently viewing.

Important:

Every data refresh triggers a new request to the OpenAir OData service. Use automatic or manual data refresh sparingly to ensure you stay within the OData requests limits for your account. See BI Connector Requirements and Limits.