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.

CREATE SERVER excel
CLASS 'ODBC'
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 SAP Sybase IQ using CREATE EXISTING, under the assumption that the first row of your sheet contains column names.

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

If SAP Sybase IQ 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.

CREATE SERVER TestExcel
CLASS 'ODBC'
USING 'DRIVER=Microsoft Excel Driver (*.xls);DBQ=c:\\temp\\LogFile.xlsx;READONLY=0;DriverID=790'

CREATE EXISTING TABLE MyWorkbook
AT 'TestExcel;c:\\temp\\LogFile.xlsx;;Logfile$';

SELECT * FROM MyWorkbook;