Reading Excel in .NET - some columns are read in as null

Question

i am trying to read in some data from an excel:

If (FileUpload1.PostedFile.ContentType = "application/vnd.ms-excel") Then
                Dim filename As String = Path.GetFileName(FileUpload1.FileName)
                'Session("userid") & "-" & Date.Now()
                filepath = "\excel\" & Session("userid") & "_" & Now.Date().ToString("Mdy") & "_" & filename
                FileUpload1.SaveAs(Server.MapPath("~/") & filepath)
                ReadExcel(filepath)

            Else
                StatusLabel.Text = "Only Excel file types are accepted"
            End If

everything seems to be fine, except when one of the columns sometimes reads in as NULL. seems like that happens if it's of a different type. i can't figure out what it is. someone help me please....

Sub ReadExcel(ByVal filepath As String)
    Dim MyConnection As System.Data.OleDb.OleDbConnection
    Dim DtSet As System.Data.DataSet
    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Server.MapPath("~/") & filepath & "';Extended Properties=Excel 8.0;")

    DtSet = New System.Data.DataSet
    Try
        MyCommand.Fill(DtSet)
        'gwResults.DataSource = DtSet.Tables(0)
        LoopSources(DtSet)
        'gwResults.DataBind()
        MyConnection.Close()
    Catch ex As Exception
        StatusLabel.Text = "Import Excel status: The file could not be loaded. The following error occured: <br>" + ex.Message
    End Try

End Sub

mattmc3 · Accepted Answer · 2011-07-15 20:48:49Z

5

You need to include Extended Properties="Excel 8.0;IMEX=1;" in your connection string. Excel tries to determine the data type of your columns by reading the first few rows. Once it thinks it knows the data type, anything that doesn't conform to that is coerced to NULL. Very annoying. IMEX=1 tells it to read your data as intermixed and should resolve your problem. If you have a header row, you should also consider including HDR=YES. See this link: http://www.connectionstrings.com/excel for more info.

edited Jul 15, 2011 at 20:48

answered Jul 15, 2011 at 19:18

mattmc3

18.6k8 gold badges88 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Madam Zu Zu Over a year ago

new connection string: MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Server.MapPath("~/") & filepath & "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'") now works! thank you!!!

mattmc3 Over a year ago

No problem. I feel you pain as I had to figure this one out myself some time ago. But, if you really want to thank me, you could always mark your question as answered :) Happy coding!

John Hartley Over a year ago

I have been battling with DBNull all morning. Thanks !

Mike Mozhaev · Accepted Answer · 2011-07-15 19:20:27Z

1

Probably the type of column is determined incorrectly. By default Jet provider reads 8 first rows to determine the type of column. And if these 8 lines contain something wrong you'll get a problem.

This can be changed by setting TypeGuessRows setting in registry.

answered Jul 15, 2011 at 19:20

Mike Mozhaev

2,47015 silver badges14 bronze badges

Collectives™ on Stack Overflow

Reading Excel in .NET - some columns are read in as null

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related