Sunday, July 11, 2010

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.

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 :
c. ImportMixedTypes

No comments: