Excel - ODBC Driver

1 - About

This article talk about the Odbc Excel driver. For more information, you can check the Microsoft Excel Driver Programming Documentation

The ODBC Excel driver is one of the Microsoft ODBC Desktop Database Drivers (Access, Paradox, dBASE, Text) and follow then the same API.

3 - SQL Grammar and function

The ODBC drivers completely support the Minimum ODBC 2.5 SQL grammar and the clauses in the Core and Extended ODBC 2.5 grammar of this article.

The function (scalar function) are listed here : Scalar Functions. For information about the arguments and return values of scalar functions, see Appendix F of the ODBC Programmer's Reference.

Example:

  • For a number:
UPDATE Table SET "Column1"=Value1 WHERE "Column2"=Value2
  • For a string and datetime
UPDATE Table SET "Column1"='Value1' WHERE "Column2"=Value2

4 - Options of the ODBC driver

4.1 - Rows To Scan

The Microsoft Excel Open Database Connectivity (ODBC) driver provides an option called Rows To Scan. This option specifies the number of rows to scan to determine the data type of each column. The data type is determined given the maximum number of kinds of data found. If data is encountered that does not match the data type guessed for the column, the data type will be returned as a NULL value.

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.

The Rows To Scan option will always use the first eight rows of data to determine the data type of a given field regardless of the number of rows specified. In the case where the first 8 rows contain four numeric and four text values, the data type for the column will default to Number.

4.2 - Read Only

Select Read Only to prohibit updates to the Excel file.

5 - Documentation / Reference


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap