This file contains important information on importing data from an external source into a SQL Server database using ADO.NET.

The following abstract is taken from How To Use ADO with Excel Data from Visual Basic or VBA and it applies while importing data from Excel spreadsheets.

Considerations that apply to OLE DB providers

A Caution about Mixed Data Types

By default the data type is determined based on the first 8 rows of data. (This is not affected by Excel cell formatting settings.) A serious problem can arise if you have numeric values mixed with text values in the same column. Both the Jet and the ODBC Provider return the data of the majority type, but return NULL (empty) values for the minority data type meaning they are not imported. If the two types are equally mixed in the column, the provider chooses numeric over text.

For example:

1.	In the eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
2.	In the eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.
3.	In the eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.

As a result, if your column contains mixed values, your only recourse is to store numeric values in that column as text, and to convert them back to numbers when needed in the client application by using the Visual Basic VAL function or an equivalent.

To work around this problem for read-only data, enable Import Mode by using the setting "IMEX=1" in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting. However, note that updates may give unexpected results in this mode. For additional information about this setting, click the article number below to view the article in the Microsoft Knowledge Base: