Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

You can create data sources to expose data in Microsoft Excel 97-2007 as services using the WSO2 Data Services Server. For demonstration, we use an excel sheet containing three columns, namely, ID, Model and Classification. The sheet is then filled with mock data. Note that the column names should appear in separate cells. For example,

 

Excel datasources allow you to create a data service using an Excel spreadsheet as the data sources can be created in two different modes storage type.

You can get the Add New Datasource screen shown below on the management console by executing steps 1 to 3 in  creating a data service. Then, select EXCEL as the Datasource Type. It opens the Excel-specific options as shown below:

Image Added

The Use Query Mode option (in the UI above) allows you to create Excel data sources in two different ways as follows:

Also, see a demonstration of service enabling an Excel sheets in Excel Sample.   

Excel

...

data sources in

...

non-

...

query mode 
Anchor
nonQ
nonQ

Follow the steps in Creating Data Sources. When you select Excel as the data source type at the time To create a data source is created, the Excel-specific options appear. For example: 

Image Removed

4. After saving, the added data source page appears. You can edit or delete the Data Source. To proceed click "Next."

Image Removed

5. The "Queries" page appears.

Initially your service does not include any queries. Click on 'Add New Query' to add a new query, input/output mappings, events etc. to your Data Service.

Enter query details using which values will be extracted. For example,

Query ID : sheet1Query
Data Source : default (This is the data source created in previous step)

Grouped by element : Products
Row name : Product
Row namespace : http://ws.wso2.org/dataservice/sample

Click on "Add Output Mapping" button to 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. We have created ID as an element and "Name" and "Classification" as attributes.

Image Removed

For information on adding validations to your input mappings in the query, refer to section Input Validators . Also, detailed information on writing queries can be found in section Data Service Queries.

6. Once a new query is added, it will be listed as follows:

Image Removed

You can edit or delete your query. To continue, click "Next."

7. The "Operations" page appear s. Click the "Add new operation" link to c reate an operation. Select the query created above to be executed when this operation is called.

Click "Save" once done. For example,

Image Removed

8. The operation will be listed in the "operations" page. You can edit or delete your operation.

Image Removed

9. The Data Service configuration is done. Click "Finish" . ( You can also continue to add resources by clicking the "Next" button).

10. Once the "Finish" button is clicked, you will be navigated to the "Deployed Services" page where the newly added data service is listed .

Image Removed

From here, you can manage your data service. For instructions, refer to section Data Service Management.

For a demonstration of service-enabling an Excel sheets, refer to page Excel Sample . 

...

Internally, the class "For example,

Image Added

Excel data sources in query mode
Anchor
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,

Image Added

Internally, the class org.wso2.carbon.dataservices.sql.driver.TDriver" is used as the SQL Driver. It is a JDBC driver implementation to be used with tabular data models such as Google Spread Sheets, Excel sheets etc.

Follow the instructions below to create a data service using this excel sheet in query mode.

1. Log on to the product's management console and select   "Data Service -> Create" under the "Main" menu.

Image Removed

2. The "Create Data Service" page appears. Fill in the fields and click "Next." The Data Service name is mandatory.

Image Removed

Info

For more information on advanced configurations, refer to   Advanced Data Service Configurations.

3. The "Data Sources" page appears. Click on the " Add New Data Source" link and add your Data Source details. The data source type should be EXCEL and select the "Use Query Mode" option. For example,

Image Removed

4. After saving, the added data source page appears. You can edit or delete the Data Source. To proceed click "Next."

5. The "Queries" page appears. Initially your service does not include any queries. Click on "Add New Query" to add a new query, input/output mappings, events etc. to your Data Service.

Info
titleNote

Note that in the 'Query' mode, the UI assists in defining the query. This capability is not available in the 'Non-Query' mode.

Currently, the Query Mode At the moment, the query mode supports only basic SELECT, INSERT, UPDATE and DELETE queries. Nested queries are not supported at the moment but will be facilitated supported in an upcoming release. For more information, refer to Given below are few examples ofthe supported queries.

6. Enter the query and click "Generate Input Mappings" to auto generate the input mapping parameters. For example,

Image Removed

7. Once a new query is added, click "Next".

8. The "Operations" page appear s. Add the operation details. In the following example, operation name is "insertProducts". Select the created query from the drop down list and click on "Add Query Params as Operation Params" to add the parameters to operation. Click "Save" once done.

Image Removed

9. The operation will be listed in the "operations" page. You can edit or delete your operation.

10. The Data Service configuration is done. Click "Finish" . You will be navigated to the "Deployed Services" page where the newly added data service is listed .

From there, you can manage your data service. For instructions, refer to section Data Service Management.

For a demonstration of service-enabling an Excel sheets, refer to page Excel Sample .

...

Currently, only basic SELECT, INSERT, UPDATE and DELETE queries are supported by the 'Query' mode. Shown below are few example queries.:

Queries supported in the query mode

Sample 1:

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

...

Code Block
DROP SHEET ProductCategories

...

hiddentrue

...

To write a query to this data source, see Writing Data Service Queries.

Also, see a demonstration of service enabling an Excel sheet in Excel Sample.