1 2 Previous Next 18 Replies Latest reply: Jan 19, 2012 10:39 AM by Ramesh Reddy RSS

Teiid for Excel Data Source

Ripan Karmakar Newbie

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>ExcelDS</jndi-name>
<connection-url>jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};Dbq=${jboss.server.home.dir}/teiid-examples/dynamicvdb-portfolio/data/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:

<model name="ExcelDS">
<source name="text-connector" translator-name="file" connection-jndi-name="java:excel-file"/>
</model>

 

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:

url = "jdbc:teiid:" + vdb + "@mm://" + host + ":" + port + ";showplan=on";
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 NAME, ADDRESS, COUNTRY, ZIPCODE 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

 

Same Exception : org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryParserException

 

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

 

Exception: org.teiid.jdbc.TeiidSQLException: Input length = 1

 

Pls help us to resolve the issues soon.

 

Regards,

Ripan K

  • 1. Re: Teiid for Excel Data Source
    Ramesh Reddy Master

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

     

    is right form. Please post the exception you are encountering here, not on the JIRA forum.

     

    Thanks


    Ramesh..

  • 2. Re: Teiid for Excel Data Source
    Ripan Karmakar Newbie

    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.

  • 3. Re: Teiid for Excel Data Source
    Ripan Karmakar Newbie

    Also as mentioned in the Jira Forum By Ramesh:

     

    It would be really great if you can elaborate on the connector mentioned above. Can you please specify the line where we need to make a change.

  • 4. Re: Teiid for Excel Data Source
    Ramesh Reddy Master

    Can you try

     

    SELECT * FROM "ExcelData.c:/abcd"."Sheet1$"
    

     

    Note the quotes in-line.

  • 5. Re: Teiid for Excel Data Source
    Ripan Karmakar Newbie

    Changed my code to

     

    sqlForExcel = "SELECT * FROM \"ExcelData.c:/abcd\".\"Sheet1$\"";

     

    Getting the following exception

     

     

     

     

    Exception in thread "main"

     

    org.teiid.jdbc.TeiidSQLException: Error Code:-3010 Message:Remote org.teiid.core.TeiidProcessingException

    : Error Code:-3010 Message:excel-connector: Error Code:-3010 Message:'[Microsoft][ODBC Excel Driver] Too few parameters. Expected 4.' error executing statement(s): [Prepared Values: [] SQL: SELECT `C:/abcd`.`Sheet1$`.`Name`, `C:/abcd`.`Sheet1$`.`Address`, `C:/abcd`.`Sheet1$`.`Country`, `C:/abcd`.`Sheet1$`.`ZipCode` FROM `C:/abcd`.`Sheet1$`]

    at org.teiid.jdbc.TeiidSQLException.create(

     

    TeiidSQLException.java:113

    )

    at org.teiid.jdbc.TeiidSQLException.create(

     

    TeiidSQLException.java:70

    )

    at org.teiid.jdbc.StatementImpl.postReceiveResults(

     

    StatementImpl.java:617

    )

     

     

     

    Can you also please verify that my model is correct?

    <model name="ExcelData">
         <source name="excel-connector" translator-name="jdbc-simple" connection-jndi-name="java:excel-file"/>
    </model>

  • 6. Re: Teiid for Excel Data Source
    Ramesh Reddy Master

    Ripan,

     

    Yes, your model fragment looks correct. There seem to be issues you are facing with DSN-less connection. So, let's try with DSN.

     

    Create "ODBC" data source to the Excel file, using the Microsoft "administrative tools" in control panel. Double click "data sources" and create a DSN for the Excel file using the Excel driver. Once you are done, then edit the "excel-file-ds.xml" file and change the connection-url to

     

    <connection-url>jdbc:odbc:{DSN}</connection-url>

     

    restart the JBoss AS, then try executing

     

    select * from Sheet1$

  • 7. Re: Teiid for Excel Data Source
    Ripan Karmakar Newbie

    Ramesh,

     

    I have also tried that, if you see my ds file, there is a commented code

     

    <!-- <connection-url>jdbc:odbc:excel-odbc</connection-url> -->

    after re-start of the server and connecting through datasource explorer in eclipse. The tables name are the same like

     

    C:/abcd.Sheet1$

     

     

  • 8. Re: Teiid for Excel Data Source
    Ramesh Reddy Master

    Can you attach your excel file?

  • 9. Re: Teiid for Excel Data Source
    Ripan Karmakar Newbie

    excel file attached

  • 10. Re: Teiid for Excel Data Source
    Ripan Karmakar Newbie

    Hi Ramesh,

     

    Did you see the excel ? It is attached at starting as abcd.xls. The data are in the Sheet1.

    It will be helpful if you kindly let us know the way how to resolve this issue.

     

    Regards,

    Ripan K

  • 11. Re: Teiid for Excel Data Source
    Ramesh Reddy Master

    Ripan,

     

    Did you try Steve's suggestion from here?

     

    Unfortunately, my XP box crashed beyond recovery (just installed new firefox version) and we are in short supply of any Windoze systems right now. I am still looking.

     

    Ramesh..

  • 12. Re: Teiid for Excel Data Source
    Ramesh Reddy Master

    Ripan,

     

    Sorry for the delay in response, I finally got to test your Excel. I also got into similar issue as you are facing. Talking with Steve, I have logged the following TEIID-1895. Note that this only affects the Dynamic VDBs. Then I tried similar test with the VDB developed using the Designer. Here I created a source relational model, then added a Base table called location and added all the columns as you see in the Excel sheet. I also added the "NameInSource" property of the Location table as "[Sheet1$]", then built the VDB and deployed it. This works perfectly as needed. For your reference I am attaching the Designer VDB project, and -ds.xml file.

     

    Hope this helps.

     

    Ramesh..

  • 13. Re: Teiid for Excel Data Source
    Ripan Karmakar Newbie

    Hi Ramesh,

     

     

    Thanks a lot for your reply !

     

    Hope this will really helpful to us and solve the purpose.We are trying to deploy and test your project.

     

    It will be great if you kindly send us the VDB (xml) and a simple standalone client for above project as well. As we have followed this approach earlier.

     

    Thank you again for your help.

     

    Regards,

    Ripan K

  • 14. Re: Teiid for Excel Data Source
    Ripan Karmakar Newbie

    Hi Ramesh,

     

     

    Thanks a lot for your reply !

     

    Hope this will really helpful to us and solve the purpose.We are trying to deploy and test your project.

     

    It will be great if you kindly send us the VDB (xml) and a simple standalone client for above project as well. As we have followed this approach earlier.

     

    Thank you again for your help.

     

    Regards,

    Ripan K

1 2 Previous Next