Microsoft Excel Online Connection Details

Introduction

Connector Version

This documentation is based on version 25.0.9368 of the connector.

Get Started

Microsoft Excel Online Version Support

The connector supports connecting to Excel Services via SharePoint Online, which is supported by the Microsoft Graph API. It models spreadsheets and ranges as bidirectional tables.

Establish a Connection

Connect to Microsoft Excel Online

There are two authentication methods available for connecting to Microsoft Excel Online data source:

• OAuth 2.0-based methods: Microsoft Excel Online provides OAuth 2.0-based authentication via both Microsoft Entra ID and Microsoft Entra service principal.
  • For Microsoft Entra ID authentication, set AuthScheme to Microsoft Entra ID.
  • For Microsoft Entra service principal authentication, set AuthScheme to AzureServicePrincipal.
• Managed Service Identity (MSI) authentication. To use this method, set AuthScheme to AzureMSI.

Microsoft Entra ID

Microsoft Entra ID is user-based authentication. Credentials are supplied to connect Microsoft Entra ID to Microsoft Excel Online via a Desktop application. To use the embedded credentials for authentication, leave the OAuth credentials (OAuthClientId and OAuthClientSecret) blank.

For connecting via a Web application, you must create a custom OAuth Microsoft Entra ID application. The creation of a Custom Microsoft Entra ID application establishes the OAuth credentials OAuthClientId and OAuthClientSecret. For a discussion of how to create a custom Microsoft Entra ID application, and reasons why that might be advantageous even if you are not connecting via a Web application, see Creating an Entra ID (Microsoft Entra ID) Application.

Microsoft Entra service principal

The authentication as a Microsoft Entra service principal is handled via the OAuth Client Credentials flow. It does not involve direct user authentication. Instead, credentials are created for just the application itself. All tasks taken by the application are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.

Create an Microsoft Entra ID App and a Microsoft Entra service principal

When authenticating using a Microsoft Entra service principal, you must create and register a Microsoft Entra ID application with a Microsoft Entra ID tenant. See Creating an Entra ID (Microsoft Entra ID) Application for more details.

In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated permissions and Application permissions. The permissions used during client credential authentication are under Application Permissions.

Assign a role to the application

To access resources in your subscription, you must assign a role to the application.

1. Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
2. Select the subscription to assign the application to.
3. Open the Access control (IAM) and select Add > Add role assignment to open the Add role assignment page.
4. Select Owner as the role to assign to your created Microsoft Entra ID app.

Complete the Authentication

Choose whether to use a client secret or a certificate and follow the relevant steps below.

Client Secret

Set these connection properties:

• AuthScheme: AzureServicePrincipal to use a client secret.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: The tenant you want to connect to.
• OAuthClientId: The client ID in your application settings.
• OAuthClientSecret: The client secret in your application settings.

Certificate

Set these connection properties:

• AuthScheme: AzureServicePrincipalCert to use a certificate.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: The tenant you want to connect to.
• OAuthJWTCert: The JWT Certificate store.
• OAuthJWTCertType: The type of the certificate store specified by OAuthJWTCert.

You are now ready to connect. Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections take place and are handled internally.

Managed Service Identity (MSI)

If you are running Microsoft Excel Online on an Azure VM and want to automatically obtain Managed Service Identity (MSI) credentials to connect, set AuthScheme to AzureMSI.

User-Managed Identities

To obtain a token for a managed identity, use the OAuthClientId property to specify the managed identity's client_id.

If your VM has multiple user-assigned managed identities, you must also specify OAuthClientId.

Connect to a Workbook

The connector exposes workbooks and worksheets from drives you specify in your Microsoft account. You can connect to a workbook by providing authentication to Excel Online and setting any of the following properties.

To control which drives are discovered:

• Drive: The ID of a specific drive. You can use the Drives and SharePointSites views to view all the sites and drives you have access to.
• SharepointURL: The browser URL of a SharePoint site. The driver exposes all drives under the site.
• OAuthClientId: If AuthScheme is set to AzureServicePrincipal, the drive associated with your OAuth application is exposed.

If none of the above are specified, access is restricted to the authenticated user's personal drive.

To control which workbooks and worksheets are exposed OR which drives are exposed:

• Workbook: The name or ID of the workbook. An authenticate user can view a list of information about the available workbooks by executing a query to the Workbooks view.
• UseSandbox: True to connect to a workbook in a sandbox account; otherwise, leave blank.
• BrowsableSchemas: A list of drive names to expose.
• Tables: A list of table names to expose.

Execute SQL Against Worksheet Data

For information on how to execute data manipulation SQL against worksheets and ranges, see:

• Selecting ExcelOnline Data
• Inserting ExcelOnline Data
• Updating ExcelOnline Data
• Deleting ExcelOnline Data
• Using Formulas

For details on how the connector models worksheets and cells as tables and columns, See Data Model.

Retrieve Data from SharePoint Excel Files

To retrieve data from Sharepoint Excel files, set the SharepointURL connection property to the URL of your Sharepoint site. For example,

SharepointURL=https://mysite.sharepoint.com/

The driver automatically looks up each document library you have in SharePoint and lists it as a schema. Individual Excel workbooks and worksheets are listed as tables in the format Workbook_Worksheet under their corresponding document library. This works in the same manner as listing your own personal Excel documents when SharepointURL is not set.

Create an Entra ID (Microsoft Entra ID) Application

Create an Entra ID (Microsoft Entra ID) Application

Microsoft Excel Online supports OAuth-based authentication using Microsoft Entra ID. If you will connect via a web application and want to authenticate via Entra ID, you must first register a custom OAuth application in the Entra Admin Center, as described below.

If you will connect via a desktop application or headless machine, you can authenticate using Microsoft Excel Online's built-in embedded application credentials, which use branding. However, custom OAuth applications are also compatible with desktop and headless authentication flows, and may be preferable for production deployments or environments requiring strict policy control.

Register the Application

To register an OAuth application in Microsoft Entra ID, follow these steps:

1. Go to https://portal.azure.com.

2. In the left-hand navigation pane, select Microsoft Entra ID > App registrations.

3. Click New registration.

4. Enter a name for the application.

