ADO.NET Provider for Excel

Build 21.0.8137

Establishing a Connection

Connecting to Excel Data Sources

The CData ADO.NET Provider for 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.

Service provider URI formats InitiateOAuth OAuthClientId OAuthClientSecret OAuthAccessToken OAuthAccessTokenSecret User Password AuthScheme AzureStorageAccount AzureAccessKey AWSAccessKey AWSSecretKey AWSRegion AccessKey SecretKey Region OracleNamespace ProjectId
Local Single File Path (One table)

file://localPath/file.xlsx

Directory Path (one table per file)

file://localPath

HTTP or HTTPS http://remoteStream

https://remoteStream

OPTIONAL OPTIONAL OPTIONAL
Amazon S3 Single File Path (One table)

s3://remotePath/file.xlsx

Directory Path (one table per file)

s3://remotePath

REQUIRED (your AccessKey) REQUIRED (your SecretKey) OPTIONAL
Azure Blob Storage azureblob://mycontainer/myblob/ REQUIRED OPTIONAL OPTIONAL OPTIONAL REQUIRED (for OAuth) REQUIRED REQUIRED (your AccessKey)
Azure Data Lake Store Gen1 adl://remotePath

adl://Account.azuredatalakestore.net@remotePath

REQUIRED OPTIONAL OPTIONAL OPTIONAL REQUIRED REQUIRED
Azure Data Lake Store Gen2 abfs://myfilesystem/remotePath REQUIRED REQUIRED (your AccessKey)
Azure Data Lake Store Gen2 with SSL abfss://myfilesystem/remotePath REQUIRED OPTIONAL OPTIONAL OPTIONAL REQUIRED (for OAuth) REQUIRED REQUIRED (your AccessKey)
Google Drive Single File Path (One table)

gdrive://remotePath/file.xlsx

Directory Path (one table per file)

gdrive://remotePath

REQUIRED OPTIONAL OPTIONAL OPTIONAL OPTIONAL REQUIRED
OneDrive Single File Path (One table)

onedrive://remotePath/file.xlsx

Directory Path (one table per file)

onedrive://remotePath

REQUIRED OPTIONAL OPTIONAL OPTIONAL OPTIONAL REQUIRED
Box Single File Path (One table)

box://remotePath/file.xlsx

Directory Path (one table per file)

box://remotePath

REQUIRED OPTIONAL OPTIONAL OPTIONAL OPTIONAL REQUIRED
Dropbox Single File Path (One table)

dropbox://remotePath/file.xlsx

Directory Path (one table per file)

dropbox://remotePath

REQUIRED OPTIONAL OPTIONAL OPTIONAL OPTIONAL REQUIRED
SharePoint Online SOAP Single File Path (One table)

sp://remotePath/file.xlsx

Directory Path (one table per file)

sp://remotePath

REQUIRED REQUIRED
SharePoint Online REST Single File Path (One table)

sprest://remotePath/file.xlsx

Directory Path (one table per file)

sprest://remotePath

REQUIRED OPTIONAL OPTIONAL OPTIONAL REQUIRED
FTP or FTPS Single File Path (One table)

ftp://server:port/remotePath/file.xlsx

Directory Path (one table per file)

ftp://server:port/remotePath

REQUIRED REQUIRED
SFTP Single File Path (One table)

sftp://server:port/remotePath/file.xlsx

Directory Path (one table per file)

sftp://server:port/remotePath

OPTIONAL OPTIONAL
Wasabi Single File Path (One table)

wasabi://bucket1/remotePath/file.xlsx

Directory Path (one table per file)

wasabi://bucket1/remotePath

REQUIRED (your AccessKey) REQUIRED (your SecretKey) OPTIONAL
Google Cloud Storage Single File Path (One table)

gs://bucket/remotePath/file.xlsx

Directory Path (one table per file)

gs://bucket/remotePath

REQUIRED OPTIONAL OPTIONAL OPTIONAL OPTIONAL REQUIRED REQUIRED
Oracle Cloud Storage Single File Path (One table)

os://bucket/remotePath/file.xlsx

Directory Path (one table per file)

os://bucket/remotePath

REQUIRED (your AccessKey) REQUIRED (your SecretKey) OPTIONAL REQUIRED
Azure File Single File Path (One table)

azurefile://fileShare/remotePath/file.xlsx

Directory Path (one table per file)

azurefile://fileShare/remotePath

REQUIRED
IBM Object Storage Source Single File Path (One table)

ibmobjectstorage://bucket1/remotePath/file.xlsx

Directory Path (one table per file)

ibmobjectstorage://bucket1/remotePath

REQUIRED OPTIONAL REQUIRED REQUIRED (your AccessKey) REQUIRED (your SecretKey) REQUIRED
Hadoop Distributed File System Single File Path (One table)

webhdfs://host:port/remotePath/file.xlsx

Directory Path (one table per file)

webhdfs://host:port/remotePath

Secure Hadoop Distributed File System Single File Path (One table)

webhdfss://host:port/remotePath/file.xlsx

Directory Path (one table per file)

webhdfss://host:port/remotePath

