DTS xls import number/char issue

[gcole]

I am trying to import an excel worksheet into a SQL table. The problem is, half way down the sheet the number includes an alpha. These are not importing as the DTS package thinks it is a number. I am doing this with EXCEL so I can't save it as a csv.

 

[pullesj]

What version of SQL Server? 7 or 2000?

I encountered the same problem when I was importing data via Excel (Office 2000) into SQL Server 7 (SP 3). I was never able to correct the issue. I had to save the Excel spreadsheet as a CSV file and change my DTS package to use the CSV file as source instead of Excel. I'm not sure if the issue still exists with SQL 2000.

I hope someone else here has better news for you, but I had to change the input format to CSV.

 

[gcole]

I can't do that as I am running a script in Excel and when you save it in VB, it closes the origonal. It will step through the code but not when it is run

 

[gcole]

I solved the issue by adding a quote before each field and then striping it off in the DTS package.

 

[RonPro]

Another option is that DTS will allow you to explicitly define the data types for the receiving fields. (I assume that you want the data coming in as char/nvarchar since it DOES have some alfas). I am at home now, so I don't have a DTS screen to reference, but it is found by clicking the 'Transform' button when you are specifying the source/destination information.