Excel 2000 connection strings

Pseudo column names (A,B,C) are used instead.

To retrieve data from the cache, add "#Cache" to the table name. For example, to query cached data from the "Sheet" table, execute "SELECT * FROM [Sheet#Cache]".

The table metadata will also be cached instead of retrieving it from the data source. This improves connection performance. Read more here →

SELECT statements will always retrieve data from the cache. DELETE/UPDATE/INSERT statements is not allowed and will throw an exception.

Above is just an example to show how it works. It can be used both with "Auto Cache" and with "Cached Data Only / Offline Mode". Read more about using RSSBus Cache Provider in this article >>>

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

How to Use JET in 64 bit environments

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

"SELECT * FROM [Sheet1$a5:d]", start picking the data as of row 5 and up to column D.

Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

A workaround for the "could not decrypt file" problem

SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

ReadOnly = 0 specifies the connection to be updateable.

See the respective OLEDB provider's connection strings options. The .net OleDbConnection will just pass on the connection string to the specified OLEDB provider. Read more here.

See the respective ODBC driver's connection strings options. The .net OdbcConnection will just pass on the connection string to the specified ODBC driver. Read more here.