SQL Offline

Excel

2010 Office System Driver: Access Database Engine (32-bit)

Settings: Initializing the Microsoft Excel Driver

Excel uses the Access (MsJet) drivers.

Column Types

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.

Table Admin

Significant restrictions exist when changing existing columns. Alter Table has been disabled as a result. 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 using Excel will cause that range to be available as a table. 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.

Updates

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 file.

Migration

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] (
[CustomerID] VarChar(5),
[CompanyName] VarChar(40),
[ContactName] VarChar(30),
[ContactTitle] VarChar(30),
[Address] VarChar(60),
[City] VarChar(15),
[Region] VarChar(15),
[PostalCode] VarChar(10),
[Country] VarChar(15),
[Phone] VarChar(24),
[Fax] VarChar(24)
)

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] (
[CategoryID] Long,
[CategoryName] VarChar(15),
[Description] VarChar(255),
[Picture] LongText
)

where longtext allows more than 255 characters to be written.