You are currently offline, waiting for your internet to reconnect

PRB: DTS Wizard may not detect Excel column type for mixed data in SQL Server

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q236605
SYMPTOMS
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.
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:
DecimalHex
11
22
33
44
55
66
77
88
99
10A
11B
12C
13D
14E
15F
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
For more information, see the "SQL Server Data Transformation Services" topic in SQL Server Books Online.
kbDSupport
Properties

Article ID: 236605 - Last Review: 02/22/2007 22:33:50 - Revision: 6.3

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbdatabase kbprb KB236605
Feedback
050&did=1&t=">ml>