The Provider for Microsoft Excel allows connecting to local and remote Excel resources. Set the URI property to the Excel resource location, in addition to any other properties necessary to connect to your data source.
While the provider is capable of pulling data from Microsoft Excel files hosted on a variety of cloud data stores, INSERT, UPDATE, and DELETE are not supported outside of local files in this provider.
If you need INSERT/UPDATE/DELETE cloud files, you can download the corresponding CData provider for that cloud host (supported via stored procedures), make changes with the local file's corresponding provider, then upload the file using the cloud source's stored procedures.
As an example, if you wanted to update a file stored on SharePoint, you could use the CData SharePoint provider's DownloadDocument procedure to download the Microsoft Excel file, update the local Microsoft Excel file with the CData Microsoft Excel provider, then use the SharePoint provider's UploadDocument procedure to upload the changed file to SharePoint.
A unique prefix at the beginning of the URI connection property is used to identify the cloud data store being targed by the provider and the remainder of the path is a relative path to the desired folder (one table per file) or single file (a single table).
Set the following to identify your Microsoft Excel resources stored on Amazon S3:
ConnectionType: Set the ConnectionType to Amazon S3.
URI: Set this to an Excel file in a bucket: s3://bucket1/folder1/file.xlsx.
Set the following to identify your Microsoft Excel resources stored on Azure Blob Storage:
ConnectionType: Set this to Azure Blob Storage.
URI: Set this to the name of your container and the name of the blob. For example: azureblob://mycontainer/myblob/file.xlsx.
Set the following to identify your Microsoft Excel resources stored on Azure Data Lake Storage:
ConnectionType: Set this to Azure Data Lake Storage Gen1, Azure Data Lake Storage Gen2, or Azure Data Lake Storage Gen2 SSL.
URI: Set this to the name of the file system, the name of the folder which contains your Microsoft Excel files, and the name of an Excel file. For example:
Gen 1: adl://myfilesystem/folder1/file.xlsx
Gen 2: abfs://myfilesystem/folder1/file.xlsx
Gen 2 SSL: abfss://myfilesystem/folder1/file.xlsx
Set the following properties to connect:
ConnectionType: Set this to Azure Files.
URI: Set this the name of your azure file share and the name of the resource. For example: azurefile://fileShare/remotePath/file.xlsx.
AzureStorageAccount (Required): Set this to the account associated with the Azure file.
You can authenticate either an Azure access key or an Azure shared access signature. Set one of the following:
AzureAccessKey: Set this to the access key associated with the Azure file.
AzureSharedAccessSignature: Set this to the shared access signature associated with the Azure file.
Set the following to identify your Microsoft Excel resources stored on Box:
ConnectionType: Set this to Box.
URI: Set this the name of the file system, the name of the folder which contains your Microsoft Excel files, and the name of an Excel file. For example: box://folder1/file.xlsx.
Set the following to identify your Microsoft Excel resources stored on Dropbox:
ConnectionType: Set this to Dropbox.
URI: Set this to the path to a Excel file. For example: dropbox://folder1/file.xlsx.
See Connecting to Dropbox for more information regarding how to connect and authenticate to Excel files hosted on Dropbox.
The provider supports both plaintext and SSL/TLS connections to FTP servers.
Set the following connection properties to connect:
ConnectionType: Set this to either FTP or FTPS.
URI: Set this to the address of the server followed by the path to the Excel file. For example: ftp://localhost:990/folder1/file.xlsx or ftps://localhost:990/folder1.
User: Set this to your username on the FTP(S) server you want to connect to.
Password: Set this to your password on the FTP(S) server you want to connect to.
Set the following to identify your Microsoft Excel resources stored on Google Cloud Storage:
ConnectionType: Set this to Google Cloud Storage.
URI: Set this to the path to the name of the file system, the name of the folder which contains your Microsoft Excel files, and the name of a Excel file. For example: gs://bucket/remotePath/file.xlsx.
Set the following to identify your Microsoft Excel resources stored on Google Drive:
ConnectionType: Set this to Google Drive.
URI: Set to the path to the name of the file system, the name of the folder which contains your Microsoft Excel files, and the name of an Excel file. For example: gdrive://folder1/file.xlsx.
Set the following to identify your Microsoft Excel resources stored on HDFS:
ConnectionType: Set this to HDFS or HDFS Secure.
URI: Set this to the path to a Excel file. For example:
HDFS: webhdfs://host:port/remotePath/file.xlsx
HDFS Secure: webhdfss://host:port/remotePath/file.xlsx
There are two authentication methods available for connecting to HDFS data source, Anonymous Authentication and Negotiate (Kerberos) Authentication.
Anonymous Authentication
In some situations, you can connect to HDFS without any authentication connection properties. To do so, set the AuthScheme property to None (default).
Authenticate using Kerberos
When authentication credentials are required, you can use Kerberos for authentication. See Using Kerberos for details on how to authenticate with Kerberos.
Set the following to identify your Microsoft Excel resources stored on HTTP streams:
ConnectionType: Set this to HTTP or HTTPS.
URI: Set this to the URI of your HTTP(S) stream. For example:
HTTP: http://remoteStream/file.xlsx
HTTPS: https://remoteStream/file.xlsx
Set the following to identify your Microsoft Excel resources stored on IBM Cloud Object Storage:
ConnectionType: Set this to IBM Object Storage Source.
URI: Set this to the bucket and folder. For example: ibmobjectstorage://bucket1/remotePath/file.xlsx.
Set the following to identify your Microsoft Excel resources stored on OneDrive:
ConnectionType: Set this to OneDrive.
URI: Set this to the path to a Excel file. For example: onedrive://remotePath/file.xlsx.
Set the following properties to authenticate with HMAC:
ConnectionType: Set the ConnectionType to Oracle Cloud Storage.
URI: Set this to an Excel file in a bucket: os://bucket/remotePath/file.xlsx.
AccessKey: Set this to an Oracle Cloud Access Key.
SecretKey: Set this to an Oracle Cloud Secret Key.
OracleNamespace: Set this to an Oracle cloud namespace.
Region (optional): Set this to the hosting region for your S3-like Web Services.
Set the following to identify your Microsoft Excel resources stored on SFTP:
ConnectionType: Set this to SFTP.
URI: Set this to the address of the server followed by the path to the folder to be used as the root folder. For example: sftp://server:port/remotePath/file.xlsx.
Set the following to identify your Microsoft Excel resources stored on SharePoint Online:
ConnectionType: Set this to SharePoint REST or SharePoint SOAP.
URI: Set this to a document library containing Excel files. For example:
SharePoint Online REST: sprest://remotePath/file.xlsx
SharePoint Online SOAP: sp://remotePath/file.xlsx
The URI, under the Connection section, must be set to a valid Excel File (including the file path). The provider supports the Office Open XML format used by Excel 2007 and later.
This section provides the details for connecting to SharePoint Online and SharePoint On Premises.
Note: Microsoft has stated that they plan to sunset the Excel Services API for SharePoint Online in February 2022. Please consider migrating to the provider for Excel Online for future access to your SharePoint data.
Regardless of which edition of SharePoint you are using, set File to the Excel workbook. This path is relative to the following properties:
Library: The Shared Documents library is used by default. You can use this property to specify another document library in your organization; for example, if you want to connect to OneDrive for Business, set this property to "Documents".
Folder: You can use this property to specify a path to a subfolder in a library. The path is relative to the library name specified in Library.
The provider detects the available tables based on the available objects in the underlying API.
The APIs surface different API objects; select the API based on the organization of your spreadsheets and your SharePoint version:
OData: The OData API allows access to tables defined from Excel table objects (not ranges or spreadsheets), which you create by clicking Insert > Table in Excel. When connecting to the OData API, the provider may not return any tables if you do not have table objects defined in your workbook. Set UseRESTAPI to true to connect to spreadsheets or ranges as tables.
REST: The REST API enables access to tables defined from Excel table objects, ranges, and spreadsheets. This is the default API. Requesting a large number of rows from ranges and spreadsheets is restricted by the REST API, and the provider limits the number of rows returned to 100 by default. The provider also defaults to detecting column names from the first row; set Header to disable this.
With DefineTables additionally set, you can define tables based on ranges, using the Excel range syntax. A range that is too large will be restricted by the API.
Set SharePointEdition to "SharePoint Online" and set the User and Password for an Azure Active Directory account.
Set the Url to a site collection to query workbooks in all nested subsites. Set the Url to a site to query workbooks in that site only.
To authenticate to SharePoint Online, set AuthScheme to the authentication type and set User and Password, if necessary.
Windows (NTLM): This is the most common authentication type. As such, the provider is preconfigured to use NTLM as the default; simply set the Windows User and Password to connect.
Basic:With the Basic authentication method, the user account credentials are sent as plaintext. To use this authentication type, set AuthScheme to Basic and set the User and Password.
Digest:With the Digest authentication method, the user account credentials are sent as an MD5 message digest. To use this authentication type, set AuthScheme to Digest and set the User and Password.
Forms: This allows authentication through a custom authentication method, instead of Active Directory. To use this authentication type, set AuthScheme to FORMS and set the User and Password.
Set the AuthScheme to ADFS. The following connection properties need to be set:
User: Set this to the ADFS user.
Password: Set this to ADFS password for the user.
SSODomain (optional): The domain configured with the ADFS identity provider.
Below is an example connection string:
Set the AuthScheme to Okta. The following connection properties are used to connect to Okta:
User: Set this to the Okta user.
Password: Set this to Okta password for the user.
SSODomain (optional): The domain configured with the OKTA identity provider.
The following is an example connection string:
Set the AuthScheme to OneLogin. The following connection properties are used to connect to OneLogin:
User: Set this to the OneLogin user.
Password: Set this to OneLogin password for the user.
SSODomain (optional): The domain configured with the OneLogin identity provider.
The following is an example connection string:
Set the AuthScheme to PingFederate. The following connection properties are used to connect to PingFederate:
User: Set this to the PingFederate user.
Password: Set this to PingFederate password for the user.
SSODomain (optional): The domain configured with the PingFederate identity provider.
The following is an example connection string:
Set SharePointEdition to "SharePoint OnPremise" and set the Url to your server's name or IP address. Additionally, set SharePointVersion and the authentication values.
To authenticate to SharePoint OnPremise, set AuthScheme to the authentication type and set User and Password, if necessary.
Note: When connecting to SharePoint On-Premises 2010, you must set UseRESTAPI to true.
Set the AuthScheme to ADFS. The following connection properties need to be set:
User: Set this to the ADFS user.
Password: Set this to ADFS password for the user.
SSOLoginURL: Set this to the WS-trust endpoint of the ADFS server.
The following SSOProperties are needed to authenticate to ADFS:
RelyingParty: The value of the relying party identifier on the ADFS server for Sharepoint.
Below is an example connection string:
| URL | Example URL |
|---|
NEGOTIATE: To authenticate with Kerberos, set AuthScheme to NEGOTIATE. Please see for details on how to authenticate with Kerberos.
Please see for details on how to authenticate with Kerberos.
Site | https://teams.contoso.com/teamA or https://teamA.contoso.com |
Site Collection | https://teams.contoso.com |
|
|
|
|
|
The provider 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 that control what drives are discovered:
Drive: Set this to 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: Set this to the browser URL of a SharePoint site. The driver will expose all drives under the site.
OAuthClientId: If AuthScheme is set to AzureServicePrincipal or if OAuthGrantType is set to CLIENT, the drive associated with your OAuth app will be exposed.
If none of the above are specified, the personal drive for the authenticated user will be used.
To control what workbooks and worksheets are exposed from the discovered drives, or what drives are exposed, you can use the following properties:
Workbook: Set to the name or Id of the workbook. If you want to view a list of information about the available workbooks, execute a query to the Workbooks view after you authenticate.
UseSandbox: Set to true if you are connecting to a workbook in a sandbox account. Otherwise, leave this blank to connect to a production account.
BrowsableSchemas: Set to a list of drive names. The drives that are exposed will be filtered by this list.
Tables: Set to a list of table names, as exposed by the driver. The tables that are exposed will be filtered by this list.
There are two authentication methods available for connecting to Microsoft Excel Online data source, the OAuth 2.0 (AzureAD) and the MSI Authentication methods.
To authenticate using OAuth, you may leave the OAuth credentials blank to use the provider's embedded app.
Alternatively, you may create a custom app to obtain the OAuthClientId, OAuthClientSecret to use custom OAuth credentials. In addition to those properties, set CallbackURL.
AuthScheme: Set this to AzureAD.
Azure Service Principal is a connection type that goes through OAuth. Set your AuthScheme to AzureServicePrincipal. The authentication as an Azure Service Principal is handled via the OAuth Client Credentials flow, and it does not involve direct user authentication. Instead, credentials are created for just the app itself. All tasks taken by the app 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.
Note: You must create a custom application prior to assigning a role. See Creating a Custom AzureAD App for more information.
When authenticating using an Azure Service Principal, you must register an application with an Azure AD tenant. Follow the steps below to create a new service principal that can be used with the role-based access control.
Assign a role to the application
To access resources in your subscription, you must assign a role to the application.
Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
Select the particular subscription to assign the application to.
Open the Access control (IAM) and select Add > Add role assignment to open the Add role assignment page.
Select Owner as the role to assign to your created Azure AD app.
Complete the Authentication
You are ready to connect after setting one of the below connection properties groups, depending on the configured app authentication (client secret or certificate).
In both methods
Before choosing client secret or certicate authentication, follow these steps then continue to the relevant section below:
AuthScheme: Set this to the AzureServicePrincipal in your app settings.
InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
AzureTenant: Set this to the tenant you wish to connect to.
OAuthClientId: Set this to the client Id in your app settings.
Authenticating using a Client Secret
Continue with the following:
OAuthClientId: Set this to the client Id in your app settings.
OAuthClientSecret: Set this to the client secret in your app settings.
Authenticating using a Certificate
Continue with the following:
OAuthJWTCert: Set this to the JWT Certificate store.
OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.
If you are running Microsoft Excel Online on an Azure VM, you can leverage Managed Service Identity (MSI) credentials to connect:
AuthScheme: Set this to AzureMSI.
The MSI credentials are automatically obtained for authentication.
All connections to Google Sheets are authenticated using OAuth. The provider supports using user accounts, service accounts and GCP instance accounts for authentication.
AuthScheme must be set to OAuth in all of the user account flows. For desktop applications, the provider's embedded application is the simplest way to authenticate. The only additional requirement is to set InitiateOAuth to GETANDREFRESH.
When the driver starts, it will open a browser and Google Sheets will request your login information. The provider will use the credentials you provide to access your Google Sheets data. These credentials will be saved and automatically refreshed as needed. For desktop applications, the provider's default application is the simplest way to authenticate. The only additional requirement is to set InitiateOAuth to GETANDREFRESH.
When the driver starts, it will open a browser and Google Sheets will request your login information. The provider will use the credentials you provide to access your Google Sheets data. These credentials will be saved and automatically refreshed as needed.
To authenticate using a service account, you must create a new service account and have a copy of the accounts certificate.
For a JSON file, you will need to set these properties:
AuthScheme: Required. Set this to OAuthJWT.
InitiateOAuth: Required. Set this to GETANDREFRESH.
OAuthJWTCertType: Required. Set this to GOOGLEJSON.
OAuthJWTCert: Required. Set this to the path to the .json file provided by Google.
OAuthJWTSubject: Optional. Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.
For a PFX file, you will need to set these properties instead:
AuthScheme: Required. Set this to OAuthJWT.
InitiateOAuth: Required. Set this to GETANDREFRESH.
OAuthJWTCertType: Required. Set this to PFXFILE.
OAuthJWTCert: Required. Set this to the path to the .pfx file provided by Google.
OAuthJWTCertPassword: Optional. Set this to the .pfx file password. In most cases this will need to be provided since Google encrypts PFX certificates.
OAuthJWTCertSubject: Optional. Set this only if you are using a OAuthJWTCertType which stores multiple certificates. Should not be set for PFX certificates generated by Google.
OAuthJWTIssuer: Required. Set this to the email address of the service account. This address will usually include the domain iam.gserviceaccount.com.
OAuthJWTSubject: Optional. Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.
If you do not already have a service account, you can create one by following procedure:
Follow these steps to enable the Google Sheets API:
Select Library from the left-hand navigation menu. This opens the Library page.
In the search field, enter "Google Sheets API" and select Google Sheets API from the search results.
On the Google Sheets API page, click ENABLE.
When using AuthScheme=OAuth, and you're using a web application, you must create an OAuth Client ID Application. For desktop and headless flows, creating a custom OAuth application is optional.
Follow these steps to create a custom OAuth application:
If you have not done so, follow the steps in the console to create an OAuth consent screen.
Select Credentials from the left-hand navigation menu.
On the Credentials page, select Create Credentials > OAuth Client ID.
In the Application Type menu, select Web application.
Specify a name for your OAuth custom web application.
Under Authorized redirect URIs, click ADD URI and enter a redirect URI. Press Enter.
Click CREATE, which returns you to the Credentials page.
A window opens that displays your client Id and client secret. Although the client secret is accessible from from the Google Cloud Console, we recommend you write down the client secret. You need both the client secret and client Id to specify the OAuthClientId and OAuthClientSecret connection properties.
When using AuthScheme=OAuthJWT, you must create a Service Account Application. Follow these steps:
If you have not done so, follow the steps in the console to create an OAuth consent screen.
Select Credentials from the left-hand navigation menu.
On the Credentials page, select Create Credentials > Service account.
On the Create service account page, enter the Service account name, the Service account ID, and, optionally, a description.
Click DONE. This returns you to the Credentials page.
When running on a GCP virtual machine, the provider can authenticate using a service account tied to the virtual machine. To use this mode, set AuthScheme to GCPInstanceAccount.
All connections to Google Sheets are authenticated using OAuth. The provider supports using user accounts, service accounts, GCP instance accounts, and API keys for authentication.
The provider supports using user accounts, service accounts and GCP instance accounts for authentication.
The following sections discuss the available authentication schemes for Google Sheets:
User Accounts (OAuth)
Service Account (OAuthJWT)
GCP Instance Account
AuthScheme must be set to OAuth in all user account flows.
After setting the following connection properties, you are ready to connect:
InitiateOAuth: Set this to GETANDREFRESH, which instructs the provider to automatically attempt to get and refresh the OAuth access token.
OAuthClientId: (custom applications only) Set this to the Client Id in your custom OAuth application settings.
OAuthClientSecret: (custom applications only) Set this to the Client Secret in the custom OAuth application settings.
When you connect the provider opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The provider then completes the OAuth process as follows:
Extracts the access token from the callback URL.
Obtains a new access token when the old one expires.
Saves OAuth values in OAuthSettingsLocation that persist across connections.
To authenticate using a service account, you must create a new service account and have a copy of the accounts certificate. If you do not already have a service account, you can create one by following the procedure in Creating a Custom OAuth App.
For a JSON file, set these properties:
AuthScheme: Set this to OAuthJWT.
InitiateOAuth: Set this to GETANDREFRESH.
OAuthJWTCertType: Set this to GOOGLEJSON.
OAuthJWTCert: Set this to the path to the .json file provided by Google.
OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.
For a PFX file, set these properties instead:
AuthScheme: Set this to OAuthJWT.
InitiateOAuth: Set this to GETANDREFRESH.
OAuthJWTCertType: Set this to PFXFILE.
OAuthJWTCert: Set this to the path to the .pfx file provided by Google.
OAuthJWTCertPassword: (optional) Set this to the .pfx file password. In most cases you must provide this since Google encrypts PFX certificates.
OAuthJWTCertSubject: (optional) Set this only if you are using a OAuthJWTCertType which stores multiple certificates. Should not be set for PFX certificates generated by Google.
OAuthJWTIssuer: Set this to the email address of the service account. This address will usually include the domain iam.gserviceaccount.com.
OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.
When running on a GCP virtual machine, the provider can authenticate using a service account tied to the virtual machine. To use this mode, set AuthScheme to GCPInstanceAccount.
To connect using API Keys, set the APIKey property, and set AuthScheme to Token.
You can create an API key in the Google Cloud Console by clicking Create credentials > API key. You can restrict the key before using it in production by clicking Restrict key and selecting one of the Restrictions.
Navigate to the .
Navigate to the .
Navigate to the .