Data Type Guess While Importing Excel using ADO


When we import Excel Data using OLEDB provider, the type of each column will be determined based on the First 8 rows of the Excel. if you have numeric values mixed with text values in the same column then data of the majority type will be considered and takes Null values for the minority data type. If the two types are equally mixed then it will consider as Numeric.

If is basically a configuration in the Registry.

RegEdit-->HKEY_LOCAL_MACHINE-->SOFTWARE-->MICROSOFT-->JET-->4.0-->ENGINES-->EXCEL
Key :TypeGuessRows
By Default, its value is set as 8. This is why the OLEDB provider guess the data type based on First Eight row.
By setting the TypeGuessRows to 0, the OLEDB provider guesses the datatype for each column is based on its entire row present in the Excel.

In the same registry, even we can find few more settings
a. AppendBlankRows - It is to specify whether the blank rows should be imported
b. FirstRowHasNames - it is to specify whether the first row is a header row.
(However, due to a bug in the ODBC driver, specifying the FirstRowHasNames setting currently has no effect. In other words, the Excel ODBC driver (MDAC 2.1 and later) always treats the first row in the specified data source as field names.Ref : http://support.microsoft.com/kb/257819)
c. ImportMixedTypes


Related Articles

More articles: Excel import using ADO DataType in excel Excel functions

Comments

Guest Author: Dominik17 Mar 2013

Thank you very much for this tip, this was it.
For those who want to query excel columns with different types, in ole con string u have to use IMEX=1 in extended properties, like :
"Extended Properties='Excel 8.0;IMEX=1';"



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: