CODEWITHSUNDEEP
vb.netexport-to-excel
Madam Zu Zu
Madam Zu Zu

Reputation: 6605

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

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

Upvotes: 3

Views: 2524

Answers (2)

mattmc3
mattmc3

Reputation: 18325

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.

Upvotes: 5

Mike Mozhaev
Mike Mozhaev

Reputation: 2415

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.

Upvotes: 1

Related Questions