Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Remote data access » Server classes for remote data access » Server class ODBC


Microsoft Excel (Microsoft 3.51.171300)

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

Create a remote server named excel that connects to the Microsoft Excel ODBC driver.

USING 'DRIVER=Microsoft Excel Driver (*.xls);DBQ=d:\\work1.xls;READONLY=0;DriverID=790'

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 sheets into SQL Anywhere using CREATE EXISTING, under the assumption that the first row of your sheet contains column names.


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:

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.