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

[Ripan Karmakar (Commented) (JIRA)]

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

Ripan Karmakar commented on TEIID-965:
--------------------------------------

We have created one client program to fetch data from text and csv data files taking Teiid support. This is working fine. We are using Teiid 7.6.0 and the JBoss 5.1.0 GA. But facing problem for excel (.xls) and xml files through we have followed the same approach.

 
The config are as below:

1.excel-file-ds.xml:

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
	<no-tx-datasource>
		<jndi-name>excel-file</jndi-name>
		<!-- <connection-url>jdbc:odbc:excel-odbc</connection-url> -->
		<connection-url>jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/abcd.xls</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>

 
2. Added entry in portfolio-vdb.xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="DynamicPortfolio" version="1">
    <description>A Dynamic VDB</description>    
    <property name="UseConnectorMetadata" value="true" />
	<model name="ExcelData">        
		<source name="excel-connector" translator-name="jdbc-simple" connection-jndi-name="java:excel-file"/>	
	</model>    	
</vdb>
 
3. abcd.xls

Name Address Country ZipCode
John 1st Street USA 12222
Mac 2nd Street USA 34421
Peter 3rd Street USA 54321

 
The connection Code:

String url = "jdbc:teiid:" + vdb + "@mm://" + host + ":" + port; 
Class.forName("org.teiid.jdbc.TeiidDriver"); 
DriverManager.getConnection(url, user, password);

 
The connection is getting created successfully.

But while executing the query we are not getting any result output. code snipet looks like this

 
sql = "SELECT * from (call ExcelDS.getTextFiles('abcd.xls')) f, TEXTTABLE(f.file COLUMNS NAME string,ADDRESS string,COUNTRY string,ZIPCODE bigdecimal HEADER) as stock ";

statement = connection.createStatement();

results = statementForCSV.executeQuery(sql);

results.next();

 
Running the program in debugging mode in eclipse we can see that the connection and statement all are created successfully but not getting any data from the ResultSet results.

 
Observations: We have also tried to change the query but not succeed.

a. sql = "SELECT * from [Sheet1$]"; // not working

b. sql = "SELECT * from (call ExcelData.getTextFiles('Sheet1$'))"; // not working

c. sql = "select * from (call ExcelDS.getTextFiles('abcd.xls'[Sheet1$])) "; // not working

d. sql = "select * from ExcelDS.[Sheet1$] "; // not working

When we tried to connect to the Teiid datasource, we can see that the table name as:

c:/abcd.Sheet1$

May be this is the reason that the tables cann't be queried. Can you also suggest what sql query needs to be used to query the excel.

Exception:
 with sql: "select * from ExcelData.c:/abcd.Sheet1$"; 

Exception in thread "main" org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryParserException: Parsing error: Encountered ":" at line 1, column 26.
 

                
> Need Excel file importer / connector
> ------------------------------------
>
>                 Key: TEIID-965
>                 URL: https://issues.jboss.org/browse/TEIID-965
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Misc. Connectors
>    Affects Versions: 7.0
>            Reporter: Steven Hawkins
>             Fix For: Open To Community
>
>
> 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.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira