Fine-Tuning Data Access
Advanced Data Modeling Settings
Use the following properties to control the tables the adapter discovers, return values, and other aspects of data access.
- 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 adapter 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*
- 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 adapter 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
- 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 adapter will follow cross sheet references, but there is a performance cost.
- 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.