Tuesday, June 11, 2013

Expose Excel Data as OData feed using Teiid

For that matter you can expose any data source that is supported by Teiid through its translators as OData service. You can convert
  • Relational databases
  • XML, JSON, CSV files
  • Web Services 
  • Sales Force
  • Excel documents
sources, or write your on top of your specific source.

Teiid added support for exposing its virtual database (VDB) as OData service without any additional work. Create a virtual database with your required sources and create any additional views and deploy in the the JBoss EAP 6.1 server, that has Teiid 8.3 or greater installed.  See https://docs.jboss.org/author/display/teiid84final/OData+Support for more information.

So, lets build a simple example of exposing the Excel Sheet. Teiid accesses Excel sheets via JDBC-ODBC bridge, and Windows OS provides a ODBC driver for Excel documents. 

Edit the standalone-teiid.xml, and create Connection Factory
 

    
        jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};Dbq=c:\ODBC\ExcelData.xls
        odbc
        TRANSACTION_NONE
        
            false
            false
            FailingConnectionOnly
        
        
            {user}
            {password}
        
         
    
        
         sun.jdbc.odbc.JdbcOdbcDriver
     
     



Now create VDB, using the excel as the source
 


    
    
        
    



save the above as "odbc-vdb.xml" and deploy into Teiid Server. See https://docs.jboss.org/author/display/TEIID/Deploying+VDBs for deployment instructions.  Make sure your VDB deployed in "ACTIVE" state.

Now, you can access your Excel data using OData API. Either using the your Web Browser or favorite OData client, go to 

 
     http://:/odata/odbc/excel.Sheet1
For more complete information about OData API see https://docs.jboss.org/author/display/TEIID/OData+Support

Thanks
Ramesh..

2 comments:

  1. I tried to do this with a test Excel file but when I started ran standalone.sh, I got this error:
    Caused by: org.xml.sax.SAXParseException: cvc-complex-type.2.1: Element 'property' must have no character or element information item [children], because the type's content type is empty.

    java version "1.6.0_37"
    JBoss EAP 6.1
    Teiid Server 8.4

    ReplyDelete
  2. Try querying your Excel based vdb using any SQL client such as SquirreL and make sure it is returning results before you use the OData interface, that should give you correct error.

    ReplyDelete