Compare two Datatables(LINQ)

rockchord · October 12, 2024, 6:07am

Hey Team i have two datatable(DT1、DT2 Hundreds of thousands of data)

DT1

ID	Company	Code	Name	Num	Phone
1	SAAB	P0340	Abigail	A111111	111
2	Speedway	S6665	Julia	J444444	444
3	netflix	Q3365	Vivien	V666666	666

DT2

ID	Company	Code	Name	Num	Phone
1	SAAB	P0340	Abi	A111111	111
2	Speedway	S6665	Julia	JJJ	404
3	netflix	Q3365	Vivien	V666666	606

Result

ID	Company	Code	Name	Num	Phone	Error
1	SAAB	P0340	Abi	A111111	111	Name
2	Speedway	S6665	Julia	JJJ	404	Num、Phone
3	netflix	Q3365	Vivien	V666666	606

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

It can compare very quickly, but it cannot display the wrong fields.
Is there any way to overcome this? Thank you very much.

rashmisingh2695 · October 12, 2024, 7:39am

Hi @rockchord

Please try to use this code in invoke code activity and you should be able to get the output
First build the OutputDT with required columns and then use the invoke code

    For Each row1 As DataRow In dtInput1.Rows
        Dim row2 As DataRow = dtInput2.AsEnumerable().Where(Function(r) r("ID").ToString() = row1("ID").ToString()).FirstOrDefault()
        If row2 IsNot Nothing Then
            Dim errors As New List(Of String)
            If row1("Company").ToString() <> row2("Company").ToString() Then errors.Add("Company")
            If row1("Code").ToString() <> row2("Code").ToString() Then errors.Add("Code")
            If row1("Name").ToString() <> row2("Name").ToString() Then errors.Add("Name")
            If row1("Num").ToString() <> row2("Num").ToString() Then errors.Add("Num")
            If row1("Phone").ToString() <> row2("Phone").ToString() Then errors.Add("Phone")
            dtOutput.Rows.Add(row2("ID"), row2("Company"), row2("Code"), row2("Name"), row2("Num"), row2("Phone"), String.Join(", ", errors))
        End If
    Next

Comparison of two input data tables showing discrepancies in names and phone numbers, with an output table highlighting the errors. (Captioned by AI)

Compare_DT.xaml (12.3 KB)

rockchord · October 12, 2024, 8:16am

Compiler error(s) encountered processing expression

I will try my best to try again

sudster · October 12, 2024, 9:28am

Here’s another solution:

Workflow file:
CompareDTsAndFillErrorCol.xaml (16.3 KB)

rockchord · October 12, 2024, 9:58am

Modify DT2

ID	Company	Code	Name	Num	Phone
1	netflix	Q3365	Vivien	V666666	606
2	SAAB	P0340	Abi	A111111	111
3	Speedway	S6665	Julia	JJJ	404
4	Xman	G6	Alex	JJJ	777

Can you help me with this math problem?

sudster · October 12, 2024, 10:07am

What is the expected output?

rockchord · October 12, 2024, 10:16am

Result

ID	Company	Code	Name	Num	Phone	Error
1	SAAB	P0340	Abi	A111111	111	Name
2	Speedway	S6665	Julia	JJJ	404	Num、Phone
3	netflix	Q3365	Vivien	V666666	606

sudster · October 12, 2024, 10:21am

That’s easy:

Change the Join Type in the xaml:

rashmisingh2695 · October 12, 2024, 10:59am

Workflow:
Compare_DT.xaml (12.4 KB)

rockchord · October 12, 2024, 11:06am

Documents invalid。

Maybe my software version is too old

rashmisingh2695 · October 12, 2024, 11:08am

Try to use this expression in Invoke code

    For Each row1 As DataRow In dtInput1.Rows
        Dim row2 As DataRow = dtInput2.AsEnumerable().Where(Function(r) r("Company").ToString() = row1("Company").ToString()).FirstOrDefault()
        If row2 IsNot Nothing Then
            Dim errors As New List(Of String)
            If row1("Company").ToString() <> row2("Company").ToString() Then errors.Add("Company")
            If row1("Code").ToString() <> row2("Code").ToString() Then errors.Add("Code")
            If row1("Name").ToString() <> row2("Name").ToString() Then errors.Add("Name")
            If row1("Num").ToString() <> row2("Num").ToString() Then errors.Add("Num")
            If row1("Phone").ToString() <> row2("Phone").ToString() Then errors.Add("Phone")
            dtOutput.Rows.Add(row1("ID"), row2("Company"), row2("Code"), row2("Name"), row2("Num"), row2("Phone"), String.Join(", ", errors))
        End If
    Next

