How to compare the data and append the extra data

Help Automation Ops
,
1

There are two excel files. In a file, there are some codes and in another file there are updated codes .So we have to compare the 2 excel files data and if there are extra codes in second excel file, then it should append in first excel file.

2

Hi @anjani_priya

Can you Provide the input and output excel data.

Regards

3

@anjani_priya,

  1. Read excel files in dtFile1 & dtFile2 respectively.
  2. Use For Each Row in data table activity to iterate through dtFile2
  3. Inside the loop, add an “If” activity to check if the code exists in dtFile1. Use this condition.
dtFile1.Select("[CodeColumn] = '" + row2("CodeColumn").ToString() + "'").Length = 0

If the condition is true, add an “Add Data Row” activity to add the row to dtFile1.

Thanks,
Ashok :slight_smile:

1 Like
4

@anjani_priya

You can use linq for the same to find extra rows and then use append range with the output

Use if condition with Dt2.AsEnumerable.Any(function(x) dt1.AsEnumerable.All(function(y) Not y("CodeColumn).ToString.Equals(x("CodeColumn").ToString))

Then on the then side use assign with

Dt2 = Dt2.AsEnumerable.Where(function(x) dt1.AsEnumerable.All(function(y) Not y("CodeColumn).ToString.Equals(x("CodeColumn").ToString)).CopyToDataTable

On else side no unmatched rows are there

Edit: changed function from Any to All

Cheers

1 Like
Forum Engagement Daily Reports
5

sheet1.xlsx (10.5 KB)
sheet2.xlsx (8.6 KB)
sheet1 source
sheet2 data
expected sheet is expected output

6

dt1 and function(x) refers to first excel file?
and
dt2 and function(y) refers to second excel file?

7

@anjani_priya

Yes thats correct…I hope you need to copy data from excel2 to excel1…if not change them accordingly

Cheers

1 Like
8

The data should not exactly copy but it should compare the codes and sheet2 extra codes should copy in sheet1

9

@anjani_priya

That is what the code does ideally…are you getting whole data?

If you see the where condition that is what it does

Edit : I have done a modification to above code from any to All please check the same…this should work as expected

Cheers

11

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