5. Specify the types of accounts this application should support:

   • For private-use applications, select Accounts in this organization directory only.
   • For distributed applications, select one of the multi-tenant options.

   Note

   If you select Accounts in this organizational directory only, when you connect with Microsoft Excel Online connector, you must set AzureTenant to the tenant's ID (either GUID or verified domain). Otherwise, authentication will fail.

6. Set the redirect URI to http://localhost:33333 (default), or use another URI appropriate for your deployment.

   When using a custom redirect URI set a CallbackURL connection property; in those cases, set it to match this URI exactly.

7. Click Register. The application management screen opens. Record these values for later use:

   • Application (client) ID is used for OAuthClientId
   • Directory (tenant) ID is used for AzureTenant

8. Go to Certificates & Secrets. Click New Client Secret, set the desired expiration, and save the generated value. This value will only be shown once — record it to use with OAuthClientSecret.

9. Add the following application permissions: Sites.Read.All, Files.Read, Files.Read.All, Files.Read.Selected, Files.ReadWrite, Files.ReadWrite.All, Files.ReadWrite.AppFolder, Files.ReadWrite.Selected, and offline_access.

10. To confirm, click Add permissions.

Select ExcelOnline Data

The following connection properties enable you to execute SELECT queries that closely match how your sheet is organized.

Select Column Headers

If your worksheet has column headers, you can set the Header property to use column names defined in a header row. Then you can query like so:

SELECT Id, Name, Item, Quantity, Amount FROM Test_xlsx_Sheet1 WHERE Amount = 50

The query above assumes that the first row of the worksheet has the column names Id, Name, Quantity, and Amount. The worksheet may have more columns than were selected and they can be in any order.

Select Alphabetical Columns

If a workbook does not have headers, or if you would like to ignore the headers, you can set the Header property to false. In this case each column is represented by alphabetical names. For example:

SELECT A, B, C, D FROM Test_xlsx_Sheet1 WHERE D = 50

Select Ranges

Instead of working with the spreadsheet as a single table, you can work with ranges as tables. Some examples: In a sparse workbook, you can use ranges to select data without the empty rows. Or, in a worksheet that is not top-left aligned, you can use a range to select data in the middle of the sheet.

To select a range of data on the worksheet, append the range to the table name with the # character. For example, the following command will select the range of cells between A1 and E5:

SELECT * FROM Test_xlsx_Sheet1#A1:E5

To assign a table name to a range, and avoid specifying the range repeatedly, see the Tables property.

Insert ExcelOnline Data

You can execute INSERT statements when working with worksheets as tables. You can specify the column names detected from a header row or specify the alphabetical column names in Excel Online. You can also configure how the connector parses the input values into Microsoft Excel Online cell formats.

Set the Header property to configure column names. Set ValueInputOption to configure input parsing.

Note that inserting to ranges is not supported. Use Batch Inserts or Temporary Tables instead.

Insert with Headers

When the Header property is set to true, you can specify cells in a header row as the column names.

INSERT INTO Test_xlsx_Sheet1 (Name, Amount) VALUES ('Test', 10)

Insert without Headers

When the Header property is set to false, provide the alphabetical column names.

INSERT INTO Test_xlsx_Sheet1 (A, B) VALUES ('Test', 10)

Configure Input Parsing

By default, the connector parses all values you input in the SQL statement the same as if you entered the input in the Microsoft Excel Online UI. For example, strings may be converted to numbers, dates, etc.

To disable the parsing of input values, set ValueInputOption. Additionally, see Using Formulas to configure formula evaluation.

Example: Bulk Inserts using Temporary Tables

If using temporary tables, they must be defined and inserted within the same connection. Closing the connection will clear out any temporary tables in memory.

Create Temporary Tables

Insert the rows you need in a temporary table. Then insert all the rows from that temporary table into the actual table.

Code Example

Below is the complete code to insert 2 rows into Test_xlsx_Sheet1:

Connection conn = DriverManager.getConnection("jdbc:excelonline:InitiateOAuth=GETANDREFRESH;");
Statement stat = conn.createStatement();

stat.executeUpdate("INSERT INTO [Test_xlsx_Sheet1#TEMP] (Column1, Column2) VALUES ('value1', 'value2')");
stat.executeUpdate("INSERT INTO [Test_xlsx_Sheet1#TEMP] (Column1, Column2) VALUES ('value3', 'value4')");

stat.executeUpdate("INSERT INTO [Test_xlsx_Sheet1] (Column1, Column2) SELECT Column1, Column2 FROM [Test_xlsx_Sheet1#TEMP]");

stat.close();

Update ExcelOnline Data

In UPDATE statements, you can specify the column names detected from a header row or specify the alphabetical column names in Microsoft Excel Online. You can also configure how the connector parses the input values into Microsoft Excel Online cell formats.

Set the Header property to configure column names. The primary key is the row number; specify the primary key with the ID column. Set ValueInputOption to configure input parsing.

Update with Headers

When the Header property is set to true, you update columns by specifying the detected column names.

UPDATE Customers SET Col1='value' WHERE Id=7

Update without Headers

When the Header is set to false, provide the alphabetical column names:

UPDATE Customers SET H='value' WHERE Id=7

Update Ranges

Set the columns corresponding to the range and specify the row number as the Id:

UPDATE [Customers#A15:C15] SET A='Ana Trujilo', B='Northwind, Inc.', C='100,000' WHERE Id='15'

Configure Input Parsing

By default, the connector parses all values you input in the SQL statement the same as if you entered the input in the Microsoft Excel Online UI. For example, strings may be converted to numbers, dates, etc.

To disable the parsing of input values, set ValueInputOption. Additionally, see Using Formulas to configure formula evaluation.

Delete ExcelOnline Data

When deleting an entire row of data, you will need to be sure to refresh the table data. This is because Ids are assigned based on the row. For example, if there are five rows, with Ids 1 through 5, and you delete row 3 then row 4 will become 3 and 5 will become 4.

Use Formulas

Set the following properties to configure formula evaluation when executing SELECT, INSERT, and UPDATE statements.

Select Formulas

The connector can return either the result of the formula or the formula itself; configure this behavior with the ValueRenderOption connection property.

Insert and Updating Formulas

Set AllowFormula to true to insert formulas (the default). Otherwise, all values used in inserts and updates are inserted as string literals.

For example, with AllowFormula set to true, the following will insert a formula into the B column that will sum cells B1:B5:

INSERT INTO Excel_Sheet (A, B) VALUES ('Bill', '=SUM(B1:B5)')

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Microsoft Excel Online connector.

User Defined Views

The connector supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats. For further information, see the SSLServerCert property under "Connection String Options".

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Microsoft Excel Online and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Log

For an overview of configuration settings that can be used to refine logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

User Defined Views

The Microsoft Excel Online connector supports the use of user defined views: user-defined virtual tables whose contents are decided by a preconfigured query. User defined views are useful in situations where you cannot directly control the query being issued to the driver; for example, when using the driver from Jitterbit.

Use a user defined view to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User defined views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This user defined view configuration file is formatted so that each root element defines the name of a view, and includes a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Test_xlsx_Sheet1 WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

In order to avoid a view's name clashing with an actual entity in the data model, user defined views are exposed in the UserViews schema by default. To change the name of the schema used for UserViews, reset the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a user defined view called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a user defined view that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Data Model

Workbooks as Tables

The connector models the worksheets and ranges in your workbooks as bidirectional tables. When you connect, the connector retrieves the metadata from Microsoft Excel Online and reflects any changes to the table schemas.

See the Getting Started section for quickstarts to executing SQL to the tables. This section provides additional details on the connector behavior.

Tables

This section provides more detail about how the connector models the Tables. See Columns for details about column discovery.

Stored Procedures

In addition to working with the data in the workbook, you can use the available stored procedures to access functionality in the Microsoft Excel Online API that is not modeled as SELECT, INSERT, UPDATE, or DELETE statements; for example, the AddWorksheet stored procedure.

System Tables

Query the available System Tables to access the available connector metadata, including schema information.

Best Practices

1) If you currently have many workbooks or sheets in your account, it may take a long time to initially retrieve all the data. You may shorten this time by adding the Workbook connection property to your connection string.

2) If you will be running a SELECT operation after INSERT or BATCH INSERT then make sure to wait 3-5 seconds before running it. The API takes a few seconds to register all the data you're adding to the database, so waiting is required to avoid partial results.

Schemas

The connector exposes worksheets from workbooks as tables. To do so, it exposes the drives containing these workbooks and worksheets as schemas for these tables. How drives are exposed as schemas is dependent on the connection propeties for the connector.

The following connection properties determine what drives are exposed as schemas by the driver. They are listed in order of precedence, where only the most precedent property is used to expose schemas:

• Drive: The ID of a specific drive. Only this drive is exposed as schema.
• SharepointURL: The browser URL of a SharePoint site. The driver will expose all drives under the site. All of these drives are exposed as schemas.
• OAuthClientId: If AuthScheme is set to AzureServicePrincipal, the drive associated with your OAuth app is exposed. Only this drive is exposed as schema.
• If none of the above are specified, the personal drive for the authenticated user is used. Only this drive is exposed as schema.

To view all the sites and drives to which you have access, use the Drives and SharePointSites views.

To filter the schemas the driver exposes, use the BrowsableSchemas connection property. Supply a list of drive names to this property. The filtering is done client-side, so this means that the names supplied should be the names that would normally be returned by the connector for the drives.

Tables

The connector enables you to represent a top-left-oriented worksheet or a user-specified range as a database table.

Top-Left Oriented Worksheets

You can use the connector to start working right away with top-left-oriented worksheets:

• The table name for a top-left-oriented worksheet is the name of the worksheet.
• By default the connector will return all rows until the first empty row. Note: an empty row between data will prevent further data from being returned.
• The first row of data in the worksheet can contain the column names. See Columns for more information.

User-Specified Ranges

Range notation is only available in a SELECT or UPDATE statement. Ranges are not supported for DELETE and INSERT commands.

You can execute SELECT or UPDATE commands against a given range with a reference in the following format: WORKSHEET#RANGE. You can also set the Tables property to assign a table name to a range.

For example, the following command will select the range of cells between A1 and E5:

SELECT * FROM Test_xlsx_Sheet1#A1:E5

Accessing shared documents as a database table

You can use the connection property ShowSharedDocuments to list shared excel workbooks as database tables. The tables will be listed along-side user owned workbooks.

Executing SQL to Worksheet Data

See the following to execute data manipulation SQL to worksheets and ranges:

• Selecting ExcelOnline Data
• Inserting ExcelOnline Data
• Updating ExcelOnline Data
• Deleting ExcelOnline Data
• Using Formulas

Columns

When you connect, the connector discovers the available columns and reflects both changes in Microsoft Excel Online and configuration changes you make with the following connection properties. See the following sections to configure aspects of column discovery specific to Microsoft Excel Online.

Configuring Column Name Detection

You can set Header to detect column names in a header row or you can reference columns alphabetically.

Header=True (Default)

Below are more specifics on how the connector detects column names from a header row when Header is set to true.

• The connector determines columns from the first row of the worksheet. If no values are provided for the first row of the worksheet, the connector creates unique, alphabetized column names that are available only within the scope of that request.
• Headers should not contain special characters.
• Due to a limitation of the Excel Online API, all column headers must be nonempty.

Header=False

When Header is set to false, alphabetical column names will be dynamically assigned.

Primary Key

The connector reports the row number as the primary key. The ID column for each row represents the row number from the top of the sheet.

For example, if you specify a range A3:E6, rows 3, 4, 5, and 6 will be returned.

Configuring Column Value Parsing

The ValueRenderOption and ValueInputOption connection properties can be used to configure how the connector parses the Microsoft Excel Online cell formats in SELECT, INSERT and UPDATE operations.

Value render options for SELECT operations

Set the ValueRenderOption to configure how the connector renders values in the output of a SELECT operation. The available value render options are as below.

• FormattedValue: Values will be calculated and formatted in the output according to the cell's formatting. Formatting is based on the workbook's locale, not the requesting user's locale. This is the default ValueRenderOption.
• UnformattedValue: Values will be calculated but not formatted in the output.
• Formula: Values will not be calculated and instead the output will include the formulas. For this reason, every column containing a formula will be exposed as String type regardless of the set TypeDetectionScheme.

Value input options for INSERT and UPDATE operations