Connecting to Cloud Files

While the provider is capable of pulling data from 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 CSV file stored on SharePoint, you could use the CData SharePoint provider's DownloadDocument procedure to download the CSV file, update the local CSV file with the CData CSV provider, then use the SharePoint provider's UploadDocument procedure to upload the changed file to SharePoint.

Connecting to a Workbook

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.

Querying a Workbook

You can then execute SELECT, INSERT, UPDATE, and DELETE statements to spreadsheets and ranges in the workbook. See Excel Operations for details on querying spreadsheet data as tables.

Connecting to Amazon S3

Set the URI to an Excel file in a bucket. Additionally, set the following properties to authenticate:

  • AWSAccessKey: Set this to an Amazon Web Services Access Key (a username).
  • AWSSecretKey: Set this to an Amazon Web Services Secret Key.
For example:
URI=s3://bucket1/folder1/file.xlsx; AWSAccessKey=token1; AWSSecretKey=secret1; AWSRegion=OHIO;
Optionally, specify AWSRegion in addition.

Note: It is also possible to connect to S3-compatible services by specifying its base StorageBaseURL. For example, if the StorageBaseURL conn prp is set to http://s3.%region%.myservice.com and Region is region-1, then we will generate request URLs like https://s3.region-1.myservice.com/bucket/... (or like https://bucket.s3.region-1.myservice.com/..., if the UseVirtualHosting property is true).

Connecting to Oracle Cloud Object Storage

Set the URI to an Excel file in a bucket. Additionally, set the following properties to authenticate:

  • 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.
For example:
URI=os://bucket/remotePath/; AccessKey=token1; SecretKey=secret1; OracleNamespace=myNamespace; Region=us-ashburn-1;
Optionally, specify Region in addition.

Connecting to Wasabi

Set the URI to an Excel file in a bucket. Additionally, set the following properties to authenticate:

  • AccessKey: Set this to a Wasabi Access Key (a username)
  • SecretKey: Set this to a Wasabi Secret Key.
Optionally, specify Region in addition.

For example:

URI=wasabi://bucket1/folder1/file.xlsx; AccessKey=token1; SecretKey=secret1; Region=OHIO;

Connect to Azure Blob Storage

Set the URI to the name of your container and the name of the blob. Additionally, set the following properties to authenticate:

  • AzureStorageAccount: Set this to the account associated with the Azure blob.
  • AzureAccessKey: Set this to the access key associated with the Azure blob.
For example:
URI=azureblob://mycontainer/myblob/; AzureStorageAccount=myAccount; AzureAccessKey=myKey;

You can also use the OAuth authentication to connect with Azure Blob Storage. For example:

URI=azureblob://mycontainer/myblob/; AzureStorageAccount=myAccount; AuthScheme=AzureAD; InitiateOAuth=GETANDREFRESH;

If you are connecting from an Azure VM with permissions for Azure Blob storage, you can simply use the AzureMSI AuthScheme For example:

URI=azureblob://mycontainer/myblob/; AzureStorageAccount=myAccount; AuthScheme=AzureMSI;

If you would like to authenticate with a service principal instead of a client secret, it is also possible to authenticate with a client certificate.

  • 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.
  • OAuthGrantType: Set this to CLIENT.
  • OAuthClientId: Set this to the Client Id in your app settings.
  • OAuthJWTCert: Set this to the JWT Certificate store.
  • OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

For example:

AuthScheme=AzureServicePrincipal;InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;;AzureTenant=MyAzureTenant;OAuthJWTCert=MyOAuthJWTCert;OAuthJWTCertType=PFXFile

Connect to Azure Data Lake Store Gen 2

Set the URI to the name of the file system, the name of the folder which contacts your Excel files, and the name of an Excel file. Additionally, set the following properties to authenticate:

  • AzureStorageAccount: Set this to the account associated with the Azure data lake store.
  • AzureAccessKey: Set this to the access key associated with the Azure data lake store.
For example:
  URI=abfs://myfilesystem/folder1/file.xlsx; AzureStorageAccount=myAccount; AzureAccessKey=myKey;
  URI=abfss://myfilesystem/folder1/file.xlsx; AzureStorageAccount=myAccount; AzureAccessKey=myKey;

You can also use the OAuth authentication to Connect with Azure Data Lake Store Gen 2. For example:

URI=abfss://myfilesystem/folder1; AzureStorageAccount=myAccount; AuthScheme=AzureAD; InitiateOAuth=GETANDREFRESH;

If you are connecting from an Azure VM with permissions to connect to Azure Data Lake Store Gen 2, you can simply set AuthScheme to AzureMSI. For example:

URI=abfss://myfilesystem/folder1; AzureStorageAccount=myAccount; AuthScheme=AzureMSI;

If you would like to authenticate with a service principal instead of a client secret, it is also possible to authenticate with a client certificate.

  • 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.
  • OAuthGrantType: Set this to CLIENT.
  • OAuthClientId: Set this to the Client Id in your app settings.
  • OAuthJWTCert: Set this to the JWT Certificate store.
  • OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

For example:

AuthScheme=AzureServicePrincipal;InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;;AzureTenant=MyAzureTenant;OAuthJWTCert=MyOAuthJWTCert;OAuthJWTCertType=PFXFile

Connect to Azure File Storage

Set the URI to the name of your azure file share and the name of the resource. Additionally, set the following properties to authenticate:

  • AzureStorageAccount (Required): Set this to the account associated with the Azure file.
  • 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.
For example:
URI=azurefile://fileShare/remotePath/file.xlsx; AzureStorageAccount=myAccount; AzureAccessKey=myAccessKey;

URI=azurefile://fileShare/remotePath/file.xlsx; AzureStorageAccount=myAccount; AzureSharedAccessSignature=mySharedSignature;

Connecting to Box

Set the URI to the path to a Excel file. To authenticate to Box, use the OAuth authentication standard. See Connecting to Box for an authentication guide.

For example:

URI=box://folder1/file.xlsx; InitiateOAuth=GETANDREFRESH; OAuthClientId=oauthclientid1; OAuthClientSecret=oauthcliensecret1; CallbackUrl=http://localhost:12345;

Connecting to Dropbox

Set the URI to the path to a Excel file. To authenticate to Dropbox, use the OAuth authentication standard. See Connecting to Dropbox for an authentication guide. You can authenticate with a user account or a service account. In the user account flow, you do not need to set any connection properties for your user credentials, as shown in the connection string below:

URI=dropbox://folder1/file.xlsx; InitiateOAuth=GETANDREFRESH; OAuthClientId=oauthclientid1; OAuthClientSecret=oauthcliensecret1; CallbackUrl=http://localhost:12345;

Connecting to Google Drive

Set the URI to the path to the name of the file system, the name of the folder which contacts your Excel files, and the name of an Excel file. To access shared files, set SharedWithMe as the name of the folder which contains your Excel files. For example URI=gdrive://SharedWithMe/remotePath. To authenticate to Google APIs, use the OAuth authentication standard. You can authorize the provider to connect to Google APIs on behalf of individual users or on behalf of a domain. See Connecting to Google Drive for a guide.

For example:

URI=gdrive://folder1/file.xlsx;InitiateOAuth=GETANDREFRESH;

Connecting to IBM Object Storage Source

Set the URI to an Excel file in a bucket. Additionally, set the following properties to authenticate:

  • AccessKey: Set this to an IBM Access Key (a username).
  • SecretKey: Set this to an IBM Secret Key.
For example:
URI=ibmobjectstorage://bucket1/folder1; AccessKey=token1; SecretKey=secret1; Region=eu-gb;
Optionally, specify Region in addition.

You can also authenticate to your IBM Object Storage instance using OAuth AuthScheme:
  • AuthScheme: Set this to OAuth.
  • ApiKey: Set this to your IBM API Key.
  • Region: Set this property to your IBM instance region.
For example:
URI=ibmobjectstorage://bucket1/folder1; ApiKey=key1; Region=eu-gb; AuthScheme=OAuth; InitiateOAuth=GETANDREFRESH;

Connecting to HDFS

There are two authentication methods available for connecting to HDFS data source, Anonymous Authentication and Negotiate (Kerberos) Authentication.

Anonymous Authentication

In some situations, HDFS may be connected to without any authentication connection properties. To do so, simply set the AuthScheme to None (default).

Authenticate using Kerberos

When authentication credentials are required, authentication over Kerberos may be used. Please see Using Kerberos for details on how to authenticate with Kerberos.

Connecting to SharePoint Online SOAP

Set the URI to a document library containing Excel files. To authenticate, set User and Password and StorageBaseURL.

For example:

URI=sp://Documents/folder1; User=user1; Password=password1; StorageBaseURL=https://subdomain.sharepoint.com;

Note that this connection method may not work if the StorageBaseURL ends with "-my.sharepoint.com". You should use the onedrive:// scheme when connecting to these sites because they do not support the components that of SharePoint that the provider needs to download files.

Connecting to SharePoint Online REST

Set the URI to a document library containing Excel files. StorageBaseURL is optional. If not provided, the driver will work with the root drive. To authenticate, use the OAuth authentication standard.

For example:

URI=sp://Documents/folder1; InitiateOAuth=GETANDREFRESH; StorageBaseURL=https://subdomain.sharepoint.com;

Note that this connection method may not work if the StorageBaseURL ends with "-my.sharepoint.com". You should use the onedrive:// scheme when connecting to these sites because they do not support the components that of SharePoint that the provider needs to download files.

Connecting to FTP

Set the URI to the address of the server followed by the path to the Excel file. To authenticate, set User and Password.

For example:

URI=ftps://localhost:990/folder1/file.xlsx; User=user1; Password=password1; 

Connecting to Google Cloud Storage

Set the URI to the path to the name of the file system, the name of the folder which contains your Excel files, and the name of a Excel file. To authenticate to Google APIs, provide a ProjectId.

For example:

URI=gs://bucket/remotePath/; ProjectId=PROJECT_ID; 

Copyright (c) 2022 CData Software, Inc. - All rights reserved.
Build 21.0.8137