Copyright: , 1996 INTERSOLV


Contents, Excel 5 Driver Help
	DataDirect ODBC  Excel 5 Driver
About the Excel 5 Driver

System Requirements

Configuring Data Sources

Using Excel Databases

Connecting to an Excel Database Using a Connection String

Select Statement

Create Table Statement

Data Types

ODBC Conformance Level

Number of Connections and Statements Supported

Copyright


Copyright
Copyright 1996 INTERSOLV Inc. All rights reserved. INTERSOLV is a registered trademark, and DataDirect is a trademark of INTERSOLV, Inc. Other company or product names mentioned herein may be trademarks or registered trademarks of their respective companies.


About the Excel 5 Driver

Two INTERSOLV DataDirect ODBC drivers support Excel .XLS files. The Excel 5 driver (documented in the current Help file) supports Excel version 5. The Excel 4 driver supports Excel versions 2, 3, and 4.

The Excel 5 driver filename is IVXLS5nn.DLL.


Excel 5 System Requirements
System Requirements

To use the Excel 5 driver, you must have version 5 of Microsoft Excel installed on the system where you intend to use an Excel 5 database for these reasons:

The Excel 5 driver requires two DLLs to operate.  These DLLs, STORAGE.DLL and COMPOBJ.DLL, are installed when you install Excel version 5.

Because the Excel 5 driver can read data from Excel 5 .XLS files only, you must use Excel 5 for any manipulation of your Excel 5 databases.


Using Excel 5 Databases
Using Excel Databases

An Excel 5 database is any Excel 5 .XLS workbook file that contains one or more named lists containing record data.  Each named list is treated as a table within the workbook database.  The lists must be set up as follows:

The first row in each list must contain the column labels that identify the fields in each record.

The name of each list must be a book-level name, not a sheet-level name.

Although the Excel 5 driver recognizes one list named "Database" per .XLS file, it is recommended that you avoid using this name--both to ensure that the Excel 5 driver recognizes other lists in the file and to prevent future naming conflicts.

See your Excel 5 documentation for more information on creating and naming lists in Excel.


Configuring Excel 5 Data Sources
Configuring Data Sources

To configure an Excel 5 data source, do the following:

1	Start the ODBC Administrator.  A list of data sources appears.

2	If you are configuring a new data source, click Add.  A list of installed drivers appears.  Select INTERSOLV Excel5Workbook for the Excel 5 driver and click OK.

If you are configuring an existing data source, select the data source name and click Setup.

The Excel 5 ODBC Setup dialog box appears.

3	Specify a data source name and optionally, a description and database directory.

4	Click the Advanced button to configure optional data source settings, such as file caching information. The ODBC Advanced Driver Setup dialog box appears.

5	Click Translate to perform a translation of your data from one character set to another. The Select Translator dialog box appears in which you can select a translator to peform the translation. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set. Click OK to leave this dialog box and perform the translation.

The translators that are listed in this dialog box are determined by the values listed in the ODBC Translators section of your ODBCINST.INI file.

6	Click OK to write these values to ODBC.INI.  These values are now the defaults when you connect to the data source.  You can change the default values by configuring your data source again.  You can override the defaults by connecting to the data source using a connection string with alternate values.


Connecting to an Excel 5 Database Using a Connection String
Connecting to an Excel Database Using a Connection String

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which ODBC.INI section to use for the default connection information.  Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in ODBC.INI.  These values are not written to ODBC.INI.

You can specify either long or short names in the connection string.  The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for Excel is

DSN=EXCEL FILES;FOC=4

The following table gives the long and short names for each attribute, as well as a description.

The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in ODBC.INI.  If you specified a value for the attribute when configuring the data source, that value is your default.

Attribute	Description
DataSourceName (DSN)	String that identifies an Excel data source configuration in ODBC.INI.  Examples include "Accounting" or "Excel Files."
Database (DB)	For the Excel 5 driver, the name of the .XLS workbook file containing the Excel database.
ScanRows (SR)	Number of rows to scan to determine the column data types.  A value of 0 means to scan the whole table.  The initial default is 25.
FileOpenCache (FOC)	Maximum number of unused file opens to cache.  For example, when FileOpenCache=4, and a user opens and closes four files, the files are not actually closed.  The driver keeps them open so that if another query uses one of these files, the driver does not have to perform another open, which is expensive.  The advantage of using file open caching is increased performance.  The disadvantage is that a user who tries to open a file exclusively may get a locking conflict even though no one appears to have the file open.  The initial default is 0.
CacheSize (CSZ)	Number of 64K blocks the driver uses to cache database records.  The higher the number, the better the performance.  The maximum number of blocks you can set depends on the system memory available.  If the cache size is greater than 0, when browsing backwards, you will not be able to see updates made by other users until you reexecute the Select statement. The default is 4.
IntlSort (IS)	IntlSort={0 | 1}. Determines the order in which records are retrieved when you issue a Select statement with an Order By clause.  If IntlSort=1, the driver uses the international sort order as defined by your operating system.  The sort is case-insensitive (a precedes B); the sorting of accented characters is also affected (see your operating system documentation).  If IntlSort=0 (the initial default), the driver uses the ASCII sort order, where uppercase letters precede lowercase letters (B precedes a).
FieldGuessing (FG)	FieldGuessing={0 | 1}. A value that determines whether the driver will try to guess column data types.  If FieldGuessing=1 (the default), the driver attempts to guess the data types.  If FieldGuessing=0, the driver assumes that all data types are SQL_CHAR.
ModifySQL (MS)	ModifySQL={0 | 1}. This attribute is provided for backward compatibility with earlier versions of Q+E products.  It determines whether the driver modifies SQL statements to conform to ODBC specifications or passes the SQL statement directly to Excel.  Specify ModifySQL=1 to have the driver modify the SQL statement to conform to ODBC specifications.  Specify ModifySQL=0 to have the driver understand SQL dialects found in earlier drivers.  The default is 1.
UseLongQualifiers (ULQ)	UseLongQualifiers={0 | 1}. This attribute specifies whether the driver uses long pathnames as table qualifiers. When you set this check box, pathnames can be up to 255 characters. The default length for pathnames is 128 characters.
CharacterLength Guess (CLG)	CharacterLengthGuess={0 | 1}. Determines whether the driver tries to guess the length of character columns. If set to 0 (the default), the driver does not try to guess the length of character columns. Instead, it assumes that all character columns have a length of 255. If set to 1, the driver tries to guess the length.


