I need urgent support. to map between to excel files

i have to excel files one contains tow column company name and the company id. the end user will send the excel file that contain the company ID so i need the bot to get the ID from the end user file and get the company name from the other excel file. Thies are the files
Company Codes.xlsx (9.5 KB)
and this is the end user file
Accruals JV_2024_06_12_12_39_45.xlsx (18.3 KB)
the column that contains
the company id is called “Company” as shown in the excel file.
can anyone support me with this

Hi @mohamed.saty2012

Your query is quite confusing, be more elaborate.

If possible show us expected data.

you have two file that i have attached the first file is the file contain the company name which is the value i want at the end the end. the file has two column “Code” and “company name” and the file that will be sent from the end-user have only the code in column “company”. so i need the robot to get the code from the end-user file and match it with the same code at the other file to get the company name. if you open the two files you will understand. for example if the end-user sent a file with a number in column “company” is “109” this number equal “ICAPP” in the other file so i need the robot to get this name in a variable that i can user for later

Hi @mohamed.saty2012 ,

Step-by-Step UiPath Workflow

  1. Read the Excel Files
  • Use Excel Application Scope to open the end-user Excel file.
  • Use Read Range activity to read the data into a DataTable variable (e.g., dtEndUser).
  • Repeat the above steps for the company codes Excel file and store it in another DataTable variable (e.g., dtCompanyCodes).
  1. Merge Data Tables
  • Use a Join Data Tables activity to merge the two DataTables.
  • Set the Input DataTable1 to dtEndUser.
  • Set the Input DataTable2 to dtCompanyCodes.
  • Set the JoinType to Inner.
  • Set the Column1 to the column containing the company code in dtEndUser.
  • Set the Column2 to the column containing the company code in dtCompanyCodes.
  • Set the Output DataTable to a new DataTable variable (e.g., dtMerged).
  1. Extract Company Names
  • Use a For Each Row activity to loop through each row in dtMerged.
  • Inside the loop, use an Assign activity to extract the company name and store it in a variable.

Regards
Sandy

Thanks Brother you saved me

1 Like

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