Exposing Excel Data as a Data Service

This tutorial guides you on how to expose the data in an Excel sheet as a data service. 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. The Products.xls file is stored in the <EI_HOME>/samples/data-services/resources/ folder and it contains data about products ( cars/motorcycles) that are manufactured in an automobile company. The data table has the following columns: ID, Model, and Classification.

Creating the data service

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

1. Download the product installer from here, and run the installer.
   Let's call the installation location of your product the <EI_HOME> directory.

   If you installed the product using the installer, this is located in a place specific to your OS as shown below:

   OS	Home directory
   Mac OS	/Library/WSO2/EnterpriseIntegrator/6.5.0
   Windows	C:\Program Files\WSO2\EnterpriseIntegrator\6.5.0\
   Ubuntu	/usr/lib/wso2/EnterpriseIntegrator/6.5.0
   CentOS	/usr/lib64/EnterpriseIntegrator/6.5.0

2. Start the ESB profile:
3. Access the ESB profile's management console using the following URL: https://localhost:9443/carbon/.
4. Sign in using admin as the username and password.
5. Click Create under Data Service to open the Create Data Service  window.

6. Enter the following data service name.

   Data Service Name	Excel

7. Leave the default values for the other fields.
8. Click  Next  to go to the  Datasources  screen.

Connecting to the datasource

Follow the steps given below to add an Excel file as the datasource.

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

   Datasource ID	Excel
   Datasource Type	EXCEL
   Excel URL	Enter ./samples/data-services/resources/Products.xls. In this tutorial, we are using a sample excel file that is stored in the above location of your product pack.
   Use Query Mode	Select Use Query Mode to enable it or keep it deselected to disable query mode. You can try out this tutorial using either option. 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.  See the next section on defining 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.

Define a query for the datasource

Create a SOAP operation to invoke the queries

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

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 or instructions.

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. Run the operation. 

Invoking your data service using REST

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

What's next?

Try out the samples under Data Integration Samples.