Excel 5 Data Types
Data Types

The following table shows how Excel data types map to the standard ODBC data types.

Excel	ODBC
Char	SQL_VARCHAR
Date	SQL_DATE
Logical	SQL_BIT
Number Float	SQL_DOUBLE
Time	SQL_TIME
Timestamp	SQL_TIMESTAMP


Excel 5 Table and Column Names
Table and Column Names

Table names are case-sensitive when using the Excel 5 driver.

Excel files contain column names in the first row of the database section or named list that is used as a table.  Use these names as the column names in a Select statement.  Column names are limited to 32 characters.  If column names are lowercase, contain upper- and lowercase, contain blank spaces, or are reserved words, surround them with the grave character (`) (ASCII 96).


Select Statement for Excel 5
Select Statement

You use a SQL Select statement to specify the columns and records to be read.  Excel Select statements support all of the Select statement clauses.


Create Table Statement for Excel 5
Create Table Statement

The Excel 5 driver does not support the Create Table statement.


Excel 5 ODBC Conformance Level
ODBC Conformance Level

The Excel 5 driver supports the Core, Level 1, and Level 2 API functions listed in Supported ODBC Functions.  The Excel 5 driver supports backward and random fetching in SQLExtendedFetch.

The drivers support the minimum SQL grammar.


Number of Connections and Statements Supported by Excel 5
Number of Connections and Statements Supported

The Excel 5 driver supports multiple connections and multiple statements per connection.


Excel 5 ODBC Setup
Excel 5 ODBC Setup Dialog

Use the Excel 5 ODBC Setup dialog to create new Excel 5 data sources or configure existing data sources.

Data Source Name:  A string that identifies this Excel data source configuration in ODBC.INI.  Examples include 'Accounting' or 'Excel Database.'

Description:  An optional long description of a data source name.  For example, 'My Accounting Database' or 'Excel .XLS file data.'

Database Workbook: Identifies the workbook file containing the Excel database.

Advanced: Displays the ODBC Advanced Driver Setup dialog box to configure optional data source settings, such as file caching.

OK

Creates or modifies the current data source using the options you specify.

Cancel

Exits the ODBC Setup dialog without creating or modifying a data source.


Advanced Options for Configuring Data Sources
Advanced Dialog

To configure optional settings for an Excel 5 data source, specify values as follows:

File Open Cache:  A numeric value to specify the maximum number of unused file opens to cache.  For example, the value 4 specifies that when a user opens and closes four tables, the tables are not actually closed.  The driver keeps them open so that if another query uses one of these tables, the driver does not have to perform another open, which is expensive.  The advantage of file open caching is increased performance.  The disadvantage is that a user who specifies file locking on open may get a locking conflict even though no one appears to have the file open.  The default is 0, which means no file open caching.

Cache Size:  The amount of memory, in 64K blocks, that the driver uses to cache database records.  The higher the number, the better the performance.  The maximum number you can set depends on the system memory available.  This value must be a multiple of 64.  The default is 256.  If the cache size is greater than 0, when browsing backwards, you will not be able to see updates made by other users until you reexecute the Select statement.

Rows to Scan:  The number of rows to scan to determine the column data types.  The default is 25 rows.  A value of 0 means to scan the whole table.

International Sort:  A setting to indicate the order in which records are retrieved when you issue a Select statement with an Order By clause.  Select this check box to use the international sort order as defined by your operating system.  International sort order is case-insensitive (a precedes B); the sorting of accented characters is also affected (see your operating system documentation).  Leave this box blank to use the ASCII sort order.  ASCII sort order is case-sensitive, where uppercase letters precede lowercase letters (B precedes a).

Use Long Qualifiers: Set this check box to use long pathnames as table qualifiers. When you set this check box, pathnames can be up to 255 characters. The default length for pathnames is 128 characters.

Translate: Displays the Select Translator dialog box to allow you to perform a translation of your data from one character set to another. Choose the INTERSOLV OEM ANSI translator to translate your data from the IBM PC character set to the ANSI character set.

Close

Returns to the Excel 5 ODBC Setup dialog box where you can click the OK button to write these settings to the ODBC.INI file.