rockchord · October 12, 2024, 9:04pm

He works great! Thanks for the help

sudster · October 13, 2024, 4:33am

@rashmisingh2695 , can I please ask a clarification since the OP @rockchord said in the first post:

In you code (specifically the line below), does all rows in DT2 iterate for every row of DT1? For example, if DT1 and DT2 100 rows each, then would the total iterations be 10000?, for 1000 rows 1 million iterations etc?

Dim row2 As DataRow = dtInput2.AsEnumerable().Where(Function(r) r("ID").ToString() = row1("ID").ToString()).FirstOrDefault()

Thanks!

rashmisingh2695 · October 13, 2024, 5:05am

dtInput2 is iterating through all rows until a matching row is found.

So, if DT1 and DT2 100 rows each then yes the total number of comparisons is 10,000 in the worst-case scenario.

rockchord · October 17, 2024, 4:59am

Can I add one more rule, please?

“Name、Num1、Phone1 “and” Name2、Num2、Phone2”
The Values may be reversed,It’s like ID4 in a table.

DT1

ID	Company	Code	Name1	Num1	Phone1	Name2	Num2	Phone2
1	SAAB	P0340	Abigail	AAA	111	Ada	BBB	222
2	Speedway	S6665	Julia	J444444	444
3	netflix	Q3365	Vivien	V666666	666
4	Disney	X123	Bonnie	BBB	333	Claire	CCC	444

DT2

ID	Company	Code	Name1	Num1	Phone1	Name2	Num2	Phone2
1	Speedway	S6665	Julia	JJJ	404
2	Disney	X123	Bonnie	BBB	333	Claire	CCC	4
3	SAAB	P0340	Ada	BBB	222	Abigail	AAA	111
4	netflix	Q3365	Vivien	V	666

Result

ID	Company	Code	Name1	Num1	Phone1	Name2	Num2	Phone2	Error
1	SAAB	P0340	Abigail	AAA	111	Ada	BBB	222
2	Speedway	S6665	Julia	J444444	444				Num1、Phone1
3	netflix	Q3365	Vivien	V666666	666				Num1
4	Disney	X123	Bonnie	BBB	333	Claire	CCC	444	Phone2

rashmisingh2695 · October 17, 2024, 7:05am

Can you please try with this code

For Each row1 As DataRow In dtInput1.Rows
    Dim row2 As DataRow = dtInput2.AsEnumerable().Where(Function(r) r("Company").ToString() = row1("Company").ToString()).FirstOrDefault()
    If row2 IsNot Nothing Then
        Dim errors As New List(Of String)

        If row1("Company").ToString() <> row2("Company").ToString() Then errors.Add("Company")
        If row1("Code").ToString() <> row2("Code").ToString() Then errors.Add("Code")

        ' Compare Name1
        If Not (String.IsNullOrEmpty(row1("Name1").ToString()) AndAlso String.IsNullOrEmpty(row2("Name1").ToString())) Then
            If row1("Name1").ToString() <> row2("Name1").ToString() Then errors.Add("Name1")
        End If

        ' Compare Num1
        If Not (String.IsNullOrEmpty(row1("Num1").ToString()) AndAlso String.IsNullOrEmpty(row2("Num1").ToString())) Then
            If row1("Num1").ToString() <> row2("Num1").ToString() Then errors.Add("Num1")
        End If

        ' Compare Phone1
        If Not (String.IsNullOrEmpty(row1("Phone1").ToString()) AndAlso String.IsNullOrEmpty(row2("Phone1").ToString())) Then
            If row1("Phone1").ToString() <> row2("Phone1").ToString() Then errors.Add("Phone1")
        End If

        ' Compare Name2
        If Not (String.IsNullOrEmpty(row1("Name2").ToString()) AndAlso String.IsNullOrEmpty(row2("Name2").ToString())) Then
            If row1("Name2").ToString() <> row2("Name2").ToString() Then errors.Add("Name2")
        End If

        ' Compare Num2
        If Not (String.IsNullOrEmpty(row1("Num2").ToString()) AndAlso String.IsNullOrEmpty(row2("Num2").ToString())) Then
            If row1("Num2").ToString() <> row2("Num2").ToString() Then errors.Add("Num2")
        End If

        ' Compare Phone2
        If Not (String.IsNullOrEmpty(row1("Phone2").ToString()) AndAlso String.IsNullOrEmpty(row2("Phone2").ToString())) Then
            If row1("Phone2").ToString() <> row2("Phone2").ToString() Then errors.Add("Phone2")
        End If

        dtOutput.Rows.Add(row1("ID"), row1("Company"), row1("Code"), row1("Name1"), row1("Num1"), row1("Phone1"), row1("Name2"), row1("Num2"), row1("Phone2"), String.Join(", ", errors))
    End If
