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.

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* 
```

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.

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.

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.