Comparing 2 excel files by column and store the mismatch data row in next tab

Help
1

Hi All,
I have 2 excel files, test1 and test2. I am comparing test1.xls with test2.xls using first Column.
I need to write the unmatched data in second tab of test1.xls.

test1,xls -
image

test2.xls-
image

Output in sheet2 of test1.xls -
image

Attached is the xls file too.
test1.xlsx (9.9 KB) test2.xlsx (8.6 KB)

Any help or xaml would be really helpful.

1 Like
2

@Zahid1 Please try like below

  1. Read 2 excel files store the output into 2 dataTable variable.

  2. Create third datatable variable with same structure as first datatable. you can do this by using clone method like below or you can create using build datatable activity.

    3rdDataTableVariable = 1stDataTableVariable.Clone

  3. Use 2 for each datarow loops with 1st datatable as input in outer loop and 2nd datatable in inner loop.

  4. With if condition check first column 1st datatable matches with first column of 2nd datatable, if value matches in inner loop set boolean variable value to “True”(Scope of boolean variable must be body of the outer loop) if you required you can compare 2nd columns from both 1st and 2nd datatables also. After completion of inner loop before going to next iteration please check the value of boolean variable.

  5. If boolean variable value is true don’t do anything, if value is false add that row from 1st datatable to 3rd datatable.

  6. outside outer for loop write the 3rd datatable to required excel file.

3

@Manjuts90 : Sorry for delayed reply, I tried the method but getting error. Is it possible for you to send me xaml. It will be really helpful

4

@Zahid1

then use the below query

DataTableVariable3 = (From x In DataTableVariable1 .AsEnumerable()
where (From y In DataTableVariable2 .AsEnumerable()
where convert.tostring(x(“Billing ID”)).equals(convert.tostring(x(“ID”)))).count=0
Select DataTableVariable1.Clone.Rows.Add(x.itemarray)).copytodatatable

5

@Zahid1 Please find the file below i have slightly changed the method.

Test.zip (167.8 KB)

1 Like
6

BlankProcess.zip (198.1 KB)

hope so this workflow work for you…
please check and let me know…

Thanks and Regards,
Parvati Thalal

7

@Manjuts90 and @parvati : I checked both the workflow and ran few scenarios. It seems to be working perfectly for me!!! Thanks a lot to both of you. Closing the thread. Sorry I am allowed to check only 1 checkbox as solution.

1 Like
8

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