System.DBNull when reading excel file / column with mixed data

8 Nov 2005 3:17 PM

Nickneem

I'm having a hard time creating a dataset from an excel worksheet

[WORKSHEET]
Auto    Drop    DEB    Leverdatum
1    1    33370    9-11-2005
1    2    30518    9-11-2005
11    10    D9930    9-11-2005
12    1    5330    9-11-2005
12    2    18050    9-11-2005

[VB.Net function]
Public Function CreateDataSetFromExcel(ByVal MyFileName As String,
ByVal MyFilePath As String) As DataSet
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & MyFilePath & MyFileName & ";Extended Properties=""Excel
8.0;HDR=Yes;IMEX=1"""

        Dim objConn As OleDbConnection = New OleDbConnection(strConn)
        Dim objComm As OleDbCommand = New OleDbCommand("select
leverdatum, deb, auto, drop from [drop$]", objConn)

        Dim MyAdapter As New OleDbDataAdapter(objComm)
        Try
            Dim TempDs As New DataSet

            objConn.Open()
            MyAdapter.Fill(TempDs)
            objConn.Close()
            Return TempDs

        Catch ex As Exception
            objConn.Close()
        End Try
    End Function

It's because of the mixed datacolumns in column "DEB".
According to http://www.connectionstrings.com/ adding IMEX=1 should do
the trick (force to read the column as text) but it won't.
I still get system.dbnull for my D9930 value:

? tmpds.Tables(0).Rows(2).ItemArray
{Length=4}
    (0): #11/9/2005#
    (1): {System.DBNull}
    (2): 11.0 {Double}
    (3): 10.0 {Double}

Thanks in advance,

Mike

9 Nov 2005 10:51 AM

Venkat_KL

Dear Nickneem,

Read this,

How to Open and Read an Excel Spreadsheet into a ListView in .NET
============================================

http://www.c-sharpcorner.com/winforms/ExcelReadMG.asp

HOW TO: Use ASP.NET to Query and Display Database Data in Excel by Using
Visual Basic .NET
==================================================

http://support.microsoft.com/default.aspx?scid=kb;en-us;308247

ResxWriter: Generating .resx files from an Excel spreadsheet
=======================================
http://www.codeproject.com/csharp/ResxWriter.asp?print=true

For Anything & Everything, Please Let Me Know,

Bye
Venkat_KL

9 Nov 2005 1:32 PM

Nickneem

Thanks for your help, I think I can use one of these articles in the
future.
For now I want to stick with ADO and I've found a rather simple
solution (stupid me).
By sorting my excel file in VBA and putting the non-numeric cells on
top everything is read as text..

I've found more on the subject in this posting:
http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/browse_frm/thread/28b6b2767ef98215/24439bea659683e4?lnk=st&q=imex%3D1&rnum=1#24439bea659683e4

I quote:
"Setting IMEX=1 will not make the data type of the column Text unless
you have a mixed mode
(alphanumeric) value in the first eight rows.
If the first eight rows (TypeGuessRows=8 in the Registry) are numeric
then the column data type will
be numeric."

AND

"Check the following registry settings for the *machine*:
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

TypeGuessRows: setting the value to 0 (zero) will force ADO to scan
all column values before choosing the appropriate data type.

ImportMixedTypes: should be set to value 'Text' i.e. import mixed-type
columns as text:

Using IMEX=1 in the connection string (as you have done) ensures the
registry setting is applied."

I'm good for now with my sorting solution but I thought after reading
this post it completes the story.

Thanks again,

Michael