CData Cloud offers access to Microsoft Excel Online across several standard services and protocols, in a cloud-hosted solution. Any application that can connect to a MySQL or SQL Server database can connect to Microsoft Excel Online through CData Cloud.
CData Cloud allows you to standardize and configure connections to Microsoft Excel Online as though it were any other OData endpoint, or standard SQL Server/MySQL database.
This page provides a guide to Establishing a Connection to Microsoft Excel Online in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.
Establishing a Connection shows how to authenticate to Microsoft Excel Online and configure any necessary connection properties to create a database in CData Cloud
Accessing data from Microsoft Excel Online through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.
Connect to Microsoft Excel Online by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.
There are two authentication methods available for connecting to Microsoft Excel Online data source:
Azure AD is user-based authentication. Credentials are supplied to connect AzureAD 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 Azure AD application. The creation of a Custom Azure AD application establishes the OAuth credentials OAuthClientId and OAuthClientSecret. For a discussion of how to create a custom Azure AD application, and reasons why that might be advantageous even if you are not connecting via a Web application, see Creating a Custom OAuth Application.
The authentication as an Azure 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 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.
Create an AzureAD App and an Azure Service Principal
When authenticating using an Azure Service Principal, you must create and register an Azure AD application with an Azure AD tenant. See Creating a Custom OAuth 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.
Client Secret
Set these connection properties:
Certificate
Set these connection properties:
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.
If you are running Microsoft Excel Online on an Azure VM and want to leverage MSI to connect, set AuthScheme to AzureMSI.
When your VM has multiple user-assigned managed identities, you must also specify OAuthClientId.
The Cloud 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:
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:
For information on how to execute data manipulation SQL against worksheets and ranges, see:
For details on how the Cloud models worksheets and cells as tables and columns, See Data Model.
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.
The following connection properties enable you to execute SELECT queries that closely match how your sheet is organized.
SELECT Id, Name, Item, Quantity, Amount FROM Test_xlsx_Sheet1 WHERE Amount = 50The 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 A, B, C, D FROM Test_xlsx_Sheet1 WHERE D = 50
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.
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 Cloud 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.
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)
When the Header property is set to false, provide the alphabetical column names.
INSERT INTO Test_xlsx_Sheet1 (A, B) VALUES ('Test', 10)
By default, the Cloud 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.
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.
Insert the rows you need in a temporary table. Then insert all the rows from that temporary table into the actual table.
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 Cloud 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.
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
When the Header is set to false, provide the alphabetical column names:
UPDATE Customers SET H='value' WHERE Id=7
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'
By default, the Cloud 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.
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.
Set the following properties to configure formula evaluation when executing SELECT, INSERT, and UPDATE statements.
The Cloud can return either the result of the formula or the formula itself; configure this behavior with the ValueRenderOption connection property.
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)')
Note: If AllowFormula is set to true, every column containing a formula will be exposed as String type regardless of the set TypeDetectionScheme.
By default, the Cloud attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert property for the available formats to do so.
To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.
In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.
Set the following properties:
The Cloud models the worksheets and ranges in your workbooks as bidirectional tables. When you connect, the Cloud 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 Cloud behavior.
This section provides more detail about how the Cloud models the Tables. See Columns for details about column discovery.
Query the available System Tables to access the available Cloud metadata, including schema information.
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.
The Cloud 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 Cloud.
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:
You can use the Drives and SharePointSites views to view all the sites and drives you have access to.
If you would like to filter the schemas the driver exposes, you can 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 Cloud for the drives.
The Cloud enables you to represent a top-left-oriented worksheet or a user-specified range as a database table.
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
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.
See the following to execute data manipulation SQL to worksheets and ranges:
When you connect, the Cloud 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.
You can set Header to detect column names in a header row or you can reference columns alphabetically.
Below are more specifics on how the Cloud detects column names from a header row when Header is set to true.
When Header is set to false, alphabetical column names will be dynamically assigned.
The Cloud 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.
The ValueRenderOption and ValueInputOption connection properties can be used to configure how the Cloud parses the Microsoft Excel Online cell formats in SELECT, INSERT and UPDATE operations.
Set the ValueRenderOption to configure how the Cloud renders values in the output of a SELECT operation. The available value render options are as below.
Set the ValueInputOption to configure how the Cloud parses values the user submits to the Microsoft Excel Online source for INSERT and UPDATE operations. The available value input options are as below.
Set the TypeDetectionScheme to configure the data type detection method. The available type detection schemes are as below.
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.
| Name | Description |
| Drives | Retrieve a list of drive objects. |
| 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. |
| SharePointSites | Retrieve the list of all sites in your SharePoint account. |
| Workbooks | Retrieve a list of workbook objects. |
| 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. |
Retrieve a list of drive objects.
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'
| Name | Type | References | Description |
| Id [KEY] | String | The unique identifier of the drive. | |
| SiteId | String | The ID of the SharePoint site or subsite that this drive belongs to. | |
| ListId | String | The ID of the list this drive corresponds to. | |
| Name | String | The name of the drive. | |
| Description | String | The description of the drive. | |
| CreatedByName | String | The display name of the identity. | |
| CreatedById | String | Unique identifier for the identity. | |
| CreatedDateTime | Datetime | Date and time the drive was created. | |
| LastModifiedDateTime | Datetime | Date and time the drive was last modified. | |
| WebUrl | String | URL that displays the resource in the browser. |
Retrieve a list of workbook objects.
| Name | Type | References | Description |
| Id [KEY] | String | The unique identifier of the item within the Drive. | |
| Name | String | The name of the item. | |
| CreatedByName | String | The display name of the identity. | |
| CreatedById | String | Unique identifier for the identity. | |
| CreatedDateTime | Datetime | Date and time the item was created. | |
| LastModifiedDateTime | Datetime | Date and time the item was last modified. | |
| Size | Long | Size of the item in bytes. | |
| WebUrl | String | URL that displays the resource in the browser. | |
| DriveId | String |
Drives.Id | The ID of the drive that this workbook belongs to. |
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.
| Name | Type | References | Description |
| Id [KEY] | String | Returns a value that uniquely identifies the worksheet in a given workbook. The value of the identifier remains the same even when the worksheet is renamed or moved. | |
| WorkbookId | String |
Workbooks.Id | The Id of the workbook containing this worksheet. |
| Name | String | The display name of the worksheet. | |
| Position | Integer | The zero-based position of the worksheet within the workbook. | |
| Visibility | String | The visibility of the worksheet. Possible values are: Visible, Hidden, VeryHidden. |
Stored procedures are function-like interfaces that extend the functionality of the Cloud 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.
| Name | Description |
| AddWorksheet | Add a worksheet to an existing Excel Online workbook. |
| 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. |
Add a worksheet to an existing Excel Online workbook.
| Name | Type | Description |
| WorkbookId | String | The Id of the workbook. Must belong to drive accessible with current connection properties. |
| Title | String | The name of the worksheet. |
| Name | Type | Description |
| Success | String | This value shows whether the operation was successful or not. |
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.
| Name | Type | Description |
| CallbackUrl | String | The URL the user will be redirected to after authorizing your application. This value must match the Reply URL in the Azure AD app settings. |
| State | String | The same value for state that you sent when you requested the authorization code. |
| Scope | String | A space-separated list of scopes to request from the Admin. Please check the Microsoft Graph API documentation for a list of available permissions. |
| Name | Type | Description |
| URL | String | The authorization URL, entered into a Web browser by the Admin to grant permisssions for your app. |
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
The following tables return database metadata for Microsoft Excel Online:
The following tables return information about how to connect to and query the data source:
The following table returns query statistics for data modification queries, including batch operations::
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
| Name | Type | Description |
| CatalogName | String | The database name. |
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
| Name | Type | Description |
| CatalogName | String | The database name. |
| SchemaName | String | The schema name. |
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
| Name | Type | Description |
| CatalogName | String | The database containing the table or view. |
| SchemaName | String | The schema containing the table or view. |
| TableName | String | The name of the table or view. |
| TableType | String | The table type (table or view). |
| Description | String | A description of the table or view. |
| IsUpdateable | Boolean | Whether the table can be updated. |
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'
| Name | Type | Description |
| CatalogName | String | The name of the database containing the table or view. |
| SchemaName | String | The schema containing the table or view. |
| TableName | String | The name of the table or view containing the column. |
| ColumnName | String | The column name. |
| DataTypeName | String | The data type name. |
| DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
| Length | Int32 | The storage size of the column. |
| DisplaySize | Int32 | The designated column's normal maximum width in characters. |
| NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
| NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
| IsNullable | Boolean | Whether the column can contain null. |
| Description | String | A brief description of the column. |
| Ordinal | Int32 | The sequence number of the column. |
| IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
| IsGeneratedColumn | String | Whether the column is generated. |
| IsHidden | Boolean | Whether the column is hidden. |
| IsArray | Boolean | Whether the column is an array. |
| IsReadOnly | Boolean | Whether the column is read-only. |
| IsKey | Boolean | Indicates whether a field returned from sys_tablecolumns is the primary key of the table. |
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
| Name | Type | Description |
| CatalogName | String | The database containing the stored procedure. |
| SchemaName | String | The schema containing the stored procedure. |
| ProcedureName | String | The name of the stored procedure. |
| Description | String | A description of the stored procedure. |
| ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
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
| Name | Type | Description |
| CatalogName | String | The name of the database containing the stored procedure. |
| SchemaName | String | The name of the schema containing the stored procedure. |
| ProcedureName | String | The name of the stored procedure containing the parameter. |
| ColumnName | String | The name of the stored procedure parameter. |
| Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
| DataTypeName | String | The name of the data type. |
| DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
| Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
| NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
| NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
| IsNullable | Boolean | Whether the parameter can contain null. |
| IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
| IsArray | Boolean | Whether the parameter is an array. |
| Description | String | The description of the parameter. |
| Ordinal | Int32 | The index of the parameter. |
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'
| Name | Type | Description |
| CatalogName | String | The name of the database containing the key. |
| SchemaName | String | The name of the schema containing the key. |
| TableName | String | The name of the table containing the key. |
| ColumnName | String | The name of the key column. |
| IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
| IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
| PrimaryKeyName | String | The name of the primary key. |
| ForeignKeyName | String | The name of the foreign key. |
| ReferencedCatalogName | String | The database containing the primary key. |
| ReferencedSchemaName | String | The schema containing the primary key. |
| ReferencedTableName | String | The table containing the primary key. |
| ReferencedColumnName | String | The column name of the primary key. |
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'
| Name | Type | Description |
| CatalogName | String | The name of the database containing the key. |
| SchemaName | String | The name of the schema containing the key. |
| TableName | String | The name of the table containing the key. |
| ColumnName | String | The name of the key column. |
| PrimaryKeyName | String | The name of the primary key. |
| ForeignKeyName | String | The name of the foreign key. |
| ReferencedCatalogName | String | The database containing the primary key. |
| ReferencedSchemaName | String | The schema containing the primary key. |
| ReferencedTableName | String | The table containing the primary key. |
| ReferencedColumnName | String | The column name of the primary key. |
| ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
Describes the primary keys.
The following query retrieves the primary keys from all tables and views:
SELECT * FROM sys_primarykeys
| Name | Type | Description |
| CatalogName | String | The name of the database containing the key. |
| SchemaName | String | The name of the schema containing the key. |
| TableName | String | The name of the table containing the key. |
| ColumnName | String | The name of the key column. |
| KeySeq | String | The sequence number of the primary key. |
| KeyName | String | The name of the primary key. |
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'
| Name | Type | Description |
| CatalogName | String | The name of the database containing the index. |
| SchemaName | String | The name of the schema containing the index. |
| TableName | String | The name of the table containing the index. |
| IndexName | String | The index name. |
| ColumnName | String | The name of the column associated with the index. |
| IsUnique | Boolean | True if the index is unique. False otherwise. |
| IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
| Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
| SortOrder | String | The sort order: A for ascending or D for descending. |
| OrdinalPosition | Int16 | The sequence number of the column in the index. |
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:excelonline:config:
This connection string enables you to query this table without a valid connection.
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 <> ''
| Name | Type | Description |
| Name | String | The name of the connection property. |
| ShortDescription | String | A brief description. |
| Type | String | The data type of the connection property. |
| Default | String | The default value if one is not explicitly set. |
| Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
| Value | String | The value you set or a preconfigured default. |
| Required | Boolean | Whether the property is required to connect. |
| Category | String | The category of the connection property. |
| IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
| Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
| PropertyName | String | A camel-cased truncated form of the connection property name. |
| Ordinal | Int32 | The index of the parameter. |
| CatOrdinal | Int32 | The index of the parameter category. |
| Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
| Visible | Boolean | Informs whether the property is visible in the connection UI. |
| ETC | String | Various miscellaneous information about the property. |
Describes the SELECT query processing that the Cloud can offload to the data source.
See SQL Compliance for SQL syntax details.
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.
| Name | Description | Possible Values |
| AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG, COUNT, MAX, MIN, SUM, DISTINCT |
| COUNT | Whether COUNT function is supported. | YES, NO |
| IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
| IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
| SUPPORTED_OPERATORS | A list of supported SQL operators. | =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR |
| GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE |
| OJ_CAPABILITIES | The supported varieties of outer joins supported. | NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS |
| OUTER_JOINS | Whether outer joins are supported. | YES, NO |
| SUBQUERIES | Whether subqueries are supported, and, if so, the degree of support. | NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED |
| STRING_FUNCTIONS | Supported string functions. | LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE |
| NUMERIC_FUNCTIONS | Supported numeric functions. | ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE |
| TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT |
| REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
| REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
| IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
| SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES, NO |
| DIALECT | Indicates the SQL dialect to use. | |
| KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
| SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES, NO |
| SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES, NO |
| DATASYNCVERSION | The CData Data Sync version needed to access this driver. | Standard, Starter, Professional, Enterprise |
| DATASYNCCATEGORY | The CData Data Sync category of this driver. | Source, Destination, Cloud Destination |
| SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE, FALSE |
| SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES, NO |
| SQL_CAP | All supported SQL capabilities for this driver. | SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX |
| PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
| ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES, NO |
| PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
| MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE, FALSE |
| REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
| REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
| REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
| REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
| REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
| IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
| CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE, FALSE |
| CREATE_SCHEMA_PROCEDURES | Indicates stored procedures that can be used for generating schema files. |
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.
| Name | Type | Description |
| NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
| VALUE | String | Detail on the supported SQL or SQL syntax. |
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
| Name | Type | Description |
| Id | String | The database-generated Id returned from a data modification operation. |
| Batch | String | An identifier for the batch. 1 for a single operation. |
| Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
| Message | String | SUCCESS or an error message if the update in the batch failed. |
The connection string 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 in the connection string for this provider. Click the links for further details.
For more information on establishing a connection, see Establishing a Connection.
| Property | Description |
| AuthScheme | The type of authentication to use when connecting to Microsoft Excel Online. |
| UseSandbox | A boolean indicating if you are using a sandbox account. The provider makes requests to the production environment by default. |
| Property | Description |
| DefineTables | Assign table names to ranges. |
| Workbook | The name or Id of the workbook. |
| Drive | The Id of the drive. |
| IncludeSharePointSites | Whether to retrieve drives for all SharePoint sites when querying Drives view. If 'true' the provider will retrieve all Site IDs recursively and for each of them issue a separate call to get their drives. Therefore, be aware that setting this property to 'true' may decrease performance for the Drives view. Note that the SharePointAccessToken or OAuthJWTCert connection property must be specified to query the SharePointSites view and other views if IncludeSharePointSites is set to 'true' when using the CLIENT OAuthGrantType or the AzureServicePrincipal AuthScheme. |
| Property | Description |
| AzureTenant | The Microsoft Online tenant being used to access data. If not specified, your default tenant is used. |
| AzureEnvironment | The Azure Environment to use when establishing a connection. |
| Property | Description |
| OAuthClientId | The client Id assigned when you register your application with an OAuth authorization server. |
| OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
| State | Optional value for representing extra OAuth state information. |
| OAuthGrantType | The grant type for the OAuth flow. |
| Property | Description |
| OAuthJWTCert | The JWT Certificate store. |
| OAuthJWTCertType | The type of key store containing the JWT Certificate. |
| OAuthJWTCertPassword | The password for the OAuth JWT certificate. |
| OAuthJWTCertSubject | The subject of the OAuth JWT certificate. |
| OAuthJWTIssuer | The issuer of the Java Web Token. |
| OAuthJWTSubject | The user subject for which the application is requesting delegated access. |
| Property | Description |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
| Property | Description |
| Verbosity | The verbosity level that determines the amount of detail included in the log file. |
| Property | Description |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
| Property | Description |
| AllowFormula | Whether or not to treat values starting with an equals (=) sign as formulas during inserts and updates. |
| Header | Indicates whether or not the provider should detect column names from the first row. |
| MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
| Pagesize | The maximum number of results to return per page from Microsoft Excel Online when UsePagination is True. |
| PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
| RowScanDepth | The maximum number of rows to scan to look for the columns available in a table. |
| SharepointURL | The base URL of your Sharepoint Server. |
| ShowSharedDocuments | Whether or not to show shared documents. |
| Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
| TypeDetectionScheme | Determines how to determine the data types of columns. |
| UsePagination | Whether or not the CData ADO.NET Provider for Microsoft Excel Online should use client side paging. |
| UseSimpleNames | Boolean determining if simple names should be used for tables and columns. |
| ValueInputOption | Determines how the provider parses values the user submits to Microsoft Excel Online. |
| ValueRenderOption | Determines how the provider renders values in the output. |
This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.
| Property | Description |
| AuthScheme | The type of authentication to use when connecting to Microsoft Excel Online. |
| UseSandbox | A boolean indicating if you are using a sandbox account. The provider makes requests to the production environment by default. |
The type of authentication to use when connecting to Microsoft Excel Online.
string
"AzureAD"
A boolean indicating if you are using a sandbox account. The provider makes requests to the production environment by default.
bool
false
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.
This section provides a complete list of the Connection properties you can configure in the connection string for this provider.
| Property | Description |
| DefineTables | Assign table names to ranges. |
| Workbook | The name or Id of the workbook. |
| Drive | The Id of the drive. |
| IncludeSharePointSites | Whether to retrieve drives for all SharePoint sites when querying Drives view. If 'true' the provider will retrieve all Site IDs recursively and for each of them issue a separate call to get their drives. Therefore, be aware that setting this property to 'true' may decrease performance for the Drives view. Note that the SharePointAccessToken or OAuthJWTCert connection property must be specified to query the SharePointSites view and other views if IncludeSharePointSites is set to 'true' when using the CLIENT OAuthGrantType or the AzureServicePrincipal AuthScheme. |
Assign table names to ranges.
string
""
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=[CData].[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
The name or Id of the workbook.
string
""
Specifies the name or Id of the workbook. A list of all workbooks is available from the Workbooks view.
The Id of the drive.
string
""
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.
This section provides a complete list of the Azure Authentication properties you can configure in the connection string for this provider.
| Property | Description |
| AzureTenant | The Microsoft Online tenant being used to access data. If not specified, your default tenant is used. |
| AzureEnvironment | The Azure Environment to use when establishing a connection. |
The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.
string
""
The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory Id in the Azure Portal > Azure Active Directory > Properties.
Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.
The AzureTenant is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.
The Azure Environment to use when establishing a connection.
string
"GLOBAL"
In most cases, leaving the environment set to global will work. However, if your Azure Account has been added to a different environment, the AzureEnvironment may be used to specify which environment. The available values are GLOBAL, CHINA, USGOVT, USGOVTDOD.
This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.
| Property | Description |
| OAuthClientId | The client Id assigned when you register your application with an OAuth authorization server. |
| OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
| State | Optional value for representing extra OAuth state information. |
| OAuthGrantType | The grant type for the OAuth flow. |
The client Id assigned when you register your application with an OAuth authorization server.
string
""
As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
The client secret assigned when you register your application with an OAuth authorization server.
string
""
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.
Optional value for representing extra OAuth state information.
string
""
Optional value for representing extra OAuth state information.
The grant type for the OAuth flow.
string
"CODE"
The following options are available: CODE,CLIENT,PASSWORD
This section provides a complete list of the JWT OAuth properties you can configure in the connection string for this provider.
| Property | Description |
| OAuthJWTCert | The JWT Certificate store. |
| OAuthJWTCertType | The type of key store containing the JWT Certificate. |
| OAuthJWTCertPassword | The password for the OAuth JWT certificate. |
| OAuthJWTCertSubject | The subject of the OAuth JWT certificate. |
| OAuthJWTIssuer | The issuer of the Java Web Token. |
| OAuthJWTSubject | The user subject for which the application is requesting delegated access. |
The JWT Certificate store.
string
""
The name of the certificate store for the client certificate.
The OAuthJWTCertType field specifies the type of the certificate store specified by OAuthJWTCert. If the store is password protected, specify the password in OAuthJWTCertPassword.
OAuthJWTCert is used in conjunction with the OAuthJWTCertSubject field in order to specify client certificates. If OAuthJWTCert has a value, and OAuthJWTCertSubject is set, a search for a certificate is initiated. Please refer to the OAuthJWTCertSubject field for details.
Designations of certificate stores are platform-dependent.
The following are designations of the most common User and Machine certificate stores in Windows:
| 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).
The type of key store containing the JWT Certificate.
string
"PEMKEY_BLOB"
This property can take one of the following values:
| USER | For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: This store type is not available in Java. |
| MACHINE | For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java. |
| PFXFILE | The certificate store is the name of a PFX (PKCS12) file containing certificates. |
| PFXBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format. |
| JKSFILE | The certificate store is the name of a Java key store (JKS) file containing certificates. Note: this store type is only available in Java. |
| JKSBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in Java key store (JKS) format. Note: this store type is only available in Java. |
| PEMKEY_FILE | The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate. |
| PEMKEY_BLOB | The certificate store is a string (base64-encoded) that contains a private key and an optional certificate. |
| PUBLIC_KEY_FILE | The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate. |
| PUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate. |
| SSHPUBLIC_KEY_FILE | The certificate store is the name of a file that contains an SSH-style public key. |
| SSHPUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains an SSH-style public key. |
| P7BFILE | The certificate store is the name of a PKCS7 file containing certificates. |
| PPKFILE | The certificate store is the name of a file that contains a PPK (PuTTY Private Key). |
| XMLFILE | The certificate store is the name of a file that contains a certificate in XML format. |
| XMLBLOB | The certificate store is a string that contains a certificate in XML format. |
The password for the OAuth JWT certificate.
string
""
If the certificate store is of a type that requires a password, this property is used to specify that password in order to open the certificate store.
The subject of the OAuth JWT certificate.
string
"*"
When loading a certificate the subject is used to locate the certificate in the store.
If an exact match is not found, the store is searched for subjects containing the value of the property.
If a match is still not found, the property is set to an empty string, and no certificate is selected.
The special value "*" picks the first certificate in the certificate store.
The certificate subject is a comma separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, [email protected]". Common fields and their meanings are displayed below.
| Field | Meaning |
| CN | Common Name. This is commonly a host name like www.server.com. |
| O | Organization |
| OU | Organizational Unit |
| L | Locality |
| S | State |
| C | Country |
| E | Email Address |
If a field value contains a comma it must be quoted.
The issuer of the Java Web Token.
string
""
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.
The user subject for which the application is requesting delegated access.
string
""
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.
This section provides a complete list of the SSL properties you can configure in the connection string for this provider.
| Property | Description |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
The certificate to be accepted from the server when connecting using TLS/SSL.
string
""
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:
| Description | Example |
| A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
| A path to a local file containing the certificate | C:\cert.cer |
| The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
| The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
| The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
This section provides a complete list of the Logging properties you can configure in the connection string for this provider.
| Property | Description |
| Verbosity | The verbosity level that determines the amount of detail included in the log file. |
The verbosity level that determines the amount of detail included in the log file.
string
"1"
The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.
This section provides a complete list of the Schema properties you can configure in the connection string for this provider.
| Property | Description |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
string
""
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.
| Property | Description |
| AllowFormula | Whether or not to treat values starting with an equals (=) sign as formulas during inserts and updates. |
| Header | Indicates whether or not the provider should detect column names from the first row. |
| MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
| Pagesize | The maximum number of results to return per page from Microsoft Excel Online when UsePagination is True. |
| PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
| RowScanDepth | The maximum number of rows to scan to look for the columns available in a table. |
| SharepointURL | The base URL of your Sharepoint Server. |
| ShowSharedDocuments | Whether or not to show shared documents. |
| Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
| TypeDetectionScheme | Determines how to determine the data types of columns. |
| UsePagination | Whether or not the CData ADO.NET Provider for Microsoft Excel Online should use client side paging. |
| UseSimpleNames | Boolean determining if simple names should be used for tables and columns. |
| ValueInputOption | Determines how the provider parses values the user submits to Microsoft Excel Online. |
| ValueRenderOption | Determines how the provider renders values in the output. |
Whether or not to treat values starting with an equals (=) sign as formulas during inserts and updates.
bool
true
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)')
Note: If True, every column containing a formula will be exposed as String type regardless of the set TypeDetectionScheme.
Indicates whether or not the provider should detect column names from the first row.
bool
true
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 Cloud will follow the additional specifications below to detect column headers:
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
int
-1
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
The maximum number of results to return per page from Microsoft Excel Online when UsePagination is True.
int
-1
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.
This property indicates whether or not to include pseudo columns as columns to the table.
string
""
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
The maximum number of rows to scan to look for the columns available in a table.
int
50
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.
The value in seconds until the timeout error is thrown, canceling the operation.
int
60
If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout expires and the operation is not yet complete, the Cloud throws an exception.
Determines how to determine the data types of columns.
string
"RowScan"
The Cloud can detect data types with the cell format (number, currency, date, etc.) or by scanning rows.
| None | Setting TypeDetectionScheme to None will return all columns as the string type. |
| RowScan | Setting TypeDetectionScheme to RowScan will scan rows to heuristically determine the data type. The RowScanDepth determines the number of rows the Cloud will scan. |
| ColumnFormat | Setting TypeDetectionScheme to ColumnFormat will report the data type based on the cell format of the column.
Note: 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. |
Whether or not the CData ADO.NET Provider for Microsoft Excel Online should use client side paging.
bool
false
Whether or not the CData Cloud should use client side paging. We suggest you set this to true in case of a large dataset.
Boolean determining if simple names should be used for tables and columns.
bool
false
Microsoft Excel Online tables and columns can use special characters in names that are normally not allowed in standard databases. UseSimpleNames makes the Cloud easier to use with traditional database tools.
Setting UseSimpleNames to true will simplify the names of tables and columns returned. It will enforce a naming scheme such that only alphanumeric characters and the underscore are valid for the displayed table and column names. Any nonalphanumeric characters will be converted to an underscore.
Determines how the provider parses values the user submits to Microsoft Excel Online.
string
"UserEntered"
Determines how the Cloud parses values the user submits to Microsoft Excel Online.
| Raw | The values the user has entered will not be parsed and will be stored as-is. |
| 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 Excel Online UI. |
Determines how the provider renders values in the output.
string
"FormattedValue"
Determines how the Cloud renders values in the output.
| FormattedValue | Values will be calculated and formatted in the reply according to the cell's formatting. Formatting is based on the workbook's locale, not the requesting user's locale. For example, if A1 is 1.23 and A2 is "=A1" and formatted as currency, then A2 would return "$1.23". |
| UnformattedValue | Values will be calculated but not formatted in the reply. For example, if A1 is 1.23 and A2 is "=A1" and formatted as currency, then A2 would return the number 1.23. |
| Formula | Values will not be calculated and instead the reply will include the formulas. For example, if A1 is 1.23 and A2 is "=A1" and formatted as currency, then A2 would return "=A1". For this reason, every column containing a formula will be exposed as String type regardless of the set TypeDetectionScheme. |