Compare two datatables with two columns

Learning Initatives Automation Starter
,
1

Hello Experts,

I have two excel files to compare and extract the same data and the different data. I used read range activity to convert datatables. I want to compare two datatables with two columns but the datatables are not the same format: for example these two datables have different columns numbers and row numbers. In my example, I used policy number (Policy) is the primary key. If the two datatables have the same policy number, I want to extract the data and save as excel file. If also the policy number is different, I want to save it as excel file. I attached the two excel files as reference.
If there is any solution or workaround, please let me know.
Best regards.

These are two input excel files to compare.
Finance Comission Statement.xlsx (9.5 KB)
5. Jul’22 Portal Business Report (All).xlsx (12.8 KB)

These are expected output excel files as match excel and unmatch excel.
Match.xlsx (11.7 KB)
Unmatch.xlsx (10.7 KB)

2

If there is any solution or workaround, please let me know.

3

Hi @Zaw_Win_Htun1 ,

It seems that you would want to Separate the matched cases and Non-Matched cases as separate excel files. But still there are certain uncertainties in describing the logic.

To make it clear, Could you also provide us with the Expected Output for the Input files that you have provided. In this way we could be sure to suggest/deliver the right solution.

4

I want the matching datatable as excel format and also un-matching datatable as excel format.

5

Hey @Zaw_Win_Htun1,

Use the below queries.
Common Values

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

UnCommon Values

dt_UnCommonRows = dt1.AsEnumerable().Except(dt2.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

Thanks,
Sanjit

6

When the two datatables are not the same format, do these two queries work? for example, one datatable has many rows and many columns while another datatable has less columns.

7

Hey @Zaw_Win_Htun1,

Yes this query would work, because if dt1 is having 3 columns and dt2 having 2 column, if you want the rows of dt1 by comparing with dt2 in that case you will get 3 columns in the output as dt1 has 3 columns.
Common&UniqueRows_Between_Two_Datatables.zip (2.8 KB)

PFA the flow for reference.

Thanks,
Sanjit

1 Like
8

If I swaped the first column and second column, it does not work. If I added more columns to datable, it does not work. I have got the issue. Thanks.

1 Like