I use a DTS package that imports data from an Excel spreasheet in a table in
SQL Server 2000. All tranformations are a simple copy column.

Zip Codes with leading 0's are imported without the leading 0. 9 digit zip
codes with a hyphen show up as null in column defined as varchar(15).

All columns in the Excel spreadsheet are defined as Text. Everything
imports fine except for the two problems above.

Can anyone help?

WR

Hello WhiskyRomeo,


Excel driver is a funny beast. Have a read of the Excel stuff here

Troubleshooting
(http://www.sqldts.com/default.aspx?222)


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

WhiskyRomeo wrote:
I had a similar problem. I used the Format Cells option to set them to Text, but they still would import without
leading zeroes or as nulls (when the cell had a non-numeric character in it). Evidently Excel was formating the display
as text but internally it still considered the cells to be numerics.

Try this:

Select the column that is importing wrong
In the toolbar, select Data, then Text to Columns
Step through the wizard until you come to the Data Type window.
If the type is listed as General, change it to Text.
Save the spreadsheet.

After the change, cells that have values that Excel thinks are numbers will display with a green triangle in the
upper-left hand corner. This is for display only and does not affect the data pump.