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

[Warren Gibson (JIRA)]

    [ https://jira.jboss.org/browse/TEIID-965?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563509#comment-12563509 ] 

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>


> 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