UniDAC Using Microsoft Excel data access provider with UniDAC in Delphi

This article provides a brief overview of the Microsoft Excel data access provider for UniDAC used to establish a connection to Excel workbooks from Delphi and Lazarus. You will find the description of some useful features and how to get started quickly.

• Overview
• Compatibility
• Requirements
• Deployment
• Excel-specific options
  • TUniConnection
  • TUniSQL
  • TUniQuery, TUniTable, TUniStoredProc
  • TUniScript
  • TUniLoader
  • TUniDump
• Connecting in Direct mode
• Data Type Mapping

Overview

Excel provider is based on the ODBC provider. It uses Microsoft Excel ODBC driver to work with a workbook. Main features of the Excel data access provider are as follows:

• High performance
• Easy deployment

The full list of the Excel provider features can be found on the UniDAC features page.

Both Professional and Standard Editions of UniDAC include the Excel provider. Express Edition of UniDAC does not include the Excel provider.

Compatibility

To learn the supported versions of Microsoft Excel, refer to the Compatibility section.

Requirements

Applications that use the Excel provider require Microsoft Data Access Components (MDAC) to be installed on the client computer. In the current versions of Microsoft Windows, since Windows 2000, MDAC is already included as a standard package.

Deployment

When an application was built without runtime packages (Link with runtime packages is set to False in Project Options), you do not need to deploy any BPL files with it. For more information, see Deployment.
Note, that UniDAC Trial requires deployment of additional BPL files regardless of the Link with the runtime packages.

Excel-specific options

TUniConnection

Option name	Description
ColumnWiseBinding	If set to True, the option enables Column-Wise Binding mode. The default value is False. Note: Row-Wise Binding mode is enabled by default. However, some ODBC drivers do not support this mode. In such a case, set the ColumnWiseBinding option to True.
Database	The full path to the workbook to connect to.
Default Cell Type	Specifies what string data types are used by the provider for literal columns. cdString - All literal columns will be returned as string. cdMemo - All literal columns will be returned as memo. The default value is cdString.
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.
Direct	If set to True, connection to the database is performed directly, and does not require any additional software on the client side. If False, the provider connects using Microsoft dBase ODBC driver. Default value is False.
DriverVersion	Specifies the version of the Microsoft Access Driver (*.mdb, *.accdb). Supported values: dvAuto - Tries the *.accdb driver first; falls back to *.mdb if not found. dvAccdb - Specifies that the *.accdb driver will be used. dvMdb - Specifies that the *.mdb driver will be used. The default value is dvAuto.
Exact Field Length	Specifies how the size of string fields in the dataset is set. False - The field size is set to one of the default values (255, 4096, or 32767), depending on the detected data type and content. True - The field size is set to the exact maximum length of the data. The default value is False.
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 include 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 driver 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 workbook simultaneously, however only in the read-only mode.
UseUnicode	If set to True, enables Unicode support. Affects character data fetched from the server. Character data is stored as WideStrings, and TStringField is replaced by TWideStringField.

TUniSQL

Option name	Description
CommandTimeout	The time to wait for a statement to be executed.

TUniQuery, TUniTable, TUniStoredProc

Option name	Description
CommandTimeout	The time to wait for a statement to be executed.
ExtendedFieldsInfo	If True, an additional query is performed to get information about returned fields and tables they belong to. The default value is True.
FetchAll	If True, all records of a query are requested from database server when the dataset is being opened. If False, records are retrieved when a data-aware component or a program requests it. The default value is False.

TUniScript

The TUniDump component has no Excel-specific options.

TUniLoader

The TUniLoader component has no Excel-specific options.

TUniDump

The TUniDump component has no Excel-specific options.

Connecting in Direct mode

Direct connection enables users to connect to Microsoft Excel workbooks directly, without the need for Microsoft Excel to be installed on their machine.

Data Type Mapping

The following table lists the constants for mapping Excel data types to Delphi data types. See Data Type Mapping for more information.

Constant	Description
excelBoolean	Maps boolean to Delphi data types.
excelInteger	Maps integer to Delphi data types.
excelDouble	Maps float to Delphi data types.
excelDate	Maps date to Delphi data types.
excelTime	Maps time to Delphi data types.
excelDateTime	Maps datetime to Delphi data types.
string	SQL_VARCHAR