Task 1 : Compare Data in 2 Excel Sheets

Reference Task

Task ID      TK 0000014 � v0.55
Release      //ReferenceTasks/Generic Tasks
Task Title      Compare Data in 2 Excel Sheets

Description

This task demonstrates comparison of data in 2 excel sheets.

Pre configured

This example uses data in 2 excel sheets for comparison and Data table node to iterate on each record in the excel sheet.

Table Definition � Define a custom Datatable, add the column names and select the datatype of each column.

Task DESIGN

Design steps

Excel node 1 �
1. EXCEL COMMAND to open Excel and provide Excel file path. 2. EXCEL COMMAND to select sheet by name and provide sheet_name as input. 3. EXCEL COMMAND to copy to table and provide table_name and range_value as input. Store in the variable of type table. 4. EXCEL COMMAND to Save file. 5. EXCEL COMMAND to close file and provide the Excel file path. 6. EXCEL COMMAND to close excel. Mapping to Datatable � Map the variable in which table is stored.

Excel node 2 �
1. EXCEL COMMAND to open Excel and provide Excel file path 2. EXCEL COMMAND to select sheet by name and provide sheet_name as input. 3. EXCEL COMMAND to Get last row with data and store the output in a variable count. 4. Select Set variable from the drop down and Set the variable i with value 2. 5. Add a while loop to fetch the cell value of each column and compare it with Excel sheeet1�s values. 6. Step 6, 7, 8 and 9 is for finding the cell number of a column. Select Default expression from drop down and use Concat expression. Provide column name as input to string 1 and i value as input to string 2. Store the Values in output variables �Col_A�, �Col_B�, �Col_C� and �Col_D�. 10. Step 10,11 and 12 is to get the cell value using cell numbers calculated. EXCEL COMMAND to Get cell value, provide the cell name as input. Store the output in corresponding variables � �sys_item�, �sys_qty�, �sys_amt�. 13. Step 13, 14 and 15 are if conditions to compare the values. 16. EXCEL COMMAND to update cells. Provide input update_value and update_cell. If the values are matched then update the cell in column D as Matched. 17. . EXCEL COMMAND to Get cell value of coulumn D into variable �Status�. 18. If condition to check the status if it is not matched. 19. EXCEL COMMAND to update cells. Provide input update_value and update_cell. If the values are not matched then update the cell in column D as Not matched. 20. Select Default expression from drop down and use Add expression to increase the I count for looping. 21. EXCEL COMMAND to Save file. 22. EXCEL COMMAND to close file and provide the Excel file path. 23. EXCEL COMMAND to close excel.