Need LINQ For below datatable

Hi All,

I received two types of data,

  1. CSV file with lakhs of data like database
  2. input with thousands of data,

We have column called id in both the file need to compare and if it matches which mean ID is present in both means need to fetch other column data in input.

please find attachment for more details.

ID match then only extract details from input.

Thanks
Vicky

@Vicky_K

hi

can you try with invoke code activity

Dim counter As Int32
For Each row In dt1.AsEnumerable
	If dt2.AsEnumerable.any(Function(a) CInt(a(0).ToString).Equals(CInt(row(0).ToString))) Then
		counter=dt2.AsEnumerable.ToList.FindIndex(Function(b) CInt(b(0).tostring).equals(CInt(row(0).ToString)))
		dt2.Rows(counter).Item(1)=row(1).ToString
	End If
	Next
	

	dt2=dt2.AsEnumerable.Where(Function(a) a(1).ToString<>"").CopyToDataTable
	

Hope this helps

Hi,

Can you try the following sample?

(From t2 In dt2.AsEnumerable()
Join t1 In dt1.AsEnumerable() On t2("ID").ToString Equals t1("ID").ToString
Where (Not String.IsNullOrEmpty(t1("NAME").ToString))
Let ra = New Object(){t2("ID").ToString,t1("NAME").ToString,t2("Status").ToString}
Select dtResult.Rows.Add(ra)).CopyToDataTable()

Sample
Sample20231124-7L.zip (8.8 KB)

Regards,


I need particular coulmn data from input not all the data if ID matches means i need to extract ID and Name (from database) and Status (from input)only.

Hi @Vicky_K

Another approch is use Join Data Tables Activity like this

The output Data Table will have ID,NAME,Gender,ID_1,NAME_1,Status

Then use Filter DataTable to remove unnecessary columns

@Vicky_K

can you try this want i understand is that, if id matches you want ID,Name from first datatable and Status from second datatable

Dim counter As Int32
Dim finaldt As DataTable
finaldt=dt2.Clone
For Each row In dt1.AsEnumerable
	If dt2.AsEnumerable.any(Function(a) CInt(a(0).ToString).Equals(CInt(row(0).ToString))) Then
		counter=dt2.AsEnumerable.ToList.FindIndex(Function(b) CInt(b(0).tostring).equals(CInt(row(0).ToString)))
		finaldt.LoadDataRow({row(0).ToString,row(1).ToString,dt2.rows(counter).Item(2)},False)
	End If
	Next
	
	dt2=finaldt

	

let me know is this the requirement

and you can also pass the column names instead of column index

Hi @Vicky_K

Can you try this

dtResult = dtExcel.AsEnumerable().
Where(Function(rowExcel) dtCSV.AsEnumerable().
Select(Function(rowCSV) rowCSV(“ID”).ToString.Trim).
Contains(rowExcel(“ID”).ToString.Trim)).
CopyToDataTable()