SQL Offline

Excel

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

Settings: Initializing the Microsoft Excel Driver

Excel uses the Access (Office) 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.

Naming Tables and Columns in Excel

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):

In Excel go to the last cell (CTRL+END), press and hold down SHIFT, and extend selection to the first cell (CTRL+HOME).
Continue to press and hold SHIFT while pressing the DOWN ARROW until the first selected row contains column names. Release SHIFT.

EXCEL - COLUMN NAMES ON ROW 9
With cells selected such that the first cell contains column names, click Define Name in the Formulas tab of the ribbon.
Enter a name in the New Name dialog box (for example: fcat_scores) and click OK.

EXCEL - CREATE NAME DIALOG BOX
If you save and open the XLS or XLSX file in SQL Offline the new name displays in the Source window.

SOURCE WINDOW
Open the named table to create an OL document.

SQL OFFLINE DOCUMENT WITH COLUMNS NAMED IN EXCEL

Linking to Open Worksheets

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.

Table Admin

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.

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 or XLSX 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.