Hi, how to get name based on code

i have created a data table contain the company code and company name as show.
image
also i have a robot gets an excel file from email. this file have data one of this data is the company code how do i make the robot when open the file and see the code get the correct company name. also is this the correct way to do it or not. all i need is when the robot read the cell with the company code get thr correct name from the data table that match the number and store it into a variable
image

1 Like

Hi @mohamed.saty2012

Try this:

Output = (From r1 In dtDataTable1.AsEnumerable()
                            Join r2 In dtDataTable2.AsEnumerable()
                            On r1("Company").ToString() Equals r2("Company").ToString()
                            Select r1.Field(Of String)("respo")).FirstOrDefault()

Output is of DataType System.String.

Regards

1 Like

@mohamed.saty2012
Please do the following steps,

  1. Loop through the Datatable which contains company name
  2. Loop through the excel file
  3. In if condition check Company = Company code
    Then
    Assign: Variable= Datatable Currentrow(“Respo(Column Name of Company)”).tostring
  4. Use the variable to process further
2 Likes

sorry can you explain more as i am new to UiPath

1 Like

Sure @mohamed.saty2012
Please follow the below steps,

  1. Build data table with company code and company name - The output datatable name let’s say “CompanyNameDT”
  2. Using read range workbook, read the excel file and save it in “CompanyDetailsDT”
  3. Drag and drop for each row in datable activity and pass “CompanyNameDT”.
  4. Inside for each row in datatable drag and drop another for each row datatable activity. It should look like below
  5. Drag and drop if condition inside the second for loop and check the condition(Adjust the variable name according to your need)

    Assign the company name from the CompanyNameDT to the variable Name. you can further use the variable “Name” for your processing.

Hope this helps!!!
Happy Automation :smiley:

2 Likes

sorry ifws not clear enough. need to clarify something. i am only wanting to get the company code only for the matching part as i will take the company name to use it to open oracle explosibility. so no need to loop for the excel sheet i need only the robot to get the company name according to the code in the excel sheet. so in more details the boot will read the cell value and based on the code the robot find should get the correct name from the data table and store it in a variable that i can used in another activity. sorry for the inconvenient.

1 Like

Hi @mohamed.saty2012

Try this in Assign activity. Since you have both in datatables you can use the syntax in assign activity

Regards

2 Likes

i am sorry i feel like i didn’t explain well. i will make it very simple.
if i have an excel file called file1 contain only one cell with the number 109 and I have another file
called file2 have two column one named “code” and the other named “company names”. the number 109 in file number one have a corresponding name in the file number two. i need to take the number from the first file which is 109 and get the corresponding company name from the file2 and store it into variable that is all.
File2.xlsx (8.4 KB)

File1.xlsx (8.4 KB)

pleas could anyone create this automation.

1 Like

Hi @mohamed.saty2012
=> Read Range Workbook File1.xlsx
image
Output-> dtDataTable1

=> Read Range Workbook File2.xlsx
image
Output-> dtDataTable2

=> Use below syntax in Assign:

CompanyName = (From r1 In dtDataTable1.AsEnumerable()
                            Join r2 In dtDataTable2.AsEnumerable()
                            On r1("Code").ToString() Equals r2("Code").ToString()
                            Select r2.Field(Of String)("Company Name")).FirstOrDefault()

CompanyName is of DataType System.String

=> You can use the CompanyName variable in your further process as required
Output:


Sequence10.xaml (9.4 KB)

Regards

1 Like

@mohamed.saty2012 for getting the company name only we are looping through the datatable. If you dont want to loop through the DT you can use assign activity as mentioned by @vrdabberu .

2 Likes

Thank you all for your time and magnificent support.

1 Like

the solution work fine, but still need one modification can you help me.

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