Importing from Excel - non-numeric values are ignored

Asked 19/9, 2009 at 12:14 Answered 19/9, 2009 at 12:45

I have code that imports from Excel sheets of a specified format. In one of the columns, most data is numeric, but non-numeric values are also present. The non-numeric values are ignored by import code, for some reason.

The connectionstring looks like this:

    Dim FileConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & Path & "\" & _
    Filename & ";Extended Properties=" & _
    """Excel 12.0;HDR=YES;IMEX=1;"""

The actual import code looks something like:

    Dim Factory As DbProviderFactory = _
    DbProviderFactories.GetFactory("System.Data.OleDb")

    Dim Adapter As DbDataAdapter = Factory.CreateDataAdapter(), _
    DataObject As New DataSet

    Using Connection As DbConnection = Factory.CreateConnection
        Connection.ConnectionString = FileConnectionString

        Using Command As DbCommand = Connection.CreateCommand

            Command.CommandText = _
            "SELECT [Column1], [Column2]" & _
            "FROM [Sheet1$]"

            Adapter.SelectCommand = Command

            Adapter.Fill(DataObject)

            ...

Please note that I have enabled IMEX = 1 to inform Excel that mixed data will be present. This doesn't seem to help. Any idea what's going on?

Lyford answered 19/9, 2009 at 12:14 Comment(0)

By default Excel bases the data type for a column on the first 8 rows of data. To change this, you need to update the registry key:

HKLM\Software\Microsoft\Office\12.0\Access Connect Engine\Engines\Excel

with the number of rows you want Excel to scan. Set the value to 0 to have it scan all the rows. Note that setting it to zero can slow the Excel loads down a bit, especially on large spreadsheets.

Also, if you are also using Excel 2003, you need to update a second registry key:

HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

Lanthanum answered 19/9, 2009 at 12:45 Comment(1)

Thanks - I do have access to the server, so I'm debating editing the reg. value, or just entering 8 dummy STRING values. The latter will be more portable, methinks. – Lyford 19/9, 2009 at 13:9

What I have noted is that the Engine infers the data type of the column from the first value in the column.

So, if your first value is numeric, the engine will assume the column as numeric and when trying to convert a alpha value to numeric will fail.

What i Have usually done is append an underscore to all the values on Excel, and then, up on SQL or on DataSet, to make the replace of the first underscore. Hope it will helps

Womanly answered 19/9, 2009 at 12:20 Comment(2)

Thanks, very useful tip - but Jeff's tip seems to be more accurate in terms of the reason. I will probably enter 8 dummy STRING values in the sheet (programmatically or manually) to have the most predictable, portable behavior. – Lyford 19/9, 2009 at 13:8

Oh, BTW, the reason I can't use your solution is because I don't want to have the overhead of adding an underscore to each value. Cheers. – Lyford 19/9, 2009 at 13:10