...
To create a data source with Excel, first follow the steps in Creating Data Sources. When you select EXCEL as the data source type at the time a data source is created, the Excel-specific options appear. For example:
The Use Query Mode option (in the UI above) allows you to create Excel data sources in two different ways as follows:
- Excel Data Sources in Non-Query Mode : Allows to directly expose the content of an Excel sheet as a service
- Excel Data Sources in Query Mode : Allows to query an Excel sheet in a familiar, SQL-like manner, and expose the results set as a service
Also, see a demonstration of service enabling an Excel sheets in Excel Sample.
Excel Data Sources in Non-Query Mode
| Anchor | ||||
|---|---|---|---|---|
|
After creating the To create a data source in non-query mode, simply uncheck the Use Query Mode check box and save. Then, click Next to add a query. Note that the Query Details UI does not provide support to write a query. You can simply add Output Mappings to define how the output looks like. For example,
...
Excel Data Sources in Query Mode Anchor Q Q
| Q | |
| Q |
...
To create a data source in query mode, check the Use Query Mode check box and save. Then, click Next to add a query. Note that the Query Details UI now provides support to write a query. For example,
Internally, the class org.wso2.carbon.dataservices.sql.driver.TDriver is used as the SQL Driver. It is a JDBC driver implementation used with tabular data models such as Google Spread Sheets, Excel sheets etc.
At the moment, the query mode supports only basic SELECT, INSERT, UPDATE and DELETE queries. Nested queries will be supported in an upcoming release. Given below are the few examples ofthe supported queries with examples:
Queries Queries supported in the query mode
...
| Code Block |
|---|
DROP SHEET ProductCategories |
Also, see a demonstration of service enabling an Excel sheets in Excel Sample.