ADO.NET Provider for Microsoft Excel

Build 23.0.8839

Fine-Tuning Data Access

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) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839