How to compare 2 data tables?

Help Studio
, ,
1

In datatable 1 I have 1 column and in datatable 2 i have one column , In sheet3 I want dt1 and dt2 and status column as matched and unmatched…How can i get this?

2

Hi @Aparna_30

  1. Excel Application Scope (To read data from Excel file)

    • Read Range: “Sheet1” into dt1
    • Read Range: “Sheet2” into dt2

  2. Build DataTable (To create dtResult)

    • Add Data Column: “Value from dt1”
    • Add Data Column: “Value from dt2”
    • Add Data Column: “Status”

  3. For Each Row (For each row in dt1)

    • For Each Row (For each row in dt2)
      • If (Compare values and find match)
        • Add DataRow (Array of “Value from dt1”, “Value from dt2”, “Matched”) to dtResult
        • Break (exit inner loop)
      • Else
        • Add DataRow (Array of “Value from dt1”, “”, “Unmatched”) to dtResult
        • Add DataRow (Array of “”, “Value from dt2”, “Unmatched”) to dtResult

  4. Write Range (To write dtResult into “Sheet3” of the Excel file)

Hope it helps!!

3

Hi @Aparna_30

Please find the below xaml for your reference

ExcelCompare.zip (175.2 KB)

I hope it helps!!

4

@Aparna_30

Please try this

  1. Use join datatable on dt1 and dt2…and select full…
  2. Use add data column to add a status column
  3. From the output datatable say dt…us ethe below to fill the status column in invoke code
    Dt.AsEnumerable.ToList.ForEach(sub(r) r("Status") = If(r(0).ToString.Equals(r(1).ToString),"Matched","UnMatched")) in invoke code add dt as in/out

Cheers

1 Like
5

Hi @Aparna_30

=> Use Read Range workbook activity to read the Sheet1 → Output - dt1
=> Use Read Range workbook activity to read the Sheet2 → Output - dt2

=> Join Data Table (dt1, dt2, JoinType = Inner, JoinColumns = “CommonColumn”) → Output - dtMerged

=> Use Add Data Column activity to add the Status column to the dtMerged Datatable (dtMerged, “Status”, Type = String) → dtMerged

=> For Each Row in datatable activity to iterate the rows in the dtMerged Datatable (Row in dtMerged)
=> Inside the For each row in datatable activity insert the If condition

If (Row("CommonColumn") in dt1 = Row("CommonColumn") in dt2)
       Assign: Row("Status") = "matched"
Else
      Assign: Row("Status") = "unmatched"

=> Use Write Range workbook activity to write the dtMerged datatable into the Sheet3 in the excel file. (Sheet3) → dtMerged

Hope it helps!!

6

image
image758×118 3.46 KB

I am getting this error in invoke code
image

7

Hi @Aparna_30

Double quote is missing in status once check it

image
image952×154 21.8 KB

1 Like
8

@Aparna_30

Please chekc the closing double quotes after status

Cheers

10

ComparisionReport_TR_COSUM_0201055167CX01.xlsx (12.3 KB)
I want to compare test sheet and prod sheet and update the status column in sheet3 as matched or unmatched and for unmatched i want to set any colour…

11

@Aparna_30

You can write the data to sheet 3 …

And for coloring teo options …have conditional formatting in sheet3 already or use set range coloe and loop through datatable or each row in excel and set it

Cheers

12

The data is not coming properly i have 58 records and it is showing me as 62 records and all the not matched records are coming at last it is not in the sequence

13

@Aparna_30

What sequence to follow?

May I know which is the base data dt1 or dt2?

Cheers

14

In the sheet 3 i want Test column from sheet1 ,prod column from sheet 2, and status column …base data is dt1 that is Test

15

Hi @Aparna_30 - Can you please give a try with the below attached workflow

SampleWorkflow.zip (14.2 KB)

Output - Total 58 rows

out
out903×498 189 KB

out
out903×464 236 KB

1 Like
16

Thank you so much I was trying this from 3 hours!!!

17

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