When you use the Microsoft SQL Server Data Transformation
Services (DTS) Import Wizard to import data from a Microsoft Excel worksheet,
if a text column contains data that could be interpreted as mixed data (for
example, alphanumeric characters representing hexadecimal data), the initial
few rows determine the actual data type used and subsequent rows may be
transferred as NULLs. DTS does not provide a warning message to indicate that
some rows might not transfer.Note
If you are using SQL Server 2005, use the SQL Server Import and Export Wizard to perform the data transform.
This behavior is by design for the Excel ISAM. The first 8
rows determine the data type of the column. For example, if most of the first 8
rows contain numeric characters, the datatype of the column is a number. All
subsequent values that do not fit that datatype are returned as NULL.
One workaround is to save the Excel sheet as a text file
and then you use the DTS Wizard to import the file into SQL Server.
Steps To reproduce the behavior
For example, create the following worksheet in Excel:
Collapse this tableExpand this table
The first column "Decimal" is formatted as General
and the second column "HEX" is formatted as Text.
If you load this
table from Excel into SQL Server using the DTS Wizard, rows 10-15 (values A-F)
are NULL when loaded into a SQL table (the HEX column is formatted as Text in
the workbook, but DTS makes it a Float type since it detects the source column
as DBTYPE_R8 ).
For more information, see the "SQL Server Data
Transformation Services" topic in SQL Server Books Online.