Retrieve Data from Excel Spreadsheet & Insert Data into a Database using WSO2 Data Service Server

Assume a particular user has some data stored in the form of Excel  files and he needs to get those data exported into a MySQL database.WSO2 Data Service Server allows to expose the data stored in data sources such as relational databases, CSV files , MS-Excel files and Google spreadsheets as web services.

We can be using Nested query features provide by WSO2 Data Service Server when writing this data service. Nested query feature is possible for a particular data service query to feed the result obtained after the execution of that particular query, as an input to some other query and eventually integrate both results to an unified format before presenting it to the user.


Create a mySQL database with one table, EXCEL.

mysql> CREATE DATABASE EXCEL_SHEET;

mysql> CREATE TABLE EXCEL(ID int,Name varchar(100),Address varchar(200));

Now create the data service:
Add Excel spreadsheet as a data source

<data name="Excel">                 
   <config id="dss">                                   
      <property name="excel_datasource">/home/amal/Desktop/EmployeeList.xls</property>                 
   </config> 
</data>

Add EXCEL_SHEET database as a data source.

<config id="Excel_db">
      <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/EXCEL_SHEET</property>
      <property name="org.wso2.ws.dataservice.user">root</property>
      <property name="org.wso2.ws.dataservice.password">mysql123</property>
</config>


Create a query that retrieve the data from Excel spread sheet.In this query the column names of the output mappings should be same as the column titles in Excel spreadsheet.

<query id="getExcel" useConfig="excel_data">
      <excel>
         <workbookname>Sheet3</workbookname>
         <hasheader>true</hasheader>
         <startingrow>2</startingrow>
         <maxrowcount>-1</maxrowcount>
      </excel>
      <result element="Employees" rowName="Employee">
         <element name="ID" column="ID" xsdType="xs:integer" />
         <element name="Name" column="Name" xsdType="xs:string" />
         <element name="Address" column="Address" xsdType="xs:string" />
      </result>
</query>

Then create a operation that use the above query. Using that operation we can test whether query works properly.

<operation name="op1">
      <call-query href="getExcel" />
   </operation>

Click on the try it and check whether operation works properly.
It should return the all EXCEL details when you click on the operation.


Create a query "q2" that calls to  insert data into tables.

<query id="q2" useConfig="EXCEL_list">                                   
      <sql>insert into Employee_List values(?,?,?)</sql>                                   
      <param name="ID" sqlType="STRING" />                                   
      <param name="Name" sqlType="STRING" />                                   
      <param name="Address" sqlType="STRING" />                 
   </query> 


Then you need to give the data retrieved from the Excel spreadsheet as input to the "q2" query.To do this use Data Service nested query feature and call q2 query inside getExcel query.

      <call-query href="outExcel">
         <with-param name="ID" query-param="ID" />
         <with-param name="Name" query-param="Name" />
         <with-param name="Address" query-param="Address" />
      </call-query>


We are almost done with the data service configuration.Now, go to the TryIt screen of the data service and execute the operations as below.

Now when you execute the getExcel operation both data retrieving from Excel spreadsheet and inserting data to tables is happenning.
Check the two tables.Data should have entered.

mysql> select * EXCEL_SHEET;