locked
need help: Using ODBC to read from Excel - mixed format columns RRS feed

  • Question

  • User2003575416 posted

    I google and can not find the solution.

    When use OldDb there is Extended Properties IMEX=1 tells the driver to always read "intermixed" data columns as text

    How is work in ODBC? I add

    Extended Properties="IMEX=1" to my connection but it still can not read mixed format
    Thursday, August 18, 2011 10:36 AM

Answers

  • User-837620913 posted

    You can't do this in ODBC.  You can change the number of rows to scan, but that does not guarantee it will work. Also, there is a bug in the RowsToScan property in that ODBC basically ignores it.

    From here about half-way down the article:

    Rows to Scan: Excel does not provide ADO with detailed schema information about the data it contains, as a relational database would. Therefore, the driver must scan through at least a few rows of the existing data in order to make an educated guess at the data type of each column. The default for "Rows to Scan" is eight (8) rows. You can specify an integer value from one (1) to sixteen (16) rows, or you can specify zero (0) to scan all existing rows. This is done by adding the optional MaxScanRows= setting to the connection string, or by changing the Rows to Scansetting in the DSN configuration dialog box. 

    However, due to a bug in the ODBC driver, specifying the Rows to Scan (MaxScanRows) setting currently has no effect. In other words, the Excel ODBC driver (MDAC 2.1 and later) always scans the first 8 rows in the specified data source in order to determine each column's datatype. 

    For a workaround of this bug you have to modify the registry.  If you want to do that, check this support article.

    http://support.microsoft.com/kb/189897/EN-US

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 18, 2011 3:23 PM