NULL being inserted for column values when importing from Excel to DataSet

I am importing excel data to a datatable in my application and facing problem for some particular column values.

Some cells in a excel sheet column CustomerUniqID show warning with green mark in corner.

The number is formatted as text or preceded by apostrophe.

These cells value are not imported and show blank value when dataset is filled from Excel sheet.

Dim query As String = "SELECT CINT(CustomerUniqID),[Status] FROM [Sheet1$]"
Dim conn As New OleDbConnection(conStr)
If conn.State = ConnectionState.Closed Then
   conn.Open()
End If
Dim cmd As New OleDbCommand(query, conn)
Dim da As New OleDbDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)

My connection string is

<add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=2'"/>

The column CustomerUniqID contains numbers and I am unable to import these cells value. How to do this ?

Solution

The only way I could get it to fail as described in the original post, is if the escaped/text cells were further down than what I was originally testing. It is too bad that OleDB wont use a Schema.ini with an excel file because that would allow a very clean and simple solution, alas...

Sample data used:

Country    Capital     Population   Rank
France     Paris       2.25         7
Canada     Toronto     2.5          6
Egypt      Cairo       10.2         9
...

It actually uses 16 rows with the last 3 "Rank" items being escaped as text (e.g. '2). These all show the green corner warning sign in Excel.

Since OleDB does not read/use a Schema, it decides the data type for each column from the first N rows (defined as 8 in my registry). When the escaped cells do not match that, it returns a DBNull value. Attempts to convert the column via SQL (CInt, Val) fail because OleDB has already decided that the data there does not match before the conversion can be applied.

In some cases, I would read the sheet in twice. First getting the "good" columns in their proper datatype into one DataTable; then again getting the 'dirty' column as text and manually converting the data. This would be useful in cases where there are other numeric columns are in the data set and you dont want them to be converted to text/string.

For the case posted, if there really are only 2 columns involved, you should be able to use one table read in as text; and add a numeric column to receive the converted value. Rather than converting from one table to another, convert from one column to another. (Just ask, if you want an example, but it is just a subset of the following).

In either case, the "trick" is to use a different connection string to force OleDB to read the data as text. Apparently both HDR=No and IMEX=1 are needed for this, at least with my config:

Dim TextConStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\capitals.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"

This example/text code uses the 2 table approach to verify that other numerics (Population) are not converted, just Rank:

' ConStr to allow OleDB to guess the datatypes   
Dim TypedConStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\capitals.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=2';"

' ConStr to force OleDB to read it all as Text 
Dim TextConStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\capitals.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"

' get the typed columns into a DT - skip Rank as dirty column
Dim SQL = "SELECT Country, Capital, Population FROM [Capitals$]"
Using con As New OleDbConnection(TypedConStr),
    da As New OleDbDataAdapter(SQL, con)

    dsPop.Tables.Add("Pop")
    da.Fill(dsPop.Tables("Pop"))
End Using

' create a new temp DT containing just the naughty column
' use the generic F/Field index in the SQL (we told Ole there was no header)
SQL = "SELECT F4 As RankText FROM [Capitals$]"
' create connection forcing the contents to text:
Using con As New OleDbConnection(TextConStr),
    da As New OleDbDataAdapter(SQL, con)

    dsPop.Tables.Add("RankText")
    da.Fill(dsPop.Tables("RankText"))
End Using
' remove the header row
dsPop.Tables("RankText").Rows.RemoveAt(0)

'create a new INT col in Dt(0)
dsPop.Tables("Pop").Columns.Add("Rank", GetType(Int32))

' convert Tbl(1) text to Int and store in Tbl(0) 
For n As Integer = 0 To dsPop.Tables(1).Rows.Count - 1
    dsPop.Tables("Pop").Rows(n).Item("Rank") = 
           Convert.ToInt32(dsPop.Tables("RankText").Rows(n).Item(0).ToString)
Next

'optional: remove the [RankText] tbl since we are done with it
dgv.DataSource = dsPop.Tables("Pop")

' report the datatype of the last row rank:
tbDataType.Text = dsPop.Tables("Pop").Rows(14).Item("Rank").GetType.ToString

In the immediate window, the types reported are as expected:

? dspop.Tables("Pop").Rows(0).Item(2)       ' (population - paris)
2.25 {Double}
? dspop.Tables("RankText").Rows(0).Item(0)  ' temp table text
"7" {String}
? dspop.Tables("Pop").Rows(0).Item(3)       ' converted, merged value
7 {Integer}

For me, OleDB is automatically converting '3 to "3". In other words, it is omitting the leading tick/apostrophe when it converts to text. Since there can be numerous possibilities resulting from the combinations of Excel versions and OleDB.ACE and OleDb.Jet, we might want a fall back converter (I wrote this after adding back ticks to Excel by accident, maybe it will of value to someone):

Private Function GetNumericValue(s As String) As Integer
    ' ToDo add exception handling
    If Char.IsDigit(s(0)) Then
        Return Convert.ToInt32(s)
    Else
        Return Convert.ToInt32(
            New String(s.ToCharArray(1, s.Length - 1))
            )
    End If
End Function

It will only examine the first char for a non-numeral, otherwise it might convert "1234 Main Street Suite 56" to 123456 which is likely not desirable. The result:

enter image description here
Russia, Japan and Portugal were the rows which had the Rank data escaped as text.

Resources: