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 location string. This allows you to use a single ODBC DSN to access all of your excel workbooks.
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 the database server 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';
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.