Compare two columns from two data table variable

Help Robot
,
1

Hi Everyone,

Hope you’re doing good!

I have 2 excel files and I am fetching some data using Excel as db storing data in a data table variable (dt1, dt2). Both dt variables have a column containing dates. I want to filter the dates which are present in dt1 and not present in dt2.

What is the effective way to achieve this.

Note:

  1. Column name will be different in both the dt variables
  2. Both will be stored virtually in dt variable and not in an excel file.
1 Like
2

Hi,

How about the following sample?

image
image1119×1033 30.8 KB

arrDr = dt1.AsEnumerable.Where(Function(r) not dt2.AsEnumerable.Any(Function(r2) r2("date").ToString=r("date").ToString)).ToArray()

Sample
Sample20231008-1aL.zip (12.0 KB)

Regards,

3

@Rupesh_Parle

  1. add new colum for dt1 with name status
  2. for each in dt1
  3. if: Dt2.AsEnumerable().Any(Function (x) x(“Date2”).ToString.Equals(Row(“Date2”).tostring))
  4. then : Row(“Status”)= “yes”
  5. outside of for each filter dt1 remove “Status” = “yes” and in output col remove “status”
4

Hi @Rupesh_Parle

Try this:

datesInDt1NotInDt2 = (From row1 In dt1.AsEnumerable() Select row1.Field(Of DateTime)("DateColumn1")).Except((From row2 In dt2.AsEnumerable() Select row2.Field(Of DateTime)("DateColumn2"))).ToArray()
5

Use this in assign activity to get the unmatched records based on comparing one specific column

Out_NonMatched_Data = In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(function(r) r(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()

Cheers @Rupesh_Parle

6

Hi @Yoichi ,

Thank you so much for the response.

My dt1 variable doesn’t contain column name in it, is there any way to assign column name to that dt variable?

The data in dt1 is fetched from array of string variable by using generate data table from text.

1 Like
7

If you don’t have column name then you can try with columnindex

Where index position starts from 0 for first column. @Rupesh_Parle

8

Hi @Rupesh_Parle

Compare “Column1” from dt1 with “Column2” from dt2 and get the matching rows.

matchingRows = from row1 in dt1.AsEnumerable()
join row2 in dt2.AsEnumerable()
on row1.Field<string>("Column1") equals row2.Field<string>("Column2")
elect row1;

Convert the matching rows to a new DataTable if needed.

MatchingDataTable = matchingRows.CopyToDataTable();

Need to get the non-matching rows, you can use the Except method

nonMatchingRows = dt1.AsEnumerable().Except(matchingRows, DataRowComparer.Default);
9

@Rupesh_Parle

Then give indexes instead of column name like this: row(0)
Note: Indexes start from 0

datesInDt1NotInDt2 = (From row1 In dt1.AsEnumerable() Select row1(0)).Except((From row2 In dt2.AsEnumerable() Select row2(1))).ToArray()

10

Hi Everyone,

Thank you for your answers.

One trick thing the dates in dt1 and in dt2 are in different format.

DT1 is in (dd MMM yyyy) format. Eg., 06 Jan 2023
DT2 is in (mm/dd/yyyy) format. Eg., 1/27/2023

@Yoichi ,@supriya117 , @Gokul001 , @Palaniyappan , @Yazan_Otaibi
Any Help?

Compare two dt containing dates with different format
11

Hi,

If the column is set as Date in the sheet, the above sample also works.
If the column is set as text etc., the following will work.

dt1.AsEnumerable.Where(Function(r) not dt2.AsEnumerable.Any(Function(r2) CDate(r2("date"))=CDate(r("date")))).ToArray()

Sample
Sample20231008-1aLv2.zip (16.5 KB)

Regards,

12

@Rupesh_Parle

Try this:
datesInDt1NotInDt2 = dt1.AsEnumerable().Select(Function(row1) DateTime.ParseExact(row1(0).ToString(), "dd MMM yyyy", System.Globalization.CultureInfo.InvariantCulture)).Except(dt2.AsEnumerable().Select(Function(row2) DateTime.ParseExact(row2(1).ToString(), "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture))).ToArray()

1 Like
13

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