ADO.NET Provider for Microsoft Excel

Build 23.0.8839

Columns

To access metadata for the columns available in the database, retrieve the Columns schema collection. You can also access the column metadata for views by retrieving the ViewColumns schema collection.

Alternatively, retrieve metadata from Result Sets. The same columns are returned for result set metadata as the Columns and ViewColumns schema collections; see below.

Retrieving Column Metadata

Call the GetSchema method of the ExcelConnection class to retrieve the Columns or ViewColumns schema collections. You can restrict results by table name, as shown in the example below.

C#

string connectionString = "URI=C:\MyExcelWorkbooks\SampleWorkbook.xlsx;";

using (ExcelConnection conn = new ExcelConnection(connectionString)) {
  conn.Open();
  DataTable databaseSchema = conn.GetSchema("Columns", new string[] {"Sheet"});
  foreach (DataRow column in databaseSchema.Rows) {
    Console.WriteLine(column["COLUMN_NAME"]);
    Console.WriteLine(column["IS_KEY"]);
    Console.WriteLine(column["DATA_TYPE"]);
  }
}

VB.NET

Dim connectionString As String = "URI=C:\MyExcelWorkbooks\SampleWorkbook.xlsx;"

Using conn As New ExcelConnection(connectionString)
  conn.Open()
  Dim databaseSchema As DataTable = conn.GetSchema("Columns", New String() {"Sheet"})
  For Each column As DataRow In databaseSchema.Rows
    Console.WriteLine(column("COLUMN_NAME"))
    Console.WriteLine(column("IS_KEY"))
    Console.WriteLine(column("DATA_TYPE"))
  Next
End Using

Columns Returned

The Columns and ViewColumns schema collections and DataTables returned from the query contain the following columns.

Column NameData TypeDescription
TABLE_CATALOGSystem.StringThe database containing the table.
TABLE_SCHEMASystem.StringThe schema containing the table.
TABLE_NAMESystem.StringThe table containing the column.
COLUMN_NAMESystem.StringThe column name.
ORDINAL_POSITIONSystem.Int32The sequence number of the column.
COLUMN_DEFAULTSystem.StringThe default value of the column.
IS_NULLABLESystem.StringWhether the column allows null values. This value is YES or NO.
DATA_TYPESystem.StringThe column data type.
CHARACTER_MAXIMUM_LENGTHSystem.Int32The maximum length in characters of a column with character data.
NUMERIC_PRECISIONSystem.Int32The maximum number of digits allowed for numeric data.
NUMERIC_SCALESystem.Int32The maximum column scale or the number of digits to the right of the decimal point in numeric data.
DATETIME_PRECISIONSystem.Int32Returns the precision in fractional seconds if the parameter type is datetime or smalldatetime. Otherwise, returns NULL.
CHARACTER_SET_NAMESystem.StringThe name of the character set for a column with character data.
COLUMN_COMMENTSystem.StringA brief description of the column.
PROVIDER_TYPESystem.TypeIndicates the appropriate data type dependent on the language you are executing in.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839