I'm having an issue importing data from an excel spreadsheet using both SSIS packages and manually through OpenDataSource in T-SQL. The spreadsheet comes from a third party data provider that we use to assist our customers in getting the right data so I can't change the setup of the spreadsheet.

In one column that is defined as a text column there is values representing manufacturing part codes such as this:
9420000073
9420000073
0305DC0161N
0503CA0030N

Using both methods (SSIS, OpenDataSet) the first two values from the above example return a null to the database while the next two are represented correctly. Any value in the column that is entirely numeric is returned as null.

I've tried tinkering with the registry per information from this article - set the TypeGuessRows to 0, include IMEX=1 in the connection string and so forth.

The best I can get is to have the data source return the numeric columns in scientific notation - eg 9.42e+009 which is useless to me.

Has anyone else had similar experiences and can point me in the right direction so I can get this bloody thing to import the data correctly?

The guts of what I'm doing with the OpenDataSource statement is:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Server\Files\Data.xls";
User ID=;Password=;Extended properties="Excel 8.0"')..."Parts$"

Yeah I always found the excel import to be troublesome. Can you switch to a flat text file or CSV? SQL Server also has less gotchya's importing from Access if that is an option.

It's an option, but I have no control over the source of the excel spreadsheet so it would add an extra step to the import process converting it to another format first. I may well have to take that route though.

Hi

Mixed data type in one column usually cause serious issues in Excel. Here is a suggestion to avoid the abnormality.

1. Use a macro to fix the data for the column(s) that cause the issue by adding single quote(') in the beginning of the data. In your case 9420000073 becomes '9420000073 - only in the beginning. I mean all the rows you need to go through.
2. This is macro hint how to do it automatically.

Sub UpdateFirstCol()
Dim lFirstRow, lLastRow, lA, i As Long

lFirstRow = 2 ' first row contains column header
lLastRow = xlLastRow("Sheet1")
' ID column. Here I assume column A contains ID
lA = ColRef2ColNo("A")

For i = lFirstRow To lLastRow
Worksheets("Sheet1").Cells(i, lA) = "'" & Worksheets("Sheet1").Cells(i, lA)
Next

End Sub

Function xlLastRow(Optional WorksheetName As String) As Long

' find the last populated row in a worksheet

If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
If Err <> 0 Then xlLastRow = 0
End With

End Function

Function ColRef2ColNo(ColRef As String) As Integer

ColRef2ColNo = 0
On Error Resume Next
ColRef2ColNo = Range(ColRef & "1").Column
On Error GoTo 0

End Function

3. Now use your SSIS or OpenDataSource.