Due to a known issue do not use JDK1.8.0_151 with WSO2 products. Use JDK 1.8.0_144 until JDK 1.8.0_162-ea is released.
This documentation is for WSO2 Data Services Server version 3.5.1. For the latest documentation, see the documentation for WSO2 Enterprise Integrator.
Skip to end of metadata
Go to start of metadata

This tutorial will guide you on how to expose data in an Excel sheet as a data service by using the Create New Data Service wizard. We will create a data service that can search for data on the file and insert data into the file.

To demonstrate this feature, we will use the Products.xls file that is shipped with WSO2 DSS by default. The Products.xls file is stored in the <DSS_HOME>/samples/resources/ folder and it contains data about products (cars/motorcyles) that are manufactured in an automobile company. The data table has the following columns: "ID", "Model" and "Classification".

See the following topics for instructions:

Start the Create New Data Service wizard

Now, let's start creating the data service from scratch:

Add an Excel file as the datasource

You can add an Excel file as the datasource as explained below.

  1. Click Add New Datasource to open the following screen.
  2. Follow the instructions below to fill the datasource details.
    1. In the Datasource Id field, enter Excel as the value.

    2. In the Datasource Type field, specify the type of datasource for which the data service is created. Select Excel from the list. You will now get the following screen:

    3. In the Excel URL field, specify the path to your Excel file. In this tutorial, we are using a sample excel file that is stored in the following location of your product pack: ./samples/resources/Products.xls.

    4. The Use Query Mode option allows you to write queries for the datasource in two different ways.

      • Non-Query mode: Allows you to directly expose the contents of the excel file as a service.

      • Query mode: Allows you to query the Excel file in SQL-like manner, and expose the results as a service.

      See the next section on writing queries for more information on how the query mode affects how you query data in the excel sheet.

  3. Save the datasource.
  4. Click Next to go to the Queries screen.

Define a query for the datasource

You can define queries in two ways for an excel datasource, depending on whether or not Query mode is enabled for the datasource.

If query mode is disabled

If query mode is disabled for the spreadsheet, you cannot write SQL statements to query the spreadsheet. Instead, you need to specify the query details by filling in the data extraction parameters. This also means that, when query mode is disabled, you cannot use the data service to insert, update, or modify data in the spreadsheet. This data service can only be used to get data that is already stored in the spreadsheet.

Follow the steps given below. 

  1. Click Add New Query to open the Add New Query screen.
  2. Enter Q1 as the query id in the Query ID field.
  3. In the Datasource field, select the datasource for which you are going to write a query. Select the Excel datasource that you created previously.

  4. You can directly specify the details of the spreadsheet as shown below.
  5. Define Output Mapping: Now, let's specify how the data fetched from the datasource should be displayed in the output. The sample Excel datasource we are using contains three columns: ID, Model and Classification. We will create an output mapping for each of these columns. 
    1. In the Output type field, specify the format in which the query results should be presented. You can select XML, JSON or RDF. We will use XML for this tutorial.
    2. In the Grouped by element field, specify a grouping for all the output mappings. This will be the XML element that will group the query result. Enter Products in this field. 
    3. In the Row Name field, specify the XML element that should group each individual result.  Enter Product in this field.
    4. Click Add New Output Mapping to start creating the output mapping for the ID column. Enter values as shown below:
    5. Click Add to save the output mapping.
    6. Create output mappings for the remaining columns given below.

      Mapping TypeElement NameDatasource TypeDatasource Column NameParameter TypeSchema Type

      Find out more about defining Output Mappings in WSO2 DSS.

  6. Click Main Configuration → Save → Next, to go to the Operations screen.

If query mode is enabled

You can query data in the spreadsheet using SQL statements. At the moment, the query mode supports only basic SELECT, INSERT, UPDATE and DELETE queries. Nested queries will be supported in an upcoming release. The org.wso2.carbon.dataservices.sql.driver.TDriver class is used internally as the SQL Driver. It is a JDBC driver implementation used with tabular data models such as Google spreadsheets, Excel sheets etc.  

Follow the steps given below.

  1. Click Add New Query to open the Add New Query screen.
  2. Enter Q1 as the query id in the Query ID field.
  3. In the Datasource field, select the datasource for which you are going to write a query. Select the Excel datasource that you created previously.

  4. Specify an SQL query to insert data into the spreadsheet.

    INSERT INTO sheet1 (ID, Model, Classification) VALUES(?,?,?)
  5. Define Input Mapping: You need to create input mappings for the ID, Model and Classification columns specified in the above SQL statement. These input mapping parameters will be used for inserting data into the relevant columns. Click Generate Input Mapping, and default input mappings will be generated for all the fields that you have specified in your SQL statement as shown below.

    Find out more about defining Input Mappings in WSO2 DSS.

  6. Click Main Configuration → Save → Next, to go to the Operations screen.

Defining operations to invoke the query

Follow the steps given below.

  1.  Click Add New Operation to open the following screen:
  2. In the Operation Name field, enter a name for your operation
  3. In the Query ID field, select the query you created previously.
  4. Save the operation.

Finish creating the data service

Once you have defined the operation, click Finish to complete the data service creation process. You will now be taken to the Deployed Services screen, which shows all the data services deployed on the server.

Invoking your data service

You can try the data service you created by using the TryIt tool that is in your product by default.

  1. Go to the Deployed Services screen.
  2. Click the Try this service link for the Excel data service. The TryIt Tool will open with the Excel data service.
  3. Select the operation your created earlier and click Send.
    • If you are invoking a query that can insert data into the excel sheet, you will need to specify the values that should be inserted before clicking Send. 
    • If you are invoking a query that can fetch data from the excel sheet, the relevant data will be published when you click Send.
  • No labels