Excel Connection Settings

Data Model

Allow Formula

Whether or not to treat values starting with an equals (=) sign as formulas during inserts and updates.

Data Type

bool

Default Value

true

Remarks

Set this value to true to treat values beginning with an equals (=) sign as formulas on inserts and updates. Set the value to false to treat all values used in inserts and updates as strings.

Buffer Changes

Indicates whether to hold changes to the data in memory until the connection is closed.

Data Type

bool

Default Value

true

Remarks

Indicates whether to hold changes to the data in memory until the connection is closed. Setting this to false will write the changes immediately but there will be a performance hit when issuing lots of requests as the file must be opened and closed repeatedly.

Empty Value Mode

Indicates whether to read the empty values as empty or as null.

Data Type

string

Default Value

"ReadAsEmpty"

Remarks

Possible values are ReadAsEmpty and ReadAsNull. An empty value is a cell which contains an empty string (e.g., =""), which is different from a cell that has not been set and thus contains a null string.

Excel File

The location of an Excel file.

Data Type

string

Default Value

""

Remarks

A path that points to an Excel file. The file must exist. The driver implements support for Office Open XML spreadsheets used by Excel 2007 and later.

Has Cross Sheet References

Indicates how cross sheet references are handled.

Data Type

bool

Default Value

false

Remarks

HasCrossSheetReferences controls how cross sheet references are handled. If there are cross sheet references, changes to one sheet may cause some other formulas in other sheets to be recalculated. If HasCrossSheetReferences is set to true, all sheets in the workbook will be loaded into memory and the cross sheet references would be handled. However, setting the property to true may slow down the performance because all sheets will be read from the Excel file.

Header

Indicates whether the first row should be used as a column header.

Data Type

bool

Default Value

true

Remarks

If true, the first row will be used as a column header. Otherwise, the pseudo column names A, B, C, etc. will be used.

Ignore Calc Error

Indicates whether to ignore errors that occurred during the calculation.

Data Type

bool

Default Value

false

Remarks

IgnoreCalcError defaults to false. If IgnoreCalcError is set to true, the provider will ignore any errors that occur due to formula calculation. If this happens, the formula result may be unreliable, but other data will be accurate.

Logfile

A path to the log file.

Data Type

string

Default Value

""

Remarks

For more control over what is written to the log file, take a look at Verbosity.

Max Log File Size

A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end.

Data Type

string

Default Value

""

Remarks

A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end. There is no limit by default. Values lower than 100kB will use 100kB as the value instead.

Null Value Mode

Indicates whether to read empty cells as null or as empty.

Data Type

string

Default Value

"ReadAsNull"

Remarks

NullValueMode controls how empty Excel cells are modelled. An empty cell is a cell that has not been set and thus contains a null string. If NullValueMode is set to ReadAsNull, NULL is reported for an empty cell; if NullValueMode is set to ReadAsEmpty, an empty string is reported for an empty cell.

Orientation

Indicates whether the data in Excel is laid out horizontally or vertically.

Data Type

string

Default Value

"Vertical"

Remarks

Normally Excel sheets have headers at the top and rows of data below it. This is the default orientation (vertical), but you can also read sheets where the headers are to the left and rows follow left to right. It can be logically more convenient to read data from such sheets using the horizontal orientation.

Other

The other parameters necessary to connect to a data source, such as username and password, when applicable.

Data Type

string

Default Value

""

Remarks

The Other property is a semicolon-separated list of name-value pairs used in connection parameters specific to a data source. Some of the other properties are:

 

   
CachePartial=True Caches only a subset of columns, which you can specify in your query.
QueryPassthrough=True Passes the specified query to the cache database instead of using the SQL parser of the driver.
RecordToFile=filename Records the underlying socket data transfer to the specified file.

 

 

Pseudo Columns

Indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; i.e., "*=*".

Readonly

You can use this property to enforce read-only access to Excel from the provider.

Data Type

bool

Default Value

false

Remarks

If this property is set to true, the driver will allow only SELECT queries. INSERT, UPDATE, DELETE, and stored procedure queries will cause an error to be thrown.

Recalculate

Indicates whether to recalculate all formulas when data is read.

Data Type

bool

Default Value

true

Remarks

When cells in an Excel sheet are modified, the formula cells change their value. If Recalculate is set to true, the provider will detect changed cells and recalculate the formulas. These new results are then updated back to the Excel sheet so that a recalculation is not necessary.

Row Scan Depth

Set this property to control control the number of rows scanned when TypeDetectionScheme is set to RowScan.

Data Type

string

Default Value

"15"

Remarks

Determines the number of rows used to determine the column data types.

Setting a high value may decrease performance. Setting a low value may prevent the data type from being determined properly, especially when there is null data.

Support Enhanced SQL

If the property is set to true, the provider will be able to handle SQL queries using a temporary database.

Data Type

bool

Default Value

true

Remarks

If the property is set to true, the provider will be able to handle SQL queries using a temporary database.

 

Tables

Comma-separated list of tables to be listed. For example: Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the driver.

Type Detection Scheme

Determines how to determine the data type of columns.

Data Type

string

Default Value

"ColumnFormat"

Remarks

 

 

   
None Setting TypeDetectionScheme to None will return all columns as string type.
RowScan Setting TypeDetectionScheme to RowScan will scan rows to heuristically determine the data type. The RowScanDepth determines the number of rows to be scanned.
ColumnFormat Setting TypeDetectionScheme to ColumnFormat will report the data type based on the cell format (Number, Currency, Date, etc.) of the column.

 

 

Verbosity

The verbosity level that determines the amount of detail included in the log file.

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the driver reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described below:

 

   
1 Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors.
2 Setting Verbosity to 2 will log everything included in Verbosity 1, cache queries, and HTTP headers.
3 Setting Verbosity to 3 will additionally log the body of the HTTP requests.
4 Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.
5 Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.

 

The Verbosity should not be set to greater than 1 for normal operation. Substantial amounts of data can be logged at higher verbosities, which can delay execution times.