I have an Excel 2003 spreadsheet with a column of alpha/numeric data.
The first row is OPEN and the next 100 or so rows are fy's ranging from
1999 - 2005. When I preview the data (using SQL Server DTS import) it
does not show OPEN but displays the fy's.

emp termination_year salary
11 50K
12 2004 45K
13 2003 60K

When I import the xls the OPEN is stored as a NULL. The data type of
the destination column is char(4). Further, in the DTS screen where
you select source table/views and destination table there exists a
transform button. When I click this button a column mappings dialog
appears and when I click the column in question the dialog indicates
that the column is of type Double NULL.

I tried formatting the column in question in the excel spreadsheet from
General to Text but the ODBC driver still considers the column to be of
type double.

Any ideas on getting the value OPEN into the db w/o converting the xls
to some other format such as CSV?

Excel is really bad at this... You can try to add an apostrophe in front of
the open and see if it picks it up then. Or you can convert to csv (which
you mentioned you did not want to do)..

Here is a simple automated way to do this if you have Excel loaded on the
box that executes the dts package.

Sub ConvertExcelToCSV(fromFile,toFile )
Dim oExcel
Dim workSheet
Set oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = False
'//Open the file in read only mode
oExcel.Workbooks.Open fromFile,false,true
'//Get the first WorkSheet
Set workSheet = oExcel.ActiveWorkbook.Worksheets(1)
'// Save it as CSV file.
oExcel.ActiveWorkbook.SaveAs toFile,6
oExcel.Workbooks(1).Close
oExcel.Quit
Set workSheet = Nothing
Set oExcel = Nothing
End Sub

Bill

<choxio (AT) yahoo (DOT) com> wrote