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

[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

 

[Guest]

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

 

[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/micr...683e4?lnk=st&q=imex=1&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/ImportMixedType­s


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