Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009

    Unanswered: Null values when importing data from excel using OpenDataSource.

    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:

    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$"

  2. #2
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2009
    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.

  4. #4
    Join Date
    Feb 2007

    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)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts