2010 Office System Driver: Access Database Engine (32-bit)
Settings: Initializing the Microsoft Excel Driver
Excel uses the Access (Office) drivers.
Access Column Type List
Column types can be Long, Double, VarChar, Date, or Currency when read from an Excel file. By default, the column type is identified by sampling values.
A common problem when working with Excel files is that the column names (Headers) are not always in the first row.
Also, the automatic table name Excel provides (for example: Sheet1$Print_Titles) is not always useful.
To create a named table in Excel that can be read by SQL Offline (and other external programs):
EXCEL - COLUMN NAMES ON ROW 9
EXCEL - CREATE NAME DIALOG BOX
SQL OFFLINE DOCUMENT WITH COLUMNS NAMED IN EXCEL
With some restrictions, you can update a worksheet that is open in Excel.
If a worksheet is loaded (open) in Excel, SQL Offline can only link to existing sheets. This means that named tables do not display in the Source window, and you cannot create new sheets.
If you do attempt to create a new sheet (for example: you specify a new table in the data link and Write Data) while the XLS worksheet is loaded into Excel, the Office client driver may fail with an unrecoverable error (Unhandled exception at 0x58E22E21 in ACEWSTR.DLL) and shut down SQL Offline. To avoid this problem SQL Offline will not execute DDL when you Write Data if the Excel window is open. You can select Create Table in the Advanced Data Link dialog box to override this precaution.
Significant restrictions exist when changing existing columns.
Columns can only be added in sheets using a header, when the file has at least two columns, and has no rows. In practice, this means that columns can be added only into a new, empty sheet. Once data is written, new columns cannot be added.
Inserting a Name that Defines a range into an XLS or XLSX using Excel will cause that range to be available as a table (see steps above). Named ranges can be dropped. Sheets can be added but not dropped.
Excel worksheets (which have a dollar suffix$) will be emptied, not deleted on drop.
Keys and Indexes are ignored.
You cannot create a column that has a terminating space in the column name.
The Update key <Automatic> will select Bookmark automatically. Excel is the only provider that SQL Offline will select a bookmark automatically as an update key. Bookmarks are row numbers.
A Key Column can be specified in the Offline Grid. It will be used to match columns during updates, however the constraint will not be represented within the XLS or XLSX file.
Consider loading Customers from Nwind.mdb, as in the Crash Course Tutorial . Replace the connection string with a link to an Excel file. For Example: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\offline.xls;Extended Properties="Excel 8.0;HDR=YES" .
Use Admin/Create Database from an Excel Provider item the Data Window to create a new .XLS file.
A Write Data command would create a table as follows:
CREATE TABLE [Customers] (
Indexes and Keys are ignored when creating the table.
BINARY data types are not supported for Excel. However, you can use the Column Properties dialog to convert BINARY to STR in the grid, leaving a hex string. If a column length is set larger than 255 is selected, LongText will be chosen. For example the Categories table would create as follows:
CREATE TABLE [Categories] (
SQL Offline © 2014 Interscape Corporation