PRB: DTS Wizard May not Detect Excel Column Type for Mixed Data

ID: Q236605

The information in this article applies to:

SYMPTOMS

When using the Microsoft SQL Server Data Transformation Services 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.


CAUSE

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.


WORKAROUND

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.


MORE INFORMATION

Steps To Reproduce the Behavior

For example, create the following worksheet in Excel:

Decimal Hex
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 A
11 B
12 C
13 D
14 E
15 F


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 ).

REFERENCES

SQL Server Books Online; search on: "SQL Server Data Transformation Services"

Additional query words: kbDSupport 


Keywords          : kbDatabase kbOLEDB kbSQLServ kbVC kbSQLServ700 
Version           : WINDOWS:97; winnt:7.0
Platform          : WINDOWS winnt 
Issue type        : kbprb

Last Reviewed: July 30, 1999