ODBC Driver for Microsoft Excel

Connection String Parameters - ODBC Driver for Microsoft Excel

Microsoft Excel ODBC Connection String Parameters

The following table lists the connection string parameters for Microsoft Excel.

Parameter	Description
`Database`	The full path to the database to connect to.
Advanced Settings
`Allow NULL strings`	To retrieve metadata, not all parameters according to MSDN can accept a null value. If `NULL`, the driver should return an error. But some 3rd-party tools pass `NULL` to the parameters. These options should be enabled for compatibility with such tools.
`Empty strings as NULL`
`Default Cell Type`	Specifies what string data types the driver uses for literal columns. The default value is `String`. `String` - all literal columns will be returned as `SQL_VARCHAR or SQL_WVARCHAR`. `Memo` - all literal columns will be returned as `SQL_LONGVARCHAR or SQL_WLONGVARCHAR`.
`Describe Amount`	Specifies the number of sample worksheet rows used to create a list of columns in the corresponding table. The default value is `25`.
`Exclusive Lock`	If set to `True`, this parameter enforces a single-user access mode for the workbook. Even with `ReadOnly` set to `True`, only one user is able to access the workbook at a time. The default value is `False`.
`Header Row`	Indicates whether the data in the very first worksheet row should be treated as column names. If set to `True`, then each column value represents the corresponding table column name. The default value is `False`.
`Hide RowId`	Specifies whether the driver should hide the `ROWID` pseudo-column. The default value is `True`.
`Include Empty Rows`	Indicates whether to inclued empty lines in the worksheet or skip them while reading data. This option is enabled by default.
`Read Only`	By default, Microsoft Excel is a single-user system. The connector implements a read-only multi-user mode to enable several users to read data from the workbook simultaneously. The possible values are `True` and `False`. The default value is `False`. By setting `ReadOnly` to `True`, two or more users are able to access the database simultaneously, however only in the `ReadOnly` mode.
`ODBC Behavior`	Used to set the behavior corresponding to the ODBC specification version that a third-party tool expects. The behavior of ODBC driver can be changed by setting a value for the `SQL_ATTR_ODBC_VERSION` attribute by calling the `SQLSetEnvAttr` function. But some third-party tools expect the driver to exhibit ODBC 2.x behavior, but forget to call `SQLSetEnvAttr` with the specified version or pass an incorrect value there. In this case, the required behavior can be explicitly specified in the `Connection String` by setting the ODBC Behavior parameter. The possible values are: `Default` - default ODBC behavior determined by a third-party tool. `Ver 2.x` - ODBC 2.x behavior is explicitly set. `Ver 3.x` - ODBC 3.x behavior is explicitly set.
`RegionalNumberSettings`	Enables the use of local regional settings when converting numbers to strings.
`RegionalDateTimeSettings`	Enables the use of local regional settings when converting dates and times to strings.
`String Types`	Sets the string value types returned by the driver as `Default`, `Ansi` or `Unicode`. `Default` - the driver defines the string types. `Ansi` - all string types will be returned as `SQL_CHAR, SQL_VARCHAR and SQL_LONGVARCHAR`. `Unicode` - all string types will be returned as `SQL_WCHAR, SQL_WVARCHAR and SQL_WLONGVARCHAR`. The parameter value should be changed if any third-party tool supports only `Ansi` string types or `Unicode` ones.

Microsoft Excel ODBC Connection String sample

DRIVER=Devart ODBC Driver for Microsoft Excel;Database=fullpathtoyourdatabase

© 2015-2024 Devart. All Rights Reserved.

Request Support

Provide Feedback