Set the ValueInputOption to configure how the connector parses values the user submits to the Microsoft Excel Online source for INSERT and UPDATE operations. The available value input options are as below.

• UserEntered: The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc., following the same rules that are applied when entering text into a cell via the Microsoft Excel Online UI. This is the default ValueInputOption.
• Raw: The values the user has entered will not be parsed and will be stored as-is.

Configuring Data Type Detection

Set the TypeDetectionScheme to configure the data type detection method. The available type detection schemes are as below.

• RowScan: The connector will scan rows to heuristically determine the data type. The number of rows the connector will scan can be determinated by the RowScanDepth property. This is the default TypeDetectionScheme.
• ColumnFormat: The connector will report the data type based on the cell format of the column. This option is applicable only for the 'UnformattedValue' and 'Formula' ValueRenderOption-s. If the ValueRenderOption is set to 'FormattedValue', all columns will be exposed as String types.
• None: The connector will return all columns as the string type.

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Microsoft Excel Online Connector Views

Drives

Retrieve a list of drive objects.

Select

The example query below will return the Drives with the specified SharePointSite Id.

SELECT * FROM Drives WHERE SiteId = 'rssbus.onmicrosoft.com,988a3-343a-4064-996a-d2a30559f,4233d-dfcd-4910-b7f0-71f1dc03a'

Columns

Folders

Retrieve a list of Folder objects.

Columns

SharedDocuments

Retrieve the list of all shared documents. Not every item listed by this view can be used as a workbook since the driver will filter out non excel files. See the Workbook and Worksheets views for more information.

Columns

SharePointSites

Retrieve the list of all sites in your SharePoint account.

Table Specific Information

In contrast to almost every other exposed table and view, this view does not implement any Microsoft Graph endpoint, but rather the '/query/search' endpoint of the SharePoint REST API. For this reason, in most cases you will need to specify the SharepointURL connection property prior to initiating the OAuth flow. During the Authorization flow, the connector will get the necessary permissions to utilize your SharePoint resources.

Select

The connector will use the SharePoint REST API to process WHERE clause conditions built with almost every column except for Id, and only for the EQUALS (=) operator. The rest of the filter is executed client side within the connector.

The example query below will return the SharePoint Site with the specified SiteId and WebId.

SELECT SiteId, SiteName, Title, SiteDescription FROM SharePointSites WHERE SiteId = '9839b8a3-343a-4064-996a-d2a300bc559f' AND WebId = '427d733d-dfcd-4910-b7f0-771f1d4cc03a'

Restrictions

To query this view using the 'AzureServicePrincipal' AuthScheme, you must specify the OAuthJWTCert instead of the OAuthClientSecret connection property. The restriction is imposed by the SharePoint REST API, which does not accept any OAuthAccessToken retrieved when specifying an OAuthClientSecret for the aforementioned authentication flows.

For a guide on how to authentication using a Certificate for the 'Microsoft Entra ID' and 'AzureServicePrincipal' AuthSchemes, see "Client Credentials" in "Getting Started".

Columns

Workbooks

Retrieve a list of workbook objects.

Columns

Worksheets

Retrieve a list of worksheet objects. If listing sheets in a workbook which do not belong to the user's drive, please specify the drive ID using the Drive connection property.

Columns

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Microsoft Excel Online.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Microsoft Excel Online, along with an indication of whether the procedure succeeded or failed.

Microsoft Excel Online Connector Stored Procedures

AddWorkbook

Creates a new workbook using the Microsoft Graph API.

Input

Result Set Columns

AddWorksheet

Add a worksheet to an existing Excel Online workbook.

Input

Result Set Columns

CreateSchema

Creates a schema file for the specified table or view.

CreateSchema

Creates a local schema file (.rsd) from an existing table or view in the data model.

The schema file is created in the directory set in the Location connection property when this procedure is executed. You can edit the file to include or exclude columns, rename columns, or adjust column datatypes.

The connector checks the Location to determine if the names of any .rsd files match a table or view in the data model. If there is a duplicate, the schema file will take precedence over the default instance of this table in the data model. If a schema file is present in Location that does not match an existing table or view, a new table or view entry is added to the data model of the connector.

Input

Result Set Columns

GetAdminConsentURL

Gets the admin consent URL that must be opened separately by an admin of a given domain to grant access to your application. Only needed when using custom OAuth credentials.

Input

Result Set Columns

GetOAuthAccessToken

Gets the OAuth access token from Excel Online.

Input

Result Set Columns

GetOAuthAuthorizationURL

Gets the Excel Online authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to Excel Online.

Input

Result Set Columns

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with Excel Online.

Input

Result Set Columns

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Microsoft Excel Online:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations:

• sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Test_xlsx_Sheet1 table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Test_xlsx_Sheet1'

Columns

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the AddWorksheet stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'AddWorksheet' AND Direction = 1 OR Direction = 2

To include result set columns in addition to the parameters, set the IncludeResultColumns pseudo column to True:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'AddWorksheet' AND IncludeResultColumns='True'

Columns

Pseudo-Columns

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Test_xlsx_Sheet1 table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Test_xlsx_Sheet1'

Columns

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys

Columns

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity

Columns

sys_information

Describes the available system information.

The following query retrieves all columns:

SELECT * FROM sys_information

Columns

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Authentication

Connection

Azure Authentication

OAuth

JWT OAuth

SSL

Schema

Miscellaneous

Authentication

This section provides a complete list of authentication properties you can configure.

AuthScheme

Specifies the type of authentication to use when connecting to Microsoft Excel Online. If this property is left blank, the default authentication is used.

Possible Values

Microsoft Entra ID, AzureMSI, AzureServicePrincipal, AzureServicePrincipalCert

Data Type

string

Default Value

Microsoft Entra ID

Remarks

• Microsoft Entra ID: Perform Microsoft Entra ID (user-based) OAuth authentication.
• AzureMSI: Automatically obtain Microsoft Entra ID Managed Service Identity credentials when running on an Azure VM.
• AzureServicePrincipal: Authenticate as a Microsoft Entra service principal (role-based, application-based) using a Client Secret.
• AzureServicePrincipalCert: Authenticate as a Microsoft Entra service principal (role-based, application-based) using a Certificate.

For information about creating a custom application to authenticate with Microsoft Entra ID, see Creating an Entra ID (Microsoft Entra ID) Application.

For information about creating a custom application to authenticate with Microsoft Entra ID Service Principal, see Creating a Service Principal App in Entra ID (Microsoft Entra ID).

UseSandbox

A boolean indicating if you are using a sandbox account. The provider makes requests to the production environment by default.

Data Type

bool

Default Value

false

Remarks

If you are using a production account, this property can be left blank. If you are using a developer test account, set this to true.

Connection

This section provides a complete list of connection properties you can configure.

DefineTables

Assign table names to ranges.

Data Type

string

Default Value

""

Remarks

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 driver 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 DefineTables, 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 driver would properly search for the workbook and worksheet identified by work.book_work.sheet, despite the specification containing reserved characters.

Here is an example DefineTables value:

DefineTables="Table1=Test_xlsx_Sheet1!A1:N25,Table2=[].[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.

For example, the following command will select the range of cells between A1 and E5:

SELECT * FROM Test_xlsx_Sheet1#A1:E5

Workbook

The name or ID of the workbook.

Data Type

string

Default Value

""

Remarks

Specifies the name or ID of the workbook. A list of all workbooks is available from the Workbooks view.

Drive

The ID of the drive.

Data Type

string

Default Value

""

Remarks

Specifies the ID of the drive. A list of all drives is available from the Drives view. This property takes precedence over SharepointURL. This means that if SharepointURL 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.

FolderId

The ID of the folder.

Data Type

string

Default Value

""

Remarks

Specifies the ID of the folder. A list of all folders is available from the Folders view.

IncludeSharePointSites

