Read data from Excel strings and manipulate them according to a specified condition

Please tell me, I’m a newbie in this business. If I need to find common elements between two Excel data tables (two files), and not between the created tables by the “Build DataTable” action. What operations need to be performed?Kniga.xaml (11.2 KB) Main.xaml (11.0 KB) File1.xlsx (8.9 KB) File2.xlsx (8.9 KB)
Read data from Excel strings and manipulate them according to a specified condition. I took 5 steps. Tell me how best to solve this problem.
Manual steps

  1. Create a “Task” folder on your desktop,
  2. copy the files File1.xlsx and File2.xlsx to this folder
    Remaining steps in UiPath
  3. Read the File1.xlsx file into a variable (from the Task folder)
  4. Read the File2.xlsx file into a variable (from the Task folder)
  5. Create a Result folder on your desktop
  6. Create a Result.xlsx file in the folder created in step 3.
  7. Transfer the information you read in step 1 to the Result.xlsx file
  8. Transfer the information read in step 2 to the Result.xlsx file, observing the following conditions:
    if there is a match of the material code (column A) and the cost (column C), then correct the quantity (column D) in the existing record (add),
    if no matches by code and cost are found, add a new line
  9. Add sheet (Sheet2) to Result.xlsx file
  10. Transfer information to the sheet added in step 6: material code, cost, quantity, total cost (cost * quantity)

Hi Alena,

isn’t it working? Not sure what is your problem. Granted I’ve not downloaded all your information. When dealing with the forum is better to cut your question into smaller pieces instead of explaining us the whole process…

Hi @Alena, do like this

  1. read the file1 and store in dt1

  2. read the file2 and store in dt2

  3. use the assign activity to do the following linq query to find common rows between the dt1 and dt2

    dt_common = dt1.AsEnumerable().Intersect(dt2.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

  4. then write the dt_common datatable to to an excel file by using write range

Please Note : The files you had provided has no common rows rows between them so i added my own and had tested it , Please check it for reference
Common_Rows_datatable.zip (52.4 KB)

Mark it as solution if you got it

Regards
Nived N
Happy Automation

I did the following

  1. read file1 into a variable
  2. read file2 into a variable
  3. created a new folder on the desktop
  4. Read data from DataTable file1 into a spreadsheet
  5. Added data from file2 data table to spreadsheet (file1 and file2 are now in the same table)
    It is necessary not only to find identical strings, but also to fulfill the condition. If the lines match, then in the first line change column D, if the lines do not match, then create a new empty line.
    How do I now make a condition in this new data table?

I don’t know how to do it right

Hi @Alena, did u tried my way i had explained

I applied your solution, it gives an error
Main.xaml: Compiler errors encountered while processing the expression “inputDT1.AsEnumerable (). Intersection (inputDT2.AsEnumerable (), System.Data.DataRowComparer.Default) .CopyToDataTable”.
“AsEnumerable” is not a member of “System.Data.DataTable”.
“AsEnumerable” is not a member of “System.Data.DataTable”.
“DataRowComparer” is not a member of “Data”.

hi @Alena, this is due to missing of some assembly line reference correct it
Check this link for reference

1 Like

The error with the AsEnumerable method is fixed, but there is a new error Assign: Source does not contain DataRows.

i think there is no common rows between the excel files @Alena

there are coincidences((