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