[ https://issues.jboss.org/browse/TEIID-965?page=com.atlassian.jira.plugin.... ] 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. -- 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