Next

rockchord · October 17, 2024, 8:09am

The image shows two tables, DT1 and DT2, highlighting a pair of rows where values in columns "Name1," "Num1," "Phone1," "Name2," "Num2," and "Phone2" are swapped between the tables. (Captioned by AI)
Can such a situation be considered correct?

rashmisingh2695 · October 17, 2024, 9:26am

This code should work for such situtations

For Each row1 As DataRow In dtInput1.Rows
    Dim row2 As DataRow = dtInput2.AsEnumerable().Where(Function(r) r("Company").ToString() = row1("Company").ToString()).FirstOrDefault()
    If row2 IsNot Nothing Then
        Dim errors As New List(Of String)

        If row1("Company").ToString() <> row2("Company").ToString() Then errors.Add("Company")
        If row1("Code").ToString() <> row2("Code").ToString() Then errors.Add("Code")

        ' Compare Name1 with Name1 and Name2, then Name2 with Name1 and Name2
        If Not (String.IsNullOrEmpty(row1("Name1").ToString()) AndAlso String.IsNullOrEmpty(row2("Name1").ToString())) Then
            If row1("Name1").ToString() <> row2("Name1").ToString() AndAlso row1("Name1").ToString() <> row2("Name2").ToString() Then
                errors.Add("Name1")
            End If
        End If

        If Not (String.IsNullOrEmpty(row1("Name2").ToString()) AndAlso String.IsNullOrEmpty(row2("Name1").ToString())) Then
            If row1("Name2").ToString() <> row2("Name1").ToString() AndAlso row1("Name2").ToString() <> row2("Name2").ToString() Then
                errors.Add("Name2")
            End If
        End If

        ' Compare Num1 with Num1 and Num2, then Num2 with Num1 and Num2
        If Not (String.IsNullOrEmpty(row1("Num1").ToString()) AndAlso String.IsNullOrEmpty(row2("Num1").ToString())) Then
            If row1("Num1").ToString() <> row2("Num1").ToString() AndAlso row1("Num1").ToString() <> row2("Num2").ToString() Then
                errors.Add("Num1")
            End If
        End If

        If Not (String.IsNullOrEmpty(row1("Num2").ToString()) AndAlso String.IsNullOrEmpty(row2("Num1").ToString())) Then
            If row1("Num2").ToString() <> row2("Num1").ToString() AndAlso row1("Num2").ToString() <> row2("Num2").ToString() Then
                errors.Add("Num2")
            End If
        End If

        ' Compare Phone1 with Phone1 and Phone2, then Phone2 with Phone1 and Phone2
        If Not (String.IsNullOrEmpty(row1("Phone1").ToString()) AndAlso String.IsNullOrEmpty(row2("Phone1").ToString())) Then
            If row1("Phone1").ToString() <> row2("Phone1").ToString() AndAlso row1("Phone1").ToString() <> row2("Phone2").ToString() Then
                errors.Add("Phone1")
            End If
        End If

        If Not (String.IsNullOrEmpty(row1("Phone2").ToString()) AndAlso String.IsNullOrEmpty(row2("Phone1").ToString())) Then
            If row1("Phone2").ToString() <> row2("Phone1").ToString() AndAlso row1("Phone2").ToString() <> row2("Phone2").ToString() Then
                errors.Add("Phone2")
            End If
        End If

        dtOutput.Rows.Add(row1("ID"), row1("Company"), row1("Code"), row1("Name1"), row1("Num1"), row1("Phone1"), row1("Name2"), row1("Num2"), row1("Phone2"), String.Join(", ", errors))
    End If
