ADO.NET Provider for Excel

Build 20.0.7655

Advanced Settings

Advanced Data Modeling Settings

Use the following properties to control the tables the provider discovers, return values, and other aspects of data access.

Schema Discovery

  • Orientation: Model tables that match the orientation of your spreadsheet: Set this to Horizontal or Vertical.
  • DefineTables: Explicitly specify the ranges you want to access as tables, using the standard Excel range format: [Table Name]=[Sheet Name]![Range]. Specify multiple tables in a semicolon-separated list:
      DefineTables="Table1=Sheet1!A1:N25,Table2=Sheet2!C3:M53"
    Note that the provider automatically detects the named ranges in a sheet and reports them as tables. You can also specify a range in the FROM clause:
     SELECT * FROM Excel_Sheet#A5:F* 

Column Discovery

  • Header: Detect column names from the first row if true. Otherwise, the pseudo column names A, B, C, etc. will be used.
  • TypeDetectionScheme: Determines how the provider detects the data types of columns.
  • RowScanDepth: When using the row-scan type detection scheme, set this property to the number of rows to scan to determine column data types.

Advanced Excel Integration

Formulas

  • AllowFormula: Whether to insert or update the formula result or the formula expression.
  • Recalculate: Recalculate formulas when the data is changed.
  • IgnoreCalcError: Ignore errors that occurred when formulas are calculated.
  • HasCrossSheetReferences: Recalculate formulas across all worksheets when the data is changed; the provider will follow cross sheet references, but there is a performance cost.

Miscellaneous

  • EmptyValueMode: Configure how to handle cells that are empty. An empty cell is a cell that contains an empty string (e.g., =""). You can return an empty cell as an empty string or a null string (default: return an empty string.)
  • NullValueMode: Configure how to handle cells that have not been set. An unset cell is different from an empty cell. Unset cells contain a null string. You can return an unset cell as an empty string or a null string (default: return NULL).
  • BufferChanges: Indicates whether to hold changes to the data in memory until the connection is closed.

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7655