i need a help to compare the Two excel sheets

[Anjaneyareddy]

hii vjr

i have Two Excel Spread sheets. 1.customerDetails.xls With the fields of( Name,Pan Number,aadhar,voter and mobile)
2.ValidationDetails.xls with the fields of(Aadhar,PAN AND VOTER)

bY USING BOTH THE FILES I WANT TO UPDATE STUTUS (LIKE) VALID OR NOT VALID FOR EACH OF THE VALIDATIONDETAILS.XLS FILE

below am attaching both the fiels

 

[VJR]

Hi Anjaneyareddy,

You can get the two worksheets into a collection and then perform a comparison. You can take a look at the below posts to see what is possible.
http://www.rpaforum.net/threads/how-to-get-unique-values-from-a-collection.757/
http://www.rpaforum.net/threads/how-to-remove-duplicates-from-a-excel-column.890/
http://www.rpaforum.net/threads/compare-two-excel-worksheets.878/
As you loop through the Validations collection you can keep updating the status (valid/invalid) column and when the looping is done you can overwrite this collection back to the validations file. If there are too many records then you can choose to use a Code stage as suggested in one of the above links.

 

[Anjaneyareddy]

Hii vjr,

These two excel files are having 2 Different colums. in customer details collection having 5 cloums and validation column only 3 columns.

can i get those 2 columns into one single collection or else i take 2 collections.

and how can i compare those validation.xls to customer.xls

could you please help me briefly.

Thanks
Anjaneyareddy

 

[VJR]

Hi Anjaneyareddy,

Yes I have seen your files that they have different number of columns which won't matter. While looping you can refer those columns with their correct names.
Get those 2 worksheets in 2 different collections.
Assuming you have Validations collection and CustomerDetails collection
- Loop through each item of the Validations collection
- Then add another inside loop to loop through the CustomerDetails collection
- Check here - If Validations.Adhaar = CustomerDetails.Adhaar and Validations.Pancard = CustomerDetails.Pancard
--If both conditions are true then update Validations.Status = VALID using a Calc stage
Once found no need to continue the inner loop so you can break the loop by directing your connectors to the Loop End stage of the inner loop.
--Even after the inside loop is complete but still it is not found then store INVALID
- After both the loops are finished your Validations collection will have all the updated Status column in which case you can then overwrite the entire collection back to the Validations excel file.
Again if you have too much of data then Code stage will help reduce the loop time.

 

[srinivas1]

Hi Anjaneyareddy,

Yes I have seen your files that they have different number of columns which won't matter. While looping you can refer those columns with their correct names.
Get those 2 worksheets in 2 different collections.
Assuming you have Validations collection and CustomerDetails collection
- Loop through each item of the Validations collection
- Then add another inside loop to loop through the CustomerDetails collection
- Check here - If Validations.Adhaar = CustomerDetails.Adhaar and Validations.Pancard = CustomerDetails.Pancard
--If both conditions are true then update Validations.Status = VALID using a Calc stage
Once found no need to continue the inner loop so you can break the loop by directing your connectors to the Loop End stage of the inner loop.
--Even after the inside loop is complete but still it is not found then store INVALID
- After both the loops are finished your Validations collection will have all the updated Status column in which case you can then overwrite the entire collection back to the Validations excel file.
Again if you have too much of data then Code stage will help reduce the loop time.

Hi VJR,

As you told I have done everything, but I am unable to write "Not-Valid" (If records not matches from both excel files).
How to write "Not-Valid"?

I am able to write "Valid" if "Adhhaar and Pancard" records matches from both excel files(CustomerDetails and Validations").

Please help......

Please find attached Screenshots

 

[VJR]

Hi srinivas1,

I did not take a look at the diagram but a general way of implementing such a logic is to use a Flag variable.
- At the beginning of the inner loop the Flag data item (lets say MatchFound) should be set to False
- Inside the inner loop as soon as a match is found (Valid) then set the MatchFound data item to True.
- Outside the inner loop but within the outer loop check the value of MatchFound data item.
If it is True then it means a match was found and you need not do anything since you already updated the status as "VALID".
If it is False then that means even after the entire loop was completed a match was not found so in this case set the collection column to INVALID.
When the flow goes back to fetch the next item from the outer loop then the MatchFound is reset to False as stated in point #i above.
Hope this makes it clear.

 

[Highspirits]

Hi Srinivas1,

for doing validation, I see you used calculation stage. You should be using a decision stage here. If the status is valid, then move "valid" to temp collection status. Else, move "invalid" to temp collection status.