Connecting to Excel Databases

On Windows, ColdFusion lets you access Microsoft Excel using ODBC or OLE DB. For information about using OLE DB with ColdFusion data sources, see "About OLE DB".

ODBC: Microsoft Excel Driver options

The following table describes ColdFusion ODBC options for Microsoft Excel data sources. You set these options when you configure a ColdFusion data source.

Option

Description

Data Source Name
A name for your ODBC data source.

Description
Descriptive information about the data source.

Workbook/Directory
The path and filename of the Excel workbook that you want to use as the ODBC data source.

Version
Enter the version number of the Excel workbook that you want to use. The ColdFusion Administrator supports Excel versions 3, 4, 5, 97, and 2000.

Driver Settings
Rows to Scan The number of rows to scan to determine the data type of each column. The data type is determined by the maximum number of kinds of data found. If data does not match the data type guessed for the column, the data type is returned as a NULL value.
Enter a number from 1 to 16 for the rows to scan. The default value is 16. If this setting is 0, all rows are scanned. A number outside the limit returns an error.

Option	Description
Data Source Name	A name for your ODBC data source.
Description	Descriptive information about the data source.
Workbook/Directory	The path and filename of the Excel workbook that you want to use as the ODBC data source.
Version	Enter the version number of the Excel workbook that you want to use. The ColdFusion Administrator supports Excel versions 3, 4, 5, 97, and 2000.
Driver Settings	Rows to Scan The number of rows to scan to determine the data type of each column. The data type is determined by the maximum number of kinds of data found. If data does not match the data type guessed for the column, the data type is returned as a NULL value. Enter a number from 1 to 16 for the rows to scan. The default value is 16. If this setting is 0, all rows are scanned. A number outside the limit returns an error.

ODBC: MERANT Excel Workbook Driver options

The following table describes ColdFusion ODBC options for data sources created with the MERANT Excel Workbook driver:

Option

Description

Data Source Name
A name for your data source.

Description
Descriptive information about the data source.

Database Workbook
A name that identifies the workbook file containing the Excel database.

International sort Determines the order in which records display when you issue a Select statement with an Order By clause.
If you do not select this option, the driver automatically uses the ASCII sort order. This order sorts items alphabetically with uppercase letters preceding lowercase letters. For example, "A, b, C" sorts as "A, C, b."
If you select this option, the driver uses the international sort order as defined by your operating system. This sort order is always alphabetic, regardless of case; the letters from the previous example would sort using as "A, b, C."

Advanced ColdFusion Administration
Advanced Data Source Management

Option	Description
Data Source Name	A name for your data source.
Description	Descriptive information about the data source.
Database Workbook	A name that identifies the workbook file containing the Excel database. International sort Determines the order in which records display when you issue a Select statement with an Order By clause. If you do not select this option, the driver automatically uses the ASCII sort order. This order sorts items alphabetically with uppercase letters preceding lowercase letters. For example, "A, b, C" sorts as "A, C, b." If you select this option, the driver uses the international sort order as defined by your operating system. This sort order is always alphabetic, regardless of case; the letters from the previous example would sort using as "A, b, C."