Hi all, I’m trying to retrieve an email from an excel, but I would need to match the Name and Company first. Here are my steps of the workflow:
Output Data Table of Excel A and Excel B
Excel A has Name, Company, Date // Excel B has Name, Company, Email
I have managed to filter Excel A date and get the Name and Company identified from that specific date.
*4. Now I’m confused at this step where I have to get the Email from Excel B, but it has to match both Name and Company of Excel A
I have added some sample examples in case it gets too confusing,
For example I have to extract Name and Company with “01/07/2022” as date from excel A.
and match that Name and company in excel B and get their email stated in excel B.
What are the steps I should take to implement this? Thanks in advance.
You can use Join function for this. UiPath has a join activity you can refer that or a LINQ query can be written for joining the two datatables and storing the result you want into a third one.
Appreciate the prompt response and beneficial help. There are still some obstacles, but I believe we’re on the right track.
I’ve implemented the join activity (left join), and the sample result is as shown below.
The current issue now is, based on the blue table, I am supposed to get Irene’s email.
However, in the orange table, I have 2 contact emails in the company (Irenes Bakery Shop):
Irene
Karina
I am only supposed to get Irene’s email, how do I implement this step to retrieve the email of the specified Name in Column ‘Name_1’ , based on the Company Name?
Note that the actual data we have people with same names, so we need to additionally cross check the ‘Company’ together with the ‘Name’.
The input data table should be the one containing your email addresses. The results would be stored in the output data table. Replace the hardcoded name and company with your own variables.
You could add handling for != 1 match, but if the filtered results do contain 1 row, then there was a match and you could access the email value like so. dtFilteredResults.Rows(0).Item("Email").ToString
This article also shows some other ways of filtering.
Take one for each row pass the first DT_1
Take one more For each roe pass the second DT_2
Take one IF Condition mention the Condition like this
CurrentRow1("Name").ToString.Trim.Equals(CurrentRow2("Name").ToString.Trim) AND CurrentRow1("Company").ToString.Trim.Equals(CurrentRow2("Company").ToString.Trim)
Then:
Take one Assign Activity->Create one String Variable
However, in the actual DT, I have thousands of rows in DT2 (which contains the email) as it is like a contact list. The bot is crawling through each data searching for the correct “Name” and “Company” to pull the email and that is taking hours to process.
Essentially what I’m trying to do in this whole process is to retrieve the “Email” of the persons “Name” at a “Company” at a specific “Date”.
I have managed to filter the specific date I want and output the result into another filteredDT_1, so it only shows Companies and Names which has the specified date tagged to them. This is what it looks like when I filter to date “14-11-22”
DT1
filteredDT_1
I was wondering if I can use the “Company” listed in filteredDT_1 's and straightaway locate the “Company” in DT_2 and pull the “Email” out? Rather than crawling through all the data to match.