How can i compare two excel files and check if specific value is modified in the second one and then take it

Learn
#1

I have two excel files. I need to iterate through the first one but during iteration i need to check if the value of the column “Company” contains in the second mapping excel file (and it might be modified there). If that’s the case i need to take the value from the second mapping excel file. This is happening because there are errors in the first excel file and that’s why i have mapping document.
eg. First Excel file:

image
image1920×1080 225 KB

Second excel file:

image
image1913×886 90.6 KB

As you can see there is difference in the company. In the first file i have “Interworks BT” and in the second file only “Interworks”. So in this case i need to take the value from the second filefor processing. That is the correct value. Any solution for this?

#2

@aleksandra_sekulovsk Will the two Excel Files Have only so many columns as in the Screenshot or is there a mapping between First and the Second File other than the Company Column?

2 Likes
#3

The Company from the first file is the same as the Company from the mapping doc. For company i need to take the value from the mapping doc.

#4

So there is a mapping only for the company not for the other fields.

#5

@aleksandra_sekulovsk Can you give some more examples so that we can be sure what logic would be proper to use? And what is the Output that you expect , or is it like you want to Substitute the name from The First file with the Second File?

Also the example that you have provided as Value “Interworks BT” in Company Column and in other file you have the Value “Interworks”, but is there a Situation where the Mapping Values are Completely different like in the First File there is a Value “My Company” and in the Other File the value is “UiPath”. And “My Company” is the Actual mapping of “UiPath”.

I hope you can understand the above Scenario :sweat_smile:

1 Like
#6

In practice I need to go through each row in the first file and once i’m on the Company cell i need to take that value and check if the second file (mapping doc) contains the same Company. If it contains i need to take that value and type that value into a web app. If it’s not contained i should proceed with he first file values. I hope it’s clear now… :slight_smile:
This is caused because in the first file some of the values for Company may have additional characters and the web application doesnt accept it. That’s why i need to use the company values from the second mapping doc. In that doc i have the correct values for Company.

#7

There will be no big difference in the Company names. It will be just few characters in the name like “Interworks (BT)”, “Interworks 1”, “Interworks BT”.

#8

@aleksandra_sekulovsk Is it possible to send sample input files with more than one values, to make sure the logic works fine for all cases… We basically have to use Contains Operation if you are trying to loop and find the Correct Company value.

#9

Of course:
Interworks - (“Intervorks (BT)”)
Audit Rights - (“Audit Rights (CAM)”)
Landlord Assurances - (“LL Assurances”)
Tenant Right to Terminate - (“Tenant Termination Rights”)
Exclusive Use - (“Exclusive Uses -”)
Go Dark - (“Go Dark (cont. operations)”)
The first values are the correct ones from the mapping docs, and the values in the brackets are the incorrect ones from the excel file,

#10

Mapping.xlsx (8.7 KB) Excel1.xlsx (9.6 KB)

1 Like
#11

@aleksandra_sekulovsk Check this Workflow, It uses an Additional Package which you need to download UiPathTeam.String.Activities . Make Sure it Works for all the Cases before you use it as a Project.

Excel Mapping.zip (16.0 KB)

1 Like
#12

Thank you @supermanPunch. Your soluition works :slight_smile:

1 Like
#13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.