T96 - Automate data comparison between applications like Excel |
Task Description |
Compare range of data in two excel files and log the results of comparison in a text file.
 T96.atmn  Excel1.xls Excel2.xls
This sample task compares the range of data in Excel1.xls and Excel2.xls and saves the similar records or data in a text file. |
| Solutions |
 |
Create new task
- Click on New -> Task Editor.
- Click on ‘Variable Manager’ tab located on right side panel of Task Editor.
- Create three variables of type Value. XlCol1, XlCol2 and XlCol3.
|
Screenshot 1
Screenshot 2
Screenshot 3
Screenshot 4
Screenshot 5
Screenshot 6
Screenshot 7 |
 |
Open Excel files
- Double click on ‘Excel’ command and select ‘Open Spreadsheet’ option to open the first excel file with session name Excel1.
- Similarly, open second Excel file with session name, Excel2.
|
 |
Capture the range of data to compare
- Get the range of data to compare from first excel file i.e. from Excel1 session using Get Multiple Cells, Excel command. The range are stored in the system variable $Excel Column$.
- Using ‘Variable Operation’ command get the data in $Excel Column(1)$, $Excel Column(2)$ and $Excel Column(3)$ into user variable, $XlCol1$, $XlCol2$ and $XlCol3$ respectively for later comparison.
|
 |
Loop through the range of data
- Insert ‘Loop’ command option, ‘For each row in Excel Dataset’ to loop through the data of session, Excel1.
- Inside this loop block, capture range of data to compare with from second excel file i.e.Excel2 session using Get Multiple Cells, Excel command.
|
 |
Compare the range of data
- Insert another ‘Loop for each row in Excel Dataset’ command to Loop through all the data of Excel2 session.
- Inside second loop block, use ‘If Variable’ commands to compare the data of both the sessions. Compare $Excel Column(1)$ with $XlCol1$, $Excel Column(2)$ with $XlCol2$ and $Excel Column(3)$ with $XlCol3$.
|
 |
Log the data
- If the result of above comparison is positive i.e. if all the three columns in both files are same, then inside If-End if block, log the data to a text file (ListofSimilarRecords.txt) using ‘Log to File’ command.
|
 |
How to run the task
- In case you do not have Automation Anywhere installed, please download and install it from, http://www.automationanywhere.com/download/freeTrial.htm
- Save T96.atmn under location, My Documents\Automation Anywhere\Automation Anywhere\My Tasks
- Save both .xls files under C:\.
- Launch Automation Anywhere and from Task List select T96.atmn and click on RUN button to run the task.
|
| |
|
|
 |
| We started using this product to copy data from legacy system to new system. Now we use it just to do about anything
automated. Countless hours saved. Thank you guys. |
- Jack Greenberg
|
| Read Customer Testimonies >> |
|
