Microsoft Excel Data Types

The following table shows how Microsoft Excel driver data types are mapped to ODBC SQL data types. The Microsoft Excel driver assigns these data types to columns in Microsoft Excel tables based on the data in the column.

Microsoft Excel data type

ODBC data type

CURRENCY

SQL_NUMERIC

DATETIME

SQL_TIMESTAMP

LOGICAL

SQL_BIT

NUMBER

SQL_DOUBLE

TEXT

SQL_VARCHAR

Note Note

SQLGetTypeInfo returns ODBC SQL data types. All conversions in Appendix D of the ODBC Programmer's Reference are supported for the ODBC SQL data types listed earlier in this topic.

The following table shows limitations on Microsoft Excel data types.

Data type

Description

Encrypted data

The Microsoft Excel driver cannot read encrypted data.

Error Strings

The Microsoft Excel driver cannot return a character string for the Microsoft Excel error values (#N/A!, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!), but returns a NULL instead.

LOGICAL

The value in a LOGICAL column is returned in a SQL_C_CHAR buffer as either 0 or 1.

NUMBER

If an integer column is created, numbers that are too big for the integer data type can be entered, and data containing non-integer values can be inserted, with the result that the column may be converted to SQL_DOUBLE.

TEXT

When the rows of a column contain more than one Microsoft Excel data type, the ODBC Microsoft Excel driver assigns the SQL_VARCHAR data type to the column. There is one exception to this: if the column contains only two or three of the datetime data types (DATE, TIME, and DATETIME), the ODBC Microsoft Excel driver assigns the SQL_TIMESTAMP data type to the column.

Creating a TEXT column of zero or unspecified length actually returns a 255-byte column.

A character string literal can contain any ANSI character (1-255 decimal). Use two consecutive single quotation marks (") to represent one single quotation mark (').

Inserting a NULL into a column with a data type other than SQL_VARCHAR will cause the data type of the column to change to SQL_VARCHAR.

More limitations on data types can be found in Data Type Limitations.

Show:
© 2014 Microsoft