Excel Online Connection

See the Connector Marketplace topic. Please request your administrator to start a trial or subscribe to the Premium Excel Online connector.

Excel Online is a web-based version of Microsoft Excel, allowing users to create, edit, and collaborate on spreadsheets in a browser.

This topic describes how to authenticate to Excel Online and configure any necessary connection properties in the Excel Online connection connector.

Connection Configuration #

Each connection property available in the Excel Online connector is explained below.

Connection Name #

The name of the connection to be created should be provided. This is the name that will display on the list of available connections.

Auth Scheme #

The type of authentication to use when connecting to Microsoft Excel Online.

AzureAD: Set this to perform Azure Active Directory OAuth authentication.

AzureServicePrincipal: Set this to authenticate as an Azure Service Principal using a Client Secret.

Client ID #

The client ID assigned when you register your application with an OAuth authorization server.

Client Secret #

The client secret assigned when you register your application with an OAuth authorization server.

Refresh Token #

The OAuth refresh token for the corresponding OAuth access token.

Azure Tenant #

The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.

Example: contoso.onmicrosoft.com.

Alternatively, specify the tenant Id. This value is the directory Id in the Azure Portal > Azure Active Directory > Properties.

Azure Environment #

The Azure Environment to use when establishing a connection.

Possible Values:

• GLOBAL

• CHINA

• USGOVT

• USGOVTDOD

In most cases, leaving the environment set to global will work. However, if your Azure Account is added to a different environment, the Azure Environment may be used to specify which environment.

Work Book #

The name or Id of the workbook.

Drive #

The Id of the drive.

This property takes precedence over SharepointURL.

This means that if Sharepoint URL and Drive are specified, a schema will only be identified for the drive specified by Drive, and tables will only be identified from the worksheets in workbooks in this drive.

Sharepoint URL #

The base URL of your Sharepoint Server.

To query the SharePointSites, this base URL of your Sharepoint Server must be specified.

A protocol prefix, either “http://” or “https://”, must be provided in the value supplied for this property.

Example: https://SomeTenantName.sharepoint.com.

This property can also be used to control what drives, workbooks, and worksheets are exposed by the connector.

If you specify Sharepoint URL and do not specify Drive, the connector will enumerate drives from the specified site as schemas, and enumerate all the worksheets from all the workbooks in each drive as tables in their respective schema.

If this property is specified when using AzureServicePrincipal authentication, the search endpoint will not be used. This property must be specified if working with a custom application that has Sites.Selected as its highest privilege.

Define Tables #

This property is used to define the ranges within a worksheet that will appear as tables.

The value is a semicolon-separated list of name-value pairs in the form [Table Name]=[Catalog].[Schema].[{Workbook Name}_{Worksheet Name}!{Range}].

The value can also be specified in the form [Table Name]=[{Workbook Name}_{Worksheet Name}!{Range}].

Note that in this case, the connector will attempt to push the defined table for each schema it identifies, should the specified workbook and worksheet exist in the schema.

For specifications of Define Tables, some characters, like “.”, are reserved characters.

To escape these characters and make sure that the name containing them is parsed properly, surround the containing name with brackets, as in [work.book_work.sheet!A1:Z50].

For this example, the connector would properly search for the workbook and worksheet identified by work.book_work.sheet, despite the specification containing reserved characters.

Here is an example Define Tables value:

DefineTables="Table1=Test_xlsx_Sheet1!A1:N25,Table2=[Gathr].[MySchema].[Spreadsheet1_Sheet2!C3:M53],Table4=xIsPcLs2-bF3AavQcSLCfzs3kGc_Sheet4!C20:N60"

After setting DefineTables, you can then issue queries that reference the specified table name.

Note that you can also use the range syntax directly in the SQL statement: append the range to the worksheet name with the “#” character.

Example: The following command will select the range of cells between A1 and E5:

SELECT * FROM Test_xlsx_Sheet1#A1:E5

Enable SSL #

Option for creating the connection with SSL enabled.

Upload Keystore File #

Option to upload keystore file.

Advanced Configuration #

This section contains advanced configuration parameters.

Include Share Point Sites #

Whether to retrieve drives for all SharePoint sites when querying Drives view.

If ’true’ the provider will retrieve all Site IDs recursively and for each of them issue a separate call to get their drives.

Therefore, be aware that setting this property to ’true’ may decrease performance for the Drives view.

Add Configuration: Additional properties can be added using this option as key-value pairs.

After entering all the details, click on the TEST button.

If the connection service identification and authentication details are provided correctly, a success message stating “connection available” is generated.

Click on the CREATE button to save the changes.

If the details are incorrect or the server is down, you will get a message “Connection unavailable”.