[Download] | [Documentation Home] | [Release Note]

JDBC driver for Excel and Google Spread Sheets

Introduction

This functionality allows users to query an Excel sheet or a Google SpreadSheet in a much familiar SQL-like manner.

The users can simply log in to the Data Service Server and create a datasource using the "Generic" option that comes under the RDBMS datasource type and make use of that configuration when you define the dataservice queries


JDBC URL format

Excel

Eg:

jdbc:wso2:excel:filePath=/home/djpro/IdeaProjects/TestParser/workbooks/workbook.xls

(Note that "filePath" attribute is mandatory for the Excel JDBC URL)


Google Spreadsheets

Eg:

jdbc:wso2:gspread:filePath=https://docs.google.com/a/wso2.com/spreadsheet/ccc?key=0AkK1ndEwj4-WdGtSTFVxMWJhVzdNRWF6NUhxT01zTXc;sheetName=testDoc;visibility=private

(Note that "filePath", "sheetName" and "visibility" attributes are mandatory for the Google Spreadsheet JDBC URL)


How to configure a datasource corresponding to the Excel/Google SpreadSheet that needs to be queried

The following images depict the datasource configurations for Excel and Google Spread Sheets

Excel

Configure Excel datasource via UI

Configure Excel datasource via the dbs

Google Spreadsheets

Configure Google Spreadsheet datasource via UI

Configure Google Spreadsheet datasource via the dbs

Supported query types

Currently, this feature provides support for basic SELECT, INSERT, UPDATE and DELETE functionalities.

Note : Nested quries are currently not supported


Eg:

SELECT customerNumber,customerName,phone,state,country FROM customers

Sample SELECT query

INSERT INTO customers(customerNumber,customerName,contactLastName) VALUES(?,?,?)

Sample INSERT query

UPDATE customers SET contactFirstName=?, contactLastName=? WHERE customerNumber=?

Sample UPDATE query

DELETE FROM customers WHERE customerNumber=?

Sample DELETE query