A Microsoft Excel spreadsheet may be used as a data source using an appropriate ODBC driver. It is treated as an unsynchronized source database. We present a few tips to make the process easier:

The DSN should be configured appropriately, listing all necessary parameters including the name of the file containing the spreadsheet.

The source table name is usually the sheet name followed by a dollar sign. For example, Sheet1$. However, as this is a non-standard table name, ODBC requires it to be surrounded by back quotes: ‘Sheet1$‘. Unfortunately, back quotes are not permitted by the SDF syntax. This may be overcome by creating a Named Range in Excel.

Select the entire sheet, or the portion to be made visible to IIR, followed by the menu options

Insert

Name

Define...

and specify a name for the range using alphanumeric characters only (e.g.

test_named_range

). This name is then specified as the table name in the SDF definition.

create_IDT xltest
sourced_from odb:99:ssa/ssa@myExcel
        test_named_range.id (PK),
        test_named_range.name NAME C(50),
        test_named_range.address ADDRESS C(50),
        test_named_range.city CITY C(50),
        test_named_range.state STATE C(2),
        test_named_range.zip ZIP C(10)
NOSYNC
;