Populate a 2D array from a dataset imported from excel using data without column headers

Question

I'm trying to populate an array from a dataset imported from excel using data without column headers. My code is as follows:

Dim conn As OleDbConnection
Dim dta As OleDbDataAdapter
Dim dts As DataSet
Dim excel As String

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Try

        Dim OpenFileDialog As New OpenFileDialog
        With OpenFileDialog
            .InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
            .Filter = "All Files (*.*) | *.*"

            If .ShowDialog(Me) = System.Windows.Forms.DialogResult.OK Then
                Dim fi As New IO.FileInfo(.FileName)
                Dim FileName As String = .FileName
                excel = fi.FullName
                Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & excel & ";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"""
                conn = New OleDbConnection(connString)
                dta = New OleDbDataAdapter("Select * from [Sheet1$]", conn)
                dts = New DataSet
                dta.Fill(dts, "[Sheet1$]")
                Dim arrCol0 As Double() = (From myRow In dts.Tables(0).AsEnumerable
                                           Select myRow.Field(Of Double)("[A]")).ToArray
                conn.Close()
            End If
        End With

    Catch ex As Exception
        MsgBox(ex.Message)
        conn.Close()
        Exit Sub
    End Try
End Sub

The [A] in "Select myRow.Field(Of Double)("[A]")" appears to be required. Is there a way to exclude the column heading?

Answer 1

You would create the array based on the number of columns and rows in the DataTable and then you would traverse the DataTable and the array by column index and row index, e.g.

Dim arr(table.Columns.Count - 1, table.Rows.Count - 1) As Double

For columnIndex = 0 To arr.GetUpperBound(0)
    For rowIndex = 0 To arr.GetUpperBound(1)
        arr(columnIndex, rowIndex) = CDbl(table.Rows(rowIndex)(columnIndex))
    Next
Next