reading excel returns some rows as null

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?

Code:

---

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

---

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

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

---