DTS import an EXCEL file with various Number Formatted in a single column

Stacie_B used Ask the Experts™
I have a problem.  I am trying to import an excel spreasheet that has a Zip Code column that is sporatically formatted as either a number or as a text.  When I try to import it into a table via a DTS package It converts the numbers formatted as strings into empty strings or nulls depending on how I do the translations.  I have tried eveything I could think of to resolve this problem, even highlighted the while sheet as set to text datatype but when the DTS package reads in the excel file it still interupts the Zip Code field as a float and turns the sporatically formmated Numbers as text to NULL.  Any suggestions on how I can force the import of all data values in thif field regardless of its formatting in my excel file?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Hello Stacie_B,

First off, make sure that the Excel file uses the Text number format in that column.  You can adapt this code
snippet in an ActiveX task:

Dim xlApp, xlWb

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("c:\folder\subfolder\file.xls")
With xlWb
    .Worksheets(1).Columns("C").NumberFormat = "@"  'update worksheet/column as needed
End With
Set xlWb = Nothing
Set xlApp = Nothing

Then also make sure that your table in SQL Server is using [n][var]char for that column.


Top Expert 2012
Take a look at this article it describes some workarounds to solve your problem when using DTS:
PROBLEM: Why does Excel Insert NULLs into my table when I know there is good data in there?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial