IQ Excel ODBC Connection (Verified Collected Digits)
We have found issues and restrictions when using ODBC connections to an Excel Spreadsheet. They are listed here, with the associated error that is printed out in the 6160 UI when testing the ODBC connection.
1. Opened Excel Spreadsheets
The first restriction is that not more than one program must be using the Excel spreadsheet. For example, if you are currently editing the spreadsheet, and testing it in the UI, it will fail, and give the following error message:
TESTING ODBC CONNECTION...
TEST FAILED WITH ERROR:
ERROR [HY000] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [HY000] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.
It’s important to state that the actual verified collected digits (in the Messaging Service), will fail as well if the Excel document is being used by another program.
2. Table Definition
To set up your Excel Spreadsheet, enter your data in rows and columns. However, by doing only that, you haven’t defined where the ODBC query will search in your excel document. If you try to test the query in the 6160, on a table with no table definition, you will get the following error message:
TESTING ODBC CONNECTION...
CONNECTION TEST PASSED
TESTING QUERY: SELECT Extension FROM Directory WHERE Extension = '1142';
TEST FAILED WITH ERROR:
ERROR [42S02] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object '<TableName>'. Make sure the object exists and that you spell its name and the path name correctly.
The ODBC driver needs to know what area it needs to search. Select the searchable area, and select Insert > Name > Define.
This window will appear, and the ODBC Table Name, as entered in the 6160, will be the same as the one defined in this window.
Note that any data outside of the region will not be searchable.
3. Column Definition
The Excel spreadsheet searchable region needs to have column names. By default, it will use the first rows in the region, and use their values as column names. If you attempt to test the ODBC Connection on inexistent rows, the following error will be returned:
TESTING ODBC CONNECTION...
CONNECTION TEST PASSED
TESTING QUERY: SELECT Extension FROM TableName WHERE Extension = '1142';
TEST FAILED WITH ERROR:
ERROR [07002] [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1.
For example, the column names in the above region would be named Name and PhoneNumber.
4. String Values
Another important restriction is that the value has to be a text value. Entering a number in Excel will automatically default it to a number value, while the 6160 ODBC connection only supports alphanumerical values. If you run a query on a column containing numbers (or other kinds of data) you will get the following error when testing the query in the 6160:
TESTING ODBC CONNECTION...
CONNECTION TEST PASSED
TESTING QUERY: SELECT PhoneNumber FROM TableName WHERE PhoneNumber = '1119';
TEST FAILED WITH ERROR:
ERROR [22018] [Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression.
This can be solved by prefixing number with an apostrophe, such as ‘1234.
5. Invalid Queries
An ODBC Connection to an Excel document has a limited set of operations. The SQL Queries that can be executed on an Excel spreadsheet are limited to 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. If using a query that is more advanced, the 6160 will return the following error when testing the ODCB Connection:
TESTING ODBC CONNECTION...
CONNECTION TEST PASSED
TESTING QUERY: IF EXISTS (SELECT * FROM NAME) return 'TRUE'ELSE return 'FALSE'
TEST FAILED WITH ERROR:
ERROR [42000] [Microsoft][ODBC Excel Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
6. Remote Excel Spreadsheet
An ODBC Connection to an Excel Spreadsheet can be made even if it is on the network. You have two choices:
- Writing the complete path when configuring the ODBCSource
Using this method, the ODBC Connection should work as it does locally (most likely provided that both computers are on the same domain, and that the 6160 user is logged on that domain)
- Mapping a network drive to your 6160 server, and grabbing the file from there.
We have tested this method, and could not get it to work in the UI. Here is the returned value:
TESTING ODBC CONNECTION...
TEST FAILED WITH ERROR:
ERROR [HY024] [Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [HY024] [Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
However, this method works in the Messaging Service (provided that the ODBC Connection and Query have been set up properly).