reading excel returns some rows as null

[lleemon]

I have a vb.net app that I can read a .xls file into a dataset or loop through as oledbdatareader and it gives the same result. Some of the rows have all nulls when I loop through them but if I open the same file in Microsoft Excel I can see that row fine.

Here is an example:

SKU | Title
5256 | X Tree
5534 | Y Tree
1020-CR | Z Tree

From the info above the first two SKU values would come through fine but when I get to the 3rd row it would show a null.

Is it possible that it kind of inherits the first rows type and since the 3rd row isn't all numeric if nulls it?

Any thoughts?

Imports System.Data.OleDb 'for reading excel file
Imports System.Data.SqlClient

Dim myDataSet As New DataSet()

FillDataSet(sFilePath, "Product info")

Dim tbl As DataTable = myDataSet.Tables(0)
Dim i As Integer = 0
Do While (i < tbl.Rows.Count)
            Dim myRow As DataRow = tbl.Rows(i)
            If IsDBNull(myRow("SKU")) = False Then
'do something
            End If

            i = (i + 1)
        Loop



Sub FillDataSet(ByVal sFile As String, ByVal sTab As String)
        'old
        'Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        '    "Data Source=" & sFile & ";" & _
        '    "Extended Properties=""Excel 8.0;"""

        Dim containsHDR As String = "Yes"  'Change to "No" if it doesn't
        Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & _
                        sFile & "';Extended Properties='Excel 12.0;HDR=" & containsHDR & ";IMEX=1'"

        ''You must use the $ after the object you reference in the spreadsheet
        Dim myData As New OleDbDataAdapter("SELECT * FROM [" & sTab & "$]", strConn)
        myData.TableMappings.Add("Table", "ExcelTest")
        myData.Fill(myDataSet)
    End Sub

[kareninstructor]

Try the following syntax for reading your Sheet as shown in the attached VS2008 project which reads a Excel 2007 sheet where the first row has headers, IMEX=1

Basic code

Public Class frmMainForm
    Private ExcelFileName As String = IO.Path.Combine(Application.StartupPath, "Products.xlsx")
    WithEvents bsProducts As New BindingSource
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dtProducts As New DataTable

        Using cn As New OleDbConnection
            Dim Builder As New OleDbConnectionStringBuilder With _
                { _
                    .DataSource = ExcelFileName, _
                    .Provider = "Microsoft.ACE.OLEDB.12.0" _
                }


            Builder.Add("Extended Properties", "Excel 12.0; IMEX=1;HDR=Yes;")

            cn.ConnectionString = Builder.ConnectionString

            cn.Open()

            Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
                '
                ' Note that each column uses an alias. Of course we can
                ' also use SELECT * FROM [Products$]
                '
                cmd.CommandText = _
                <SQL>
                    SELECT 
                        ProductID As Identifier, 
                        ProductName As Product, 
                        QuantityPerUnit As Quantity 
                    FROM 
                        [Products$]
                </SQL>.Value

                Dim dr As System.Data.IDataReader = cmd.ExecuteReader
                dtProducts.Load(dr)
                dtProducts.Columns("Identifier").ColumnMapping = MappingType.Hidden
                bsProducts.DataSource = dtProducts
                DataGridView1.DataSource = bsProducts
                DataGridView1.AutoResizeColumns()

                Dim Position As Integer = bsProducts.Find("Product", "Alice Mutton")

                If Position > -1 Then
                    bsProducts.Position = Position
                End If

            End Using

        End Using
    End Sub
End Class