IBM Support

Problem accessing xls spreadsheet from ODBC database adapter when Table name contains a space

Technote (troubleshooting)


Using the ODBC database adapter to connect with an Excel worksheet, where the Table name contains a space, will result in the following error when running a query: Error in SQLExecDirect

Message: [Microsoft][ODBC Excel Driver] Syntax error in query. Incomplete query clause.
SQL State: S1000


The following steps may be used to recreate the problem.

- Set up a system DSN referencing an Excel .xls file with a table name that contains a space.
- Configure the MDQ file using the above system DSN.
- Create a simple SELECT query like: SELECT * FROM 'TABLE 1$'
- Generate a type tree for the query, which will cause an error to occur.
- The ODBC database adapter trace will show the following error:

Message: [Microsoft][ODBC Excel Driver] Syntax error in query. Incomplete query clause.
SQL State: S1000


The problem occurs when a query is used to reference an .xls table name which contains a space and the standard single quote syntax is not supported by the ODBC driver.

Diagnosing the problem

If you run a database query from Database Interface Designer or run a DBQUERY / DBLOOKUP function from a map rule and the above error occurs, then review the query to determine whether the table name contains a space.

Resolving the problem

The problem may be resolved by surrounding the table name in the query with a set of double quotes in addition to the existing single quotes.

Database Interface Designer Query example:

SELECT * FROM "'Sheet 1$'"

In a map rule, the double quote characters may need to be represented as the Hex 22 value in order to distinguish them from the existing double quotes that surround the entire SQL query.

Map rule (using Hex 22 value for the double quotes) example:

=DBLOOKUP("select * from <<22>>'Sheet 1$'<<22>>","-DBTYPE odbc -SOURCE LocalExcel -T")

Document information

More support for: Transformation Extender
Database Adapter

Software version: 8.2, 8.3, 8.4

Operating system(s): Windows

Reference #: 1622292

Modified date: 15 January 2013