When importing Excel Spreadsheet to SQL Server via OLEDB, the import program will "determine" the data type based on "guessing" by default. It examines the first 8 rows of the data in that column, and use the majority data type as the data type. For example, if a column has text for rows 1 to 5, and numeric from 6 to 8, then the import program will decide to use Text Type to import the data, and leave the 3 numeric rows as NULL.

This is not acceptable in many cases, as we hope we can import all of the data as is, be it text or number. For example, if we have a Column called "Lot Number". It has 3 rows ABCD12345, 22222, and ABCDEF. If we let it import as default, the row 22222 will become NULL.

To overcome this, we will need to:

Step 1: reformat the column as Text column, instead of General.

Step 2: re-type 22222 again in that row.

Please note that step 2 is necessary to prevent 22222 to become NULL.

There are some topics regarding this issue and some registry values can be changed. Like Under:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/

You can change:

ImportMixedTypes=Text (or Majority Type, which is the default)
TypeGuessRows=8

Also, in Connection String, you can use IMEX=1 to force ImportMixedTypes=Text

By changing TypeGuessRows, you can change the range the Import Program guess. By specifying ImportMixedTypes=Text and IMEX=1, you can force to treat the columns as Text only.