Getting a table schema from an Excel File in ADO.NET

  • Thread starter Thread starter Garry
  • Start date Start date
G

Garry

Sorry - previous post has incorrect title

I am using the following code which appears in slightly different versions
in many articles on the web

Dim excelConn As New System.Data.OleDb.OleDbConnection
Dim dtXlsSchema As DataTable
Dim int As Integer

excelConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & strExcelFileName & ";" & "Extended Properties='Excel
8.0;HDR=Yes;IMEX=1'"

excelConn.Open()


dtXlsSchema =
excelConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New
Object() {Nothing, Nothing, Nothing, "TABLE"})

For int = 0 To dtXlsSchema.Rows.Count - 1
Debug.WriteLine(dtXlsSchema.Rows(int).Item("Table_Name").ToString)
Next

The value in dtXlsSchema.Rows.Count is always zero.

If in code I tried to build a DataSet from the excel file, I use a
TableName/WorksheetName with the syntax strWorksheetName & "$;" with or
without square brackets, with or without the dollar sign, I get an error
saying that vs.net, cannot find the object {TableName}.

The Excel file exists and has 5 worksheets. The Open() succeeds without any
error message.

Can anyone tell me what I am doing wrong??

Code after this example:

dtXlsSchema = excelConn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Columns,
New Object() {Nothing, Nothing, strWorksheetName & "$", "TABLE"})
For int = 0 To dtXlsSchema.Columns.Count - 1
Debug.WriteLine(dtXlsSchema.Columns(int).ToString)
Next

does have a value(28)



Garry
 
¤ Sorry - previous post has incorrect title
¤
¤ I am using the following code which appears in slightly different versions
¤ in many articles on the web
¤
¤ Dim excelConn As New System.Data.OleDb.OleDbConnection
¤ Dim dtXlsSchema As DataTable
¤ Dim int As Integer
¤
¤ excelConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
¤ Source=" & strExcelFileName & ";" & "Extended Properties='Excel
¤ 8.0;HDR=Yes;IMEX=1'"
¤
¤ excelConn.Open()
¤
¤
¤ dtXlsSchema =
¤ excelConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New
¤ Object() {Nothing, Nothing, Nothing, "TABLE"})
¤
¤ For int = 0 To dtXlsSchema.Rows.Count - 1
¤ Debug.WriteLine(dtXlsSchema.Rows(int).Item("Table_Name").ToString)
¤ Next
¤
¤ The value in dtXlsSchema.Rows.Count is always zero.
¤
¤ If in code I tried to build a DataSet from the excel file, I use a
¤ TableName/WorksheetName with the syntax strWorksheetName & "$;" with or
¤ without square brackets, with or without the dollar sign, I get an error
¤ saying that vs.net, cannot find the object {TableName}.
¤
¤ The Excel file exists and has 5 worksheets. The Open() succeeds without any
¤ error message.
¤
¤ Can anyone tell me what I am doing wrong??
¤
¤ Code after this example:
¤
¤ dtXlsSchema = excelConn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Columns,
¤ New Object() {Nothing, Nothing, strWorksheetName & "$", "TABLE"})
¤ For int = 0 To dtXlsSchema.Columns.Count - 1
¤ Debug.WriteLine(dtXlsSchema.Columns(int).ToString)
¤ Next
¤
¤ does have a value(28)

The above code is correct for retrieving the column schema for a specified table.

The syntax for retrieving a list of tables is the following:

SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing,
Nothing})

You do not need to specify parameter values in the call when retrieving the list of table names.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top