Need LINQ For below datatable

Help Studio
, , ,
1

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.

image
image1320×358 11.8 KB

ID match then only extract details from input.

Thanks
Vicky

2

@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

image
image1229×524 34.6 KB

Hope this helps

3

Hi,

Can you try the following sample?

image
image1130×833 42.9 KB

(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,

4

image
image1378×354 10.6 KB

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.

5

Hi @Vicky_K

Another approch is use Join Data Tables Activity like this

image
image764×481 12.4 KB

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

Then use Filter DataTable to remove unnecessary columns

image
image751×472 17.3 KB

6

@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

7

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()