Next

rockchord · October 17, 2024, 11:58am

rashmisingh2695:

For Each row1 As DataRow In dtInput1.Rows
    Dim row2 As DataRow = dtInput2.AsEnumerable().Where(Function(r) r("Company").ToString() = row1("Company").ToString()).FirstOrDefault()
    If row2 IsNot Nothing Then
        Dim errors As New List(Of String)

        If row1("Company").ToString() <> row2("Company").ToString() Then errors.Add("Company")
        If row1("Code").ToString() <> row2("Code").ToString() Then errors.Add("Code")

        ' Compare Name1 with Name1 and Name2, then Name2 with Name1 and Name2
        If Not (String.IsNullOrEmpty(row1("Name1").ToString()) AndAlso String.IsNullOrEmpty(row2("Name1").ToString())) Then
            If row1("Name1").ToString() <> row2("Name1").ToString() AndAlso row1("Name1").ToString() <> row2("Name2").ToString() Then
                errors.Add("Name1")
            End If
        End If

        If Not (String.IsNullOrEmpty(row1("Name2").ToString()) AndAlso String.IsNullOrEmpty(row2("Name1").ToString())) Then
            If row1("Name2").ToString() <> row2("Name1").ToString() AndAlso row1("Name2").ToString() <> row2("Name2").ToString() Then
                errors.Add("Name2")
            End If
        End If

        ' Compare Num1 with Num1 and Num2, then Num2 with Num1 and Num2
        If Not (String.IsNullOrEmpty(row1("Num1").ToString()) AndAlso String.IsNullOrEmpty(row2("Num1").ToString())) Then
            If row1("Num1").ToString() <> row2("Num1").ToString() AndAlso row1("Num1").ToString() <> row2("Num2").ToString() Then
                errors.Add("Num1")
            End If
        End If

        If Not (String.IsNullOrEmpty(row1("Num2").ToString()) AndAlso String.IsNullOrEmpty(row2("Num1").ToString())) Then
            If row1("Num2").ToString() <> row2("Num1").ToString() AndAlso row1("Num2").ToString() <> row2("Num2").ToString() Then
                errors.Add("Num2")
            End If
        End If

        ' Compare Phone1 with Phone1 and Phone2, then Phone2 with Phone1 and Phone2
        If Not (String.IsNullOrEmpty(row1("Phone1").ToString()) AndAlso String.IsNullOrEmpty(row2("Phone1").ToString())) Then
            If row1("Phone1").ToString() <> row2("Phone1").ToString() AndAlso row1("Phone1").ToString() <> row2("Phone2").ToString() Then
                errors.Add("Phone1")
            End If
        End If

        If Not (String.IsNullOrEmpty(row1("Phone2").ToString()) AndAlso String.IsNullOrEmpty(row2("Phone1").ToString())) Then
            If row1("Phone2").ToString() <> row2("Phone1").ToString() AndAlso row1("Phone2").ToString() <> row2("Phone2").ToString() Then
                errors.Add("Phone2")
            End If
        End If

        dtOutput.Rows.Add(row1("ID"), row1("Company"), row1("Code"), row1("Name1"), row1("Num1"), row1("Phone1"), row1("Name2"), row1("Num2"), row1("Phone2"), String.Join(", ", errors))
    End If
Next

He works great!
I can’t thank you enough for your kindness.

system · October 20, 2024, 11:59am

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

Topic		Replies	Views
Compare Datatables one specific column with other specific Column and Get Matched and Not Matched Records` Academy Feedback datatable	54	20133	April 15, 2021
Compare 2 dataTable according to 1 specific column and take the rows which has different values Activities excel , activities , question	17	2185	August 7, 2021
Create new table by comparing two other tables: New table contains items from table1 that were not found in table2 Help	25	2433	January 26, 2020
Compare two datatable Help activities , studio	9	2604	November 27, 2019
COMPARE TWO DATATABLES AND GET NEW DATATABLES WITHOUT USING LOOPS! Help orchestrator , activities , studio	8	5937	April 30, 2022

Compare two Datatables(LINQ)

Related topics