Specifies whether or not drives for all SharePoint sites are retrieved when querying Drives view. (Note: This property only affects Drives views.

Data Type

bool

Default Value

false

Remarks

If True, the connector retrieves all Site IDs recursively, and for each of them issues a separate call to get their drives. Be aware that setting this property to True may decrease performance for the Drives view.

Azure Authentication

This section provides a complete list of Azure authentication properties you can configure.

AzureTenant

Identifies the Microsoft Excel Online tenant being used to access data. Accepts either the tenant's domain name (for example, contoso.onmicrosoft.com) or its directory (tenant) ID.

Data Type

string

Default Value

""

Remarks

A tenant is a digital container for your organization's users and resources, managed through Microsoft Entra ID (formerly Microsoft Entra ID). Each tenant is associated with a unique directory ID, and often with a custom domain (for example, microsoft.com or contoso.onmicrosoft.com).

You can locate the directory (tenant) ID in the Microsoft Entra admin center by navigating to Microsoft Entra ID > Properties and copying the value labeled "Directory (tenant) ID".

This property is required in the following cases:

• When AuthScheme is set to AzureServicePrincipal or AzureServicePrincipalCert
• When AuthScheme is Microsoft Entra ID and the user account belongs to multiple tenants

You can provide the tenant value in one of two formats:

• A domain name (for example, contoso.onmicrosoft.com)
• A directory (tenant) ID in GUID format (for example, c9d7b8e4-1234-4f90-bc1a-2a28e0f9e9e0)

Specifying the tenant explicitly ensures that the authentication request is routed to the correct directory, which is especially important when a user belongs to multiple tenants or when using service principal–based authentication.

If this value is omitted when required, authentication may fail or connect to the wrong tenant. This can result in errors such as unauthorized or resource not found.

AzureEnvironment

Specifies the Azure network environment to which you will connect. Must be the same network to which your Azure account was added.

Possible Values

GLOBAL, CHINA, USGOVT, USGOVTDOD

Data Type

string

Default Value

GLOBAL

Remarks

Required if your Azure account is part of a different network than the Global network, such as China, USGOVT, or USGOVTDOD.

OAuth

This section provides a complete list of OAuth properties you can configure.

InitiateOAuth

Specifies the process for obtaining or refreshing the OAuth access token, which maintains user access while an authenticated, authorized user is working.

Possible Values

OFF, REFRESH, GETANDREFRESH

Data Type

string

Default Value

OFF

Remarks

OAuth is an authorization framework that enables applications to obtain limited access to user accounts on an HTTP service. The OAuth flow defines the method to be used for logging in users, exchanging their credentials for an OAuth access token to be used for authentication, and providing limited access to applications.

Microsoft Excel Online supports the following options for initiating OAuth access:

1. OFF: No automatic OAuth flow initiation. The OAuth flow is handled entirely by the user, who will take action to obtain their OAuthAccessToken. Note that with this setting the user must refresh the token manually and reconnect with an updated OAuthAccessToken property when the current token expires.
2. GETANDREFRESH: The OAuth flow is handled entirely by the connector. If a token already exists, it is refreshed when necessary. If no token currently exists, it will be obtained by prompting the user to login.
3. REFRESH: The user handles obtaining the OAuth Access Token and sets up the sequence for refreshing the OAuth Access Token. (The user is never prompted to log in to authenticate. After the user logs in, the connector handles the refresh of the OAuth Access Token.

OAuthClientId

Specifies the client ID (also known as the consumer key) assigned to your custom OAuth application. This ID is required to identify the application to the OAuth authorization server during authentication.

Data Type

string

Default Value

""

Remarks

This property is required when using a custom OAuth application, such as in web-based authentication flows, service-based authentication, or certificate-based flows that require application registration. It is also required if an embedded OAuth application is not available for the driver. When an embedded OAuth application is available, this value may already be provided by the connector and not require manual entry.

This value is generally used alongside other OAuth-related properties such as OAuthClientSecret and OAuthSettingsLocation when configuring an authenticated connection.

OAuthClientId is one of the key connection parameters that need to be set before users can authenticate via OAuth. You can typically find this value in your identity provider’s application registration settings. Look for a field labeled Client ID, Application ID, or Consumer Key.

While the client ID is not considered a confidential value like a client secret, it is still part of your application's identity and should be handled carefully. Avoid exposing it in public repositories or shared configuration files.

OAuthClientSecret

Specifies the client secret assigned to your custom OAuth application. This confidential value is used to authenticate the application to the OAuth authorization server.

Data Type

string

Default Value

""

Remarks

This property is required when using a custom OAuth application in any flow that requires secure client authentication, such as web-based OAuth, service-based connections, or certificate-based authorization flows. It is not required when using an embedded OAuth application.

The client secret is used during the token exchange step of the OAuth flow, when the driver requests an access token from the authorization server. If this value is missing or incorrect, authentication will fail, and the server may return an invalid_client or unauthorized_client error.

OAuthClientSecret is one of the key connection parameters that need to be set before users can authenticate via OAuth. You can obtain this value from your identity provider when registering the OAuth application. It may be referred to as the client secret, application secret, or consumer secret.

This value should be stored securely and never exposed in public repositories, scripts, or unsecured environments. Client secrets may also expire after a set period. Be sure to monitor expiration dates and rotate secrets as needed to maintain uninterrupted access.

OAuthAccessToken

Specifies the OAuth access token used to authenticate requests to the data source. This token is issued by the authorization server after a successful OAuth exchange.

Data Type

string

Default Value

""

Remarks

The OAuthAccessToken is a temporary credential that authorizes access to protected resources. It is typically returned by the identity provider after the user or client application completes an OAuth authentication flow. This property is most commonly used in automated workflows or custom OAuth implementations where you want to manage token handling outside of the driver.

The OAuth access token has a server-dependent timeout, limiting user access. This is set using the OAuthExpiresIn property. However, it can be reissued between requests to keep access alive as long as the user keeps working.

If InitiateOAuth is set to REFRESH, we recommend that you also set both OAuthExpiresIn and OAuthTokenTimestamp. The connector uses these properties to determine when the token expires so it can refresh most efficiently. If OAuthExpiresIn and OAuthTokenTimestamp are not specified, the connector refreshes the token immediately.

Access tokens should be treated as sensitive credentials and stored securely. Avoid exposing them in logs, scripts, or configuration files that are not access-controlled.

OAuthSettingsLocation

The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH. Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.

Data Type

string

Default Value

%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt

Remarks

When InitiateOAuth is set to GETANDREFRESH or REFRESH, the driver saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and to allow the credentials to be shared across connections or processes.

Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://' followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.

The default location is "%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt" with %APPDATA% set to the user's configuration directory. The default values are

• Windows: "register://%DSN"
• Unix: "%AppData%..."

where DSN is the name of the current DSN used in the open connection.

The following table lists the value of %APPDATA% by OS:

CallbackURL

Identifies the URL users return to after authenticating to Microsoft Excel Online via OAuth. (Custom OAuth applications only.).

Data Type

string

Default Value

""

Remarks

If you created a custom OAuth application, the OAuth authorization server redirects the user to this URL during the authentication process. This value must match the callback URL you specified when you Configured the custom OAuth application.

Scope

A space-seperated list of scopes.

Data Type

string

Default Value

"https://graph.microsoft.com/Sites.Read.All` `https://graph.microsoft.com/Sites.ReadWrite.All` `https://graph.microsoft.com/Files.Read` `https://graph.microsoft.com/Files.Read.All` `https://graph.microsoft.com/Files.Read.Selected` `https://graph.microsoft.com/Files.ReadWrite` `https://graph.microsoft.com/Files.ReadWrite.All` `https://graph.microsoft.com/Files.ReadWrite.AppFolder` `https://graph.microsoft.com/Files.ReadWrite.Selected

Remarks

This connection property is only used for the Microsoft Entra ID authentication flow. It defaults to the following: https://graph.microsoft.com/Sites.Read.All https://graph.microsoft.com/Sites.ReadWrite.All https://graph.microsoft.com/Files.Read https://graph.microsoft.com/Files.Read.All https://graph.microsoft.com/Files.Read.Selected https://graph.microsoft.com/Files.ReadWrite https://graph.microsoft.com/Files.ReadWrite.All https://graph.microsoft.com/Files.ReadWrite.AppFolder https://graph.microsoft.com/Files.ReadWrite.Selected.

State

Optional value for representing extra OAuth state information.

Data Type

string

Default Value

""

Remarks

Optional value for representing extra OAuth state information.

OAuthVerifier

Specifies a verifier code returned from the OAuthAuthorizationURL. Used when authenticating to OAuth on a headless server, where a browser can't be launched. Requires both OAuthSettingsLocation and OAuthVerifier to be set.

Data Type

string

Default Value

""

OAuthRefreshToken

Specifies the OAuth refresh token used to request a new access token after the original has expired.

Data Type

string

Default Value

""

Remarks

The refresh token is used to obtain a new access token when the current one expires. It enables seamless authentication for long-running or automated workflows without requiring the user to log in again. This property is especially important in headless, CI/CD, or server-based environments where interactive authentication is not possible.

The refresh token is typically obtained during the initial OAuth exchange by calling the GetOAuthAccessToken stored procedure. After that, it can be set using this property to enable automatic token refresh, or passed to the RefreshOAuthAccessToken stored procedure if you prefer to manage the refresh manually.

When InitiateOAuth is set to REFRESH, the driver uses this token to retrieve a new access token automatically. After the first refresh, the driver saves updated tokens in the location defined by OAuthSettingsLocation, and uses those values for subsequent connections.

The OAuthRefreshToken should be handled securely and stored in a trusted location. Like access tokens, refresh tokens can expire or be revoked depending on the identity provider’s policies.

OAuthExpiresIn

Specifies the duration in seconds, of an OAuth Access Token's lifetime. The token can be reissued to keep access alive as long as the user keeps working.

Data Type

string

Default Value

""

Remarks

The OAuth Access Token is assigned to an authenticated user, granting that user access to the network for a specified period of time. The access token is used in place of the user's login ID and password, which stay on the server.

An access token created by the server is only valid for a limited time. OAuthExpiresIn is the number of seconds the token is valid from when it was created. For example, a token generated at 2024-01-29 20:00:00 UTC that expires at 2024-01-29 21:00:00 UTC (an hour later) would have an OAuthExpiresIn value of 3600, no matter what the current time is.

To determine how long the user has before the Access Token will expire, use OAuthTokenTimestamp.

OAuthTokenTimestamp

Displays a Unix epoch timestamp in milliseconds that shows how long ago the current Access Token was created.

Data Type

string

Default Value

""

Remarks

The OAuth Access Token is assigned to an authenticated user, granting that user access to the network for a specified period of time. The access token is used in place of the user's login ID and password, which stay on the server.

An access token created by the server is only valid for a limited time. OAuthTokenTimestamp is the Unix timestamp when the server created the token. For example, OAuthTokenTimestamp=1706558400 indicates the OAuthAccessToken was generated by the server at 2024-01-29 20:00:00 UTC.

JWT OAuth

This section provides a complete list of JWT OAuth properties you can configure.

OAuthJWTCert

Supplies the name of the client certificate's JWT Certificate store.

Data Type

string

Default Value

""

Remarks

The OAuthJWTCertType field specifies the type of the certificate store specified in OAuthJWTCert. If the store is password-protected, use OAuthJWTCertPassword to supply the password..

OAuthJWTCert is used in conjunction with the OAuthJWTCertSubject field in order to specify client certificates.

If OAuthJWTCert has a value, and OAuthJWTCertSubject is set, the Microsoft Excel Online connector initiates a search for a certificate. For further information, see OAuthJWTCertSubject.

Designations of certificate stores are platform-dependent.

Notes

• The most common User and Machine certificate stores in Windows include:

  • MY: A certificate store holding personal certificates with their

    associated private keys.

  • CA: Certifying authority certificates.

  • ROOT: Root certificates.

  • SPC: Software publisher certificates.

  • In Java, the certificate store normally is a file containing certificates and optional private keys.
  • When the certificate store type is PFXFile, this property must be set to the name of the file.
  • When the type is PFXBlob, the property must be set to the binary contents of a PFX file (i.e. PKCS12 certificate store).

OAuthJWTCertType

Identifies the type of key store containing the JWT Certificate.

Possible Values

USER, MACHINE, PFXFILE, PFXBLOB, JKSFILE, JKSBLOB, PEMKEY_FILE, PEMKEY_BLOB, PUBLIC_KEY_FILE, PUBLIC_KEY_BLOB, SSHPUBLIC_KEY_FILE, SSHPUBLIC_KEY_BLOB, P7BFILE, PPKFILE, XMLFILE, XMLBLOB, BCFKSFILE, BCFKSBLOB

Data Type

string

Default Value

USER

Remarks

OAuthJWTCertPassword

Provides the password for the OAuth JWT certificate used to access a password-protected certificate store. If the certificate store does not require a password, leave this property blank.

Data Type

string

Default Value

""

Remarks

This property specifies the password needed to open a password-protected certificate store. To determine if a password is necessary, refer to the documentation or configuration for your specific certificate store.

OAuthJWTCertSubject

Identifies the subject of the OAuth JWT certificate used to locate a matching certificate in the store. Supports partial matches and the wildcard '*' to select the first certificate.

Data Type

string

Default Value

*

Remarks

The value of this property is used to locate a matching certificate in the store. The search process works as follows:

• If an exact match for the subject is found, the corresponding certificate is selected.
• If no exact match is found, the store is searched for certificates whose subjects contain the property value.
• If no match is found, no certificate is selected.

You can set the value to '*' to automatically select the first certificate in the store. The certificate subject is a comma-separated list of distinguished name fields and values. For example: CN=www.server.com, OU=test, C=US, E=example@jbexample.com. Common fields include:

If a field value contains a comma, enclose it in quotes. For example: "O=ACME, Inc.".

OAuthJWTIssuer

The issuer of the Java Web Token.

Data Type

string

Default Value

""

Remarks

The issuer of the Java Web Token. In most cases, this takes the value of the OAuth App ID (Client Id) connection property and does not need to be individually set.

OAuthJWTSubject

The user subject for which the application is requesting delegated access.

Data Type

string

Default Value

""

Remarks

The user subject for which the application is requesting delegated access. In most cases, this takes the value of the OAuth App ID (Client Id) connection property and does not need to be individually set.

SSL

This section provides a complete list of SSL properties you can configure.

SSLServerCert

Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.

Location

Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.

Data Type

string

Default Value

%APPDATA%\ExcelOnline Data Provider\Schema

Remarks

The Location property is only needed if you want to either customize definitions (for example, change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is %APPDATA%\ExcelOnline Data Provider\Schema, where %APPDATA% is set to the user's configuration directory:

BrowsableSchemas

Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.

Tables

Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing all available tables from some databases can take extra time, thus degrading performance. Providing a list of tables in the connection string saves time and improves performance.

If there are lots of tables available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those tables. To do this, specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Views

Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing all available views from some databases can take extra time, thus degrading performance. Providing a list of views in the connection string saves time and improves performance.

If there are lots of views available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those views. To do this, specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

AllowFormula

Whether or not to treat values starting with an equals (=) sign as formulas during inserts and updates.

Data Type

bool

Default Value

true

Remarks

Set this value to true to treat values beginning with an equals (=) sign as formulas on inserts and updates. Set the value to false to treat all values used in inserts and updates as strings.

For example, with AllowFormula set to true, the following will insert a formula into the B column that will sum cells B1:B5:

INSERT INTO Excel_Sheet (A, B) VALUES ('Bill', '=SUM(B1:B5)')

Header

Indicates whether or not the provider should detect column names from the first row.

Data Type

bool

Default Value

true

Remarks

If true, the first row will be used as a column header. Otherwise, the pseudo column names A, B, C, etc. will be used.

The connector will follow the additional specifications below to detect column headers:

• Headers should not contain special characters.
• Due to a limitation of the Excel Online API, all column headers must be nonempty.

IncludeFileExtensions

Set to true if file extensions should be included in table names.

Data Type

bool

Default Value

false

Remarks

Set to true if file extensions should be included in table names. For example, if set to True, this will make table [WorkbookName]_[WorksheetName] appear as [WorkbookName].[extension]_[WorksheetName].

MaxRows

Specifies the maximum rows returned for queries without aggregation or GROUP BY.

Data Type

int

Default Value

-1

Remarks

This property sets an upper limit on the number of rows the connector returns for queries that do not include aggregation or GROUP BY clauses. This limit ensures that queries do not return excessively large result sets by default.

When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting. If MaxRows is set to "-1", no row limit is enforced unless a LIMIT clause is explicitly included in the query.

This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.

NullValueMode

Indicates whether to read empty cells as null or as empty.

Possible Values

ReadAsNull, ReadAsEmpty

Data Type

string

Default Value

ReadAsNull

Remarks

NullValueMode controls how empty cells are modelled. An empty cell is a cell that has not been set and thus contains a null string. If NullValueMode is set to ReadAsNull, NULL is reported for an empty cell; if NullValueMode is set to ReadAsEmpty, an empty string is reported for an empty cell.

Other

Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.

Data Type

string

Default Value

""

Remarks

This property allows advanced users to configure hidden properties for specialized scenarios. These settings are not required for normal use cases but can address unique requirements or provide additional functionality. Multiple properties can be defined in a semicolon-separated list.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting

Pagesize

The maximum number of results to return per page from Microsoft Excel Online when UsePagination is True.

Data Type

int

Default Value

-1

Remarks

The Pagesize property affects the maximum number of results to return per page from Microsoft Excel Online. Setting a higher value may result in better performance at the cost of additional memory eaten up per page consumed. A value of -1 indicates the maximum possible page size for the driver.

PseudoColumns

Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.

Data Type

string

Default Value

""

Remarks

This property allows you to define which pseudocolumns the connector exposes as table columns.

To specify individual pseudocolumns, use the following format: "Table1=Column1;Table1=Column2;Table2=Column3"

To include all pseudocolumns for all tables use: "*=*"

RowScanDepth

The maximum number of rows to scan to look for the columns available in a table.

Data Type

int

Default Value

50

Remarks

The columns in a table must be determined by scanning table rows. This value determines the maximum number of rows that will be scanned.

Setting a high value may decrease performance. Setting a low value may prevent the data type from being determined properly, especially when there is null data.

SeparatorCharacter

The character to be used as a separator between workbook name and worksheet in the table name.

Data Type

string

Default Value

_

Remarks

The character to be used as a separator between workbook name and worksheet in the table name. Examples:

• If separator is set to "_" (default): MyWorkbook_Sheet1
• If separator is set to "-": MyWorkbook-Sheet1
• If separator is set to "#": MyWorkbook#Sheet1

SharepointURL

The base URL of your Sharepoint Server.

Data Type

string

Default Value

""

Remarks

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.

For 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 SharepointURL 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 or AzureServicePrincipalCert 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.

ShowEmptyRows

Indicates whether or not the empty rows should be pushed.

Data Type

bool

Default Value

false

Remarks

If true, the empty rows will be pushed at the output.

ShowSharedDocuments

Whether or not to show shared documents.

Data Type

bool

Default Value

false

Remarks

Whether or not to show shared documents. If set to true, shared documents will be listed along-side user owned workbooks as database tables. Ultimately, the specific files should have been granted direct access or explicitly shared with the authenticated user.

Timeout

Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.

Data Type

int

Default Value

60

Remarks

This property controls the maximum time, in seconds, that the connector waits for an operation to complete before canceling it. If the timeout period expires before the operation finishes, the connector cancels the operation and throws an exception.

The timeout applies to each individual communication with the server rather than the entire query or operation. For example, a query could continue running beyond the timeout value if each paging call completes within the timeout limit.

Setting this property to 0 disables the timeout, allowing operations to run indefinitely until they succeed or fail due to other conditions such as server-side timeouts, network interruptions, or resource limits on the server. Use this property cautiously to avoid long-running operations that could degrade performance or result in unresponsive behavior.

TypeDetectionScheme

Determines the method for identifying the data types of columns.

Possible Values

None, RowScan, ColumnFormat

Data Type

string

Default Value

RowScan

Remarks

The connector can detect data types with the cell format (number, currency, date, etc.) or by scanning rows.

UsePagination

Whether or not the ADO.NET Provider for Microsoft Excel Online should use client side paging.

Data Type

bool

Default Value

false

Remarks

Whether or not the Microsoft Excel Online connector should use client side paging. We suggest you set this to true in case of a large dataset.

UserDefinedViews

Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.

Data Type

string

Default Value

""

Remarks

This property allows you to define and manage custom views through a JSON-formatted configuration file called UserDefinedViews.json. These views are automatically recognized by the connector and enable you to execute custom SQL queries as if they were standard database views. The JSON file defines each view as a root element with a child element called "query", which contains the SQL query for the view. For example:

{
    "MyView": {
        "query": "SELECT * FROM Test_xlsx_Sheet1 WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }

}

You can define multiple views in a single file and specify the filepath using this property. For example: UserDefinedViews=C:\Path\To\UserDefinedViews.json. When you use this property, only the specified views are seen by the connector.

Refer to User Defined Views for more information.

UseSearchAPI

Specifies whether the ADO.NET Provider for Microsoft Excel Online should use the /search(q='.xls') endpoint to locate and retrieve workbook files. When set to true (default) , the driver performs a targeted search to list only workbook files, improving efficiency in large directories. If set to false, all items are listed using the /children endpoint, and workbook files are filtered client-side.

Data Type

bool

Default Value

true

Remarks

Set this property to true to use the /search(q='.xls') endpoint for efficient workbook retrieval, particularly in large directories. If issues occur with this endpoint, set the property to false to retrieve all items and filter them client-side.

UseSimpleNames

Specifies whether or not simple names should be used for tables and columns.

Data Type

bool

Default Value

false

Remarks

Microsoft Excel Online tables and can use special characters in names that are normally not allowed in standard databases. UseSimpleNames makes the connector easier to use with traditional database tools.

Setting UseSimpleNames to True simplifies the names of columns returned. It also enforces a naming scheme such that only alphanumeric characters and the underscore are valid for the displayed column names.

Notes:

• Any non-alphanumeric characters are converted to underscores.
• If the column or table names exceed 128 characters in length they are truncated to 128 characters to comply with SQL Server standards.

ValueInputOption

Determines how the provider parses values the user submits to Microsoft Excel Online.

Possible Values

UserEntered, Raw

Data Type

string

Default Value

UserEntered

Remarks

Determines how the connector parses values the user submits to Microsoft Excel Online.

ValueRenderOption

Determines how the provider renders values in the output.

Possible Values

FormattedValue, UnformattedValue, Formula

Data Type

string

Default Value

FormattedValue

Remarks

Determines how the connector renders values in the output.