Using DTS to import data from an excel spreadsheet SQL version 2003 version 8.0

I've been using this spreadsheet for quite sometime, but with a different company.  I can't seem to get it to work now.  Please see attached error message and Excel file
DTS-Error-Message.doc
New-Prop-Template.xls
rhonlw83Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Brendt HessConnect With a Mentor Senior DBACommented:
The Excel DTS import does not like certain fields where they contain all numbers, but the field is formatted as text.  I have found it easier to prepend on a single quote on any field containing numeric text to ensure that the DTS package succeeds.

Here is a snippet of Excel macro code that will ensure that the values in the cells will be treated as strings by the DTS import routines.  Try running it against that column, then run the import.  I believe your import will succeed.
Sub Textify()
'
' Select the last cell in the column that needs to be textified
' So, if data exists in column C, go to the last cell in that 
'   column with data in it, then run the macro. 
    Do
        If ActiveCell.Row = 1 Then Exit Do
        If Left(ActiveCell.FormulaR1C1, 1) <> "'" Then
            ActiveCell.FormulaR1C1 = "'" & ActiveCell.FormulaR1C1
        End If
        ActiveCell.Offset(-1, 0).Activate
    Loop
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.