[teiid-issues] [JBoss JIRA] Issue Comment Edited: (TEIID-965) Need Excel file importer / connector
Warren Gibson (JIRA)
jira-events at lists.jboss.org
Mon Nov 15 20:41:45 EST 2010
[ https://jira.jboss.org/browse/TEIID-965?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563509#comment-12563509 ]
Warren Gibson edited comment on TEIID-965 at 11/15/10 8:41 PM:
---------------------------------------------------------------
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 file. 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 name 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 the DSNs pointing to the source file.
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 <xxx> and password <xxx>)
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>
was (Author: wgibson at jboss.org):
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>
> Need Excel file importer / connector
> ------------------------------------
>
> Key: TEIID-965
> URL: https://jira.jboss.org/browse/TEIID-965
> Project: Teiid
> Issue Type: Feature Request
> Components: Misc. Connectors
> Affects Versions: 7.0
> Reporter: Steven Hawkins
>
> Really need to add an Excel connector in SP1. The easiest way to do this is probably with one of the open source JDBC drivers that work over Excel files.
> Something like:
> https://xlsql.dev.java.net/ - Excel JDBC driver (GPL unfortunately, so we probably can't distribute the driver)
> http://www.andykhan.com/jexcelapi/ - Java Excel API (GPL too)
> http://xlsjdbc.sourceforge.net/ - LGPL (uses Apache POI), really cheesy and maybe not being updated
> http://jakarta.apache.org/poi/ - Excel API, event-based and not particularly friendly
> http://www.vistaportal.com/products/vistajdbc-QA.htm - commercial
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the teiid-issues
mailing list