Copyright: , 1996 INTERSOLV


Contents, Excel 4 Driver Help
	DataDirect ODBC  Excel 4 Driver
About the Excel 4 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 4 Driver

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

The Excel 4 driver filename is IVXLSnn.DLL.


Excel 4 System Requirements
System Requirements

There are no software requirements for using the Excel 4 driver, because you do not need the Excel program to use it.  It works wherever your Excel version 2, 3, or 4 .XLS files are present.


Using Excel 4 Databases
Using Excel Databases

The Excel 4 driver lets you create databases using Excel version 2, 3, or 4.  An Excel 4 database is a directory of component .XLS worksheet files, each of which contains a single table.

With the Excel 4 driver, you can open any Excel worksheet file that contains a database section.  To create a database section in a worksheet, you select a range to be used as a database and choose Excel's Set Database command from the Data menu.  The driver accesses the rows and columns from the database section.

The Excel 4 driver allows some database manipulation via SQL statements.  The Insert statement is limited to use with the Create Table statement only.  The Drop Table statement is also supported.  You may not execute Update or Delete statements.


Configuring Excel 4 Data Sources
Configuring Data Sources

To configure an Excel 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 ExcelFile and click OK.

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

The Excel 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 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 4 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)	The directory in which the Excel files are stored.
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.
UltraSafeCommit (USF)	UltraSafeCommit={0 | 1}. Excel 4 driver attribute. A number that specifies when the driver flushes the file cache. If UltraSafeCommit=1, the driver updates directory entries after each Commit. This decreases performance. If UltraSafeCommit=0 (the default) the driver updates the directory entry when the file is closed. In this case, a machine "crash" before closing the file causes newly inserted records to be lost.
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 4 Data Types
Data Types

The following table shows how Excel data types map to the standard ODBC data types.  These Excel data types can be used in a Create Table statement with the Excel 4 driver.

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


Excel 4 Table and Column Names
Table and Column Names

Table names are not case-sensitive when using the Excel 4 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 4
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
Create Table Statement

The Excel 4 driver supports the Create Table statement.  The Insert statement can be used to load the newly created table, but this must be done during the same connection in which the Create Table statement was issued.  Once the connection used to create the table is closed, the Insert statement no longer operates on that table.  Future rows need to be inserted directly through Excel.  The Excel driver creates tables in Microsoft Excel version 4.0 (BIFF3) format.


Excel ODBC Conformance Level
ODBC Conformance Level

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

The drivers support the minimum SQL grammar.


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

The Excel drivers support multiple connections and multiple statements per connection.


Excel 4 ODBC Setup Dialog

Use the Excel ODBC Setup dialog to create new Excel 4 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 Directory:  This identifies the directory in which the Excel files are stored.  If none is specified, the current working directory is used.

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 4 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).

Ultra Safe Commit: A number that specifies when the driver flushes the file cache. If UltraSafeCommit=1, the driver updates directory entries after each Commit. This decreases performance. If UltraSafeCommit=0 (the default) the driver updates the directory entry when the file is closed. In this case, a machine "crash" before closing the file causes newly inserted records to be lost.

Character Length Guess: A value of 0 or 1 that 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.

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 4 ODBC Setup dialog box where you can click the OK button to write these settings to the ODBC.INI file.