Exposing Excel Data as a Data Service

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 EI by default. The Products.xls file is stored in the <EI_HOME>/samples/data-services/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. Also, see the samples in Data Integration Samples.

Creating the data service

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

1. Log in to the management console using the following URL on your browser:  https://localhost:9443/carbon/.
2. Click Create under Data Service to open the Create Data Service window.

3. Enter the following data service name.

   Data Service Name	Excel

4. Leave the default values for the other fields
5. Click Next to go to the Datasources screen.

Connecting to the datasource

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

1. Click Add New Datasource and enter the following details:

   Datasource ID	Excel
   Datasource Type	EXCEL
   Excel URL	In this tutorial, we are using a sample excel file that is stored in the following location of your product pack: ./samples/data-services/resources/Products.xls.
   Use Query Mode	The Use Query Mode option allows you to write queries for the datasource in two different ways.  If query mode is enabled, you can write SQL queries for the excel sheet. If query mode is disabled, you can get data from the excel datasource without an SQL query.  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. See the next section on writing queries for more information on how the query mode affects how you query data in the excel sheet.

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

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

Creating a query to GET data

• If Query Mode is enabled for the datasource, follow the steps given below.

  1. Click Add New Query and add the following details.

     Query ID	GetProductbyID
     Datasource	EXCEL
     SQL	select ID, Model, Classification from Sheet1 where ID=:ID

  2. 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.
  3. Define Output Mapping: Click Generate Response to create the output mapping. This defines how the employee details retrieved from the datasource will be presented in the result. Note that, by default, the output type is XML. 
  4. Save the query.

• If Query Mode is disabled for the datasource, follow the steps given below.

  1. Click Add New Query and add the following details:

     Query ID	GetProducts
     Datasource Type	EXCEL
     Workbook Name	Sheet 1
     Start reading from	2
     Rows to read	5
     Headers available	true
     Header row	1

  2. 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.  

  3. Start by giving the following information:

     Output type	You can select XML, JSON or RDF. We will use XMLfor this tutorial. This specifies the format in which the query results should be presented.
     Grouped by element	Enter Products. This will be the XML element that will group the query result.
     Row Name	Enter Product. This is the XML element that should group each individual result.

  4. ClickAdd New Output Mappingto start creating the output mapping. Listed below are the output mappings that should be created.

     Mapping Type	Element Name	Datasource Type	Datasource Column Name	Parameter Type	Schema Type
     Element	ID	Column	ID	SCALAR	string
     Element	Model	Column	Model	SCALAR	string
     Element	Classification	Column	Classification	SCALAR	string

  5. Once you have created all of the above mappings, click Main Configuration to return to the Query screen.
  6. Save the query.

Creating a query to POST data

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 and add the following details.

   Query ID	AddProducts
   Datasource	EXCEL
   SQL	INSERT INTO sheet1 (ID, Model, Classification) VALUES(:ID, :Model, :Classification)

2. 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.

3. Save the query.

Create a SOAP operation to invoke the queries

To invoke the query, you need to define an operation.

1. Click Add New Operation and enter the following information.
   

   Operation Name	GetProductsbyIDOp
   Query ID	GetProductsbyID

2. Save the operation.

3. Click Add New Operation and enter the following information.
   

   Operation Name	AddProductsOp
   Query ID	AddProducts

4. Save the operation.
   

You can now invoke the data service query using SOAP.

Creating a REST resource to invoke the query

Now, let's create REST resources to invoke the query created above. Alternatively, you can create SOAP operations to invoke the queries. See the previous section for instructions.

1. Click Add New Resource and enter the following information.
   

   Resource Path	Products/{ID}
   Resource Method	GET
   Query ID	GetProductsbyID

2. Save the resource.

3. Click Add New Resource and enter the following information.
   

   Resource Path	Products
   Resource Method	POST
   Query ID	AddProducts

4. Save the resource.
   

You can now invoke the data service query using REST.

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 using SOAP

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.

Invoking your data service using REST

You can send an HTTP GET request to invoke the data service using cURL as shown below.

curl -X GET http://localhost:8280/services/Excel.HTTPEndpoint/Products/S10_4757

This will return the response in XML.