Comparing one column contents of two excel file


#1

Hi,
Can anyone help me with a simple workflow for comparing contents between two excel files please. I need to make a comparison for a single column value only. Like comparing a reference value of customers in one excel file with those in next excel file. Thanks


#2

@suwalruchan365
Please find attached xaml CompareDatatables.xaml (15.5 KB)

Hope this helps :slight_smile:


#3

Hi Madhavi,
Can you please tell me where I should mention the column name that needs to be compared.
“(from dt1 in dtDatatable1.AsEnumerable() join dt2 in dtDatatable2.AsEnumerable() On dt1.Field(of String)(“ColumnInDt1”) Equals dt2.Field(of String) (“ColumnInDt2”) Select dt1).CopyToDataTable()”

Are those ‘ColumnInDt1’ and ‘ColumnInDT2’.
I made a change to these values along with excel file path but I am getting an error message.


#4

@suwalruchan365
“(from dt1 in dtDatatable1.AsEnumerable() join dt2 in dtDatatable2.AsEnumerable() On dt1.Field(of String)(“ColumnInDt1”) Equals dt2.Field(of String) (“ColumnInDt2”) Select dt1).CopyToDataTable()”

dtDatatable1 -> First datatable
dtDatatable2 -> second datatable
ColumnInDt1 -> Column name from first datatable
ColumnInDt2 -> column name from second datatable

Replace these values for the exact value from your Excel


#5

I did as you told but I am getting an error message that says,
“Object reference not set to an instance of an object.This error usually occurs when using a variable with no set value (not initialized)”


#6

@suwalruchan365 Is its possible to provide your excel values with the columns to be compared?


#7

source.xlsx (8.1 KB)
success.xlsx (7.4 KB)

Here, I need to compare the phone numbers to find users with non-matching numbers.

comparions.xaml (13.3 KB)
I tried using this workflow as well but it is scanning each record twice.


#8

CompareDatatables.xaml (20.6 KB)

The assign statement for dtResult is throwing error. May be when you pass actual data, it should work.


#9

Hi Madhavi,
Sorry to trouble you again. I passed the actual data but it’s showing an error message in the assign activity that says ‘The source contains no DataRows’.


Comparing excel contents
#10

CompareDatatables.xaml (19.0 KB)

@suwalruchan365 The issue was that the first and second excel inputs were interchanged.


#12

@Madhavi in assign statement why u taken asEnumerable()? without it also i am getting answer.