Read schema information from Excel worksheets with ADO.NET

The ADO connection object (be it OleDBConnection or SqlConnection) exposes a GetOleDbSchemaTable method used to retrieve schema information from a database.

GetOleDbSchemaTable takes 2 parameters. The first is an OleDbSchemaGuid value that specifies the type of schema to retrieve. Parameter 2 is an object array of restrictions in the order: Table_Catalog, Table_Schema, Table_Name and Column_Name. Restrictions are used to limit the information returned. The code below helps to clarify this.

This snippet shows how to retrieve a list of tables, the schema for a specific table and the column names for a specific table. It works whether the database is Oracle, SQL Server,... Just change the connection string appropriately. For Excel, you must supply the path to the .XLS file. The worksheets in an Excel file are treated as tables and typically end with a "$".

    Dim i As Integer
    Dim dtXlsSchema As DataTable
    Dim myConn As New OleDbConnection
    Dim XlsConn As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
        "Source=C:\temp\myWorksheet.xls;" & _
        "Extended Properties=Excel 8.0"
    '
    ' Open an ADO connection to the Excel file.
    '
    myConn.ConnectionString = XlsConn
    myConn.Open()
    '
    ' Get a list of tables (worksheets) in the XLS file.
    '
    dtXlsSchema = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                      New Object() {Nothing, Nothing, Nothing, "TABLE"})

    For i = 0 To dtXlsSchema.Rows.Count - 1
        Debug.WriteLine(dtXlsSchema.Rows(i).Item("Table_Name").ToString)
    Next
    '
    ' Get the schema for the specified table.
    ' Change "MyTableName" to the actual worksheet name.
    '
    dtXlsSchema = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
                      New Object() {Nothing, Nothing, "MyTableName$", "TABLE"})

    For i = 0 To dtXlsSchema.Columns.Count - 1
        Debug.WriteLine(dtXlsSchema.Columns(i).ToString)
    Next
    '
    ' List the columns for the specified table.
    '
    dtXlsSchema = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
                      New Object() {Nothing, Nothing, "MyTableName$", Nothing})

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

    myConn.Close()