Data Flask

Excel

Excel uses the Access (MsJet) drivers.

Settings Initializing the Microsoft Excel Driver (External)

Column Type

If Excel was used to create the .XLS, OleDb will interpret the first row of an .xls sheet as a list of column names, and will scan 8 rows to determine type (char, int, float, date, currency).

If OleDb was used to create the sheet, the sheet will retain the column type. BINARY types are not allowed, but types such as DATE and BOOL are.

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 Data Flask will select a bookmark automatically as an update key. Bookmarks are row numbers.

A Key Column can be specified in the Flask 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\flask.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.