Reading Excel Using OLEDB when column header contain spaces and special characters

I am using the following answer to a previous question.  I am using Visual Basic 2010.
Read Data from Excel using OLEDB
http://vb.net-informations.com/excel-2007/vb.net_excel_oledb.htm
=============
My problem is that I don't have control over the Excel sheet, and the column headers contain spaces and other non-alphanumeric characters.  I need to be able to refer to individual columns (fields), but I need help with the syntax, OR find a way to ignore the Excel column headers and be able to refer to the fields with the generic F1, F2, etc (which is what happens if row 1 is empty).

Typical column headers as an example:  "A: Address"  "B: Name"

And this will fail, but I don't know the right syntax to make it work...
("select 'A: Address' from [Sheet1$]", MyConnection)

Similarly, this will fail...
("select * from [Sheet1$] where 'B: Name' like 'Fred%'", MyConnection)



Imports System.Data
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles Button1.Click
        Try
            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='c:\testfile.xls'; " _
             "Extended Properties=Excel 8.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter _
                ("select * from [Sheet1$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "TestTable")
            DtSet = New System.Data.DataSet
            MyCommand.Fill(DtSet)
            DataGridView1.DataSource = DtSet.Tables(0)
            MyConnection.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class

Open in new window

motokentAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
If I am not mistaken it is not single-quoted, but square-bracketed.

("select * from [Sheet1$] where [B: Name] like 'Fred%'", MyConnection)
0
 
motokentAuthor Commented:
That's it!  Thank you for the quick response.  Before I close it... can you tell me if there's a way to tell it to ignore the field names (basically assume that row 1 contains data rather than column header)?
0
 
cyberkiwiConnect With a Mentor Commented:
Have a look  http://www.connectionstrings.com/excel

The connection string should contain HDR=No;
e.g.  Data Source='c:\testfile.xls';HDR=No;
0
 
motokentAuthor Commented:
Excellent.  This helped a lot.
0
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.