With Excel, each Excel workbook is logically considered to be a database holding several tables. Tables are mapped to sheets in a workbook. When you configure an ODBC data source name in the ODBC driver manager, you specify a default workbook name associated with that data source. However, when you issue a CREATE TABLE statement, you can override the default and specify a workbook name in the location string. This allows you to use a single ODBC DSN to access all of your excel workbooks.

In this example, a remote server named excel was created. To create a workbook named work1.xls with a sheet (table) called mywork:

CREATE TABLE mywork (a int, b char(20))
AT 'excel;d:\work1.xls;;mywork';

To create a second sheet (or table) execute a statement such as:

CREATE TABLE mywork2 (x float, y int)
AT 'excel;d:\work1.xls;;mywork2';

You can import existing worksheets into SQL Anywhere using CREATE EXISTING, under the assumption that the first row of your spreadsheet contains column names.

CREATE EXISTING TABLE mywork
AT'excel;d:\work1;;mywork';

If SQL Anywhere reports that the table is not found, you may need to explicitly state the column and row range you want to map to. For example:

CREATE EXISTING TABLE mywork
AT 'excel;d:\work1;;mywork$';

Adding the $ to the sheet name indicates that the entire worksheet should be selected.

Note in the location string specified by AT that a semicolon is used instead of a period for field separators. This is because periods occur in the file names. Excel does not support the owner name field so leave this blank.

Deletes are not supported. Also some updates may not be possible since the Excel driver does not support positioned updates.