How would you do a connection string in OLE DB and write a select * from Excel spreadsheet query?

I figured out how to connect to the Excel spreadsheet in my code, but the only thing holding me back is how to write a query string in with OLE DB.  How would I do a simple select * from (excel spreadsheet) and how would I do a select column1 from (excel spreadsheet)?

Who is Participating?
YZlatConnect With a Mentor Commented:
here is a function that thakes two parameters (1) path to your excel file, 2) is optional - Spreadsheet name. If not specified, uses "Sheet1" by default)reads data from Excel into a DataTable:

Function ReadDataFromExcel(ByVal path As String, Optional ByVal sheet As String = "Sheet1") As System.Data.DataTable
        Dim ds As New DataSet
        Dim da As OleDbDataAdapter
        Dim conn As OleDbConnection

            conn = New OleDbConnection( _
                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "data source=" & path & "; " & _
                  "Extended Properties=Excel 8.0;")

            da = New OleDbDataAdapter("SELECT * FROM [" & sheet & "$]", conn)


            ''get the name of the last column
            Dim lastCol As DataColumn
            lastCol = ds.Tables(0).Columns(ds.Tables(0).Columns.Count - 1)
            ''remove the last column from a DataTable

        Catch ex As Exception
            If conn.State = ConnectionState.Open Then
            End If
        End Try
        ReadDataFromExcel = ds.Tables(0)
    End Function
Bob LearnedCommented:
Select * From [Sheet1$]
Select Column1 From [Sheet1$]

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.