Page Comparison

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/motorcylesmotor cycles) that are manufactured in an automobile company. The data table has the following columns: "ID", "Model" and "Classification".

...

If query mode is disabled for the spreadsheet, you cannot use write SQL statements to query data in the excel sheet. Note that in non-query mode, you can only get data from the sheet and you cannot insert, update or modify any datathe 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 Type	Element Name	Datasource Type	Datasource Column Name	Parameter Type	Schema Type
      Element	Model	Column	Model	SCALAR	string
      Element	Classification	Column	Classification	SCALAR	string

      Info
      Find out more about defining Output Mappings.

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

If query mode is enabled

...

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.
   

   Code Block
   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.

   

   Info
   Find out more about defining Input Mappings.

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

Defining operations to invoke the query

...