[teiid-issues] [JBoss JIRA] Commented: (TEIID-965) Need Excel file importer / connector

[ https://jira.jboss.org/browse/TEIID-965?page=com.atlassian.jira.plugin.sy... ] Warren Gibson commented on TEIID-965: ------------------------------------- 11-15-2010 WG - I was able to connect a MS Excel source to a Teiid VDB and query it using the following steps. ODBC Excel as a Teiid source Test Environment: Windows with Excel 2003 or later, designer installed, Teiid installed, and a jdbc client such as Squirrel installed. 1. Open Designer and create a new project. 2. Create a Source Model with a Base Table representing the columns in the Excel files. I used a source model name to match the Excel target source. 3. Create a virtual table with same columns as the base table. 4. Create a vdb for Excel with a unique jndi name. The unique jndi names will also be used in the ds file and as the Windows DSN name. Used jdbc-simple as the Translator. 5. Deployed the vdb to a running Teiid instance. 6. Use the Windows ODBC Data Source Administration tool to create two DSNs pointing to the source files. 7. Create a ds file for Excel using the jndi-name in the vdb. See example below. 8. Copy ds file to server deploy directory. 9. Use a JDBC client such as Squirrel to query the vdbs. The Teiid client jar (teiid-<x.xx>-client.jar) is used to set up a driver in Squirrel. Connect to vdbs. (example url:jdbc:teiid:<vdb>@mm://localhost:31000, user name admin and password teiid) 10 Queried the source table and virtual table with a good sampling of queries. DS FILE EXAMPLE: ?xml version="1.0" encoding="UTF-8"?> <datasources> <no-tx-datasource> <jndi-name>ExcelDS</jndi-name> <connection-url>jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};Dbq=<URL of source ,xls location></connection-url> <driver-class>sun.jdbc.odbc.JdbcOdbcDriver</driver-class> <transaction-isolation>TRANSACTION_NONE</transaction-isolation> <min-pool-size>1</min-pool-size> <max-pool-size>1</max-pool-size> <idle-timeout-minutes>5</idle-timeout-minutes> </no-tx-datasource> </datasources> -- This message is automatically generated by JIRA. - For more information on JIRA, see: http://www.atlassian.com/software/jira