Dear Masters, Can u give Linq query to merge this three data tables.Tq

[lakshmi_narayana_ch (kristen)]

Linq to merge 3 data tables.xlsx (9.1 KB)

[raja.arslankhan]

@lakshmi_narayana_ch

How to copy one column from DT A to another column DT B - LINQ query

@Hiba_B Assumptions: take A Col Values when Col is not present in B A and B DataTables have same row count So we can implement a dynamic conditional side by side merge Calculate the cols not present in B: [grafik] [grafik] [grafik] prepare dt 3 and add missing cols /transfer also other col settings e.g. datatype [grafik] [grafik] Merge rows: [grafik] (From i In Enumerable.Range(0,dt2.Rows.Count) Let raB = dt2.Rows(i).ItemArray Let raA = arrMissingCols.Select(Function (x) dt1.Rows(i…

How to merge the excel table properly

@Vi_ciel have a look on following flow, supporting different rows count e.g. on dt1, dt2 [grafik] With datatable merge details: [grafik] input / output: [grafik] Kindly note: in case of multiple datatables will have the same column name, it has to be handled in advance by making the col names unique find starter help here: SideBySideMerge_AlsoDifferentRCnts.xaml (10.8 KB)

please read these two threads

[ashwin.ashok ((TheCodingTheory))]

Hi @lakshmi_narayana_ch ,

Are you sure you want LINQ?
Because it can get pretty complicated…how complicated you ask?

Well here you go:

(From row In dt_main.AsEnumerable()
Let zipCountCountry = dt1.AsEnumerable().Where(Function(w) row("ID").ToString.Trim.Equals(w("ID").ToString.Trim))
Let pin = dt2.AsEnumerable.Where(Function(w) row("ID").ToString.Trim.Equals(w("ID").ToString.Trim))
Let firstCond = If(zipCountCountry.Count.Equals(0), False, True)
Let secCond = If(pin.Count.Equals(0), False, True)
Let ra = New Object() _
	{	
		row("ID"), 
		If(firstCond AndAlso String.IsNullOrEmpty(row("Zip").ToString),zipCountCountry.Select(Function(s) s("Zip").ToString).First(),row("Zip")),
		If(firstCond AndAlso String.IsNullOrEmpty(row("Count").ToString),zipCountCountry.Select(Function(s) s("Count").ToString).First(),row("Count")),
		If(firstCond AndAlso String.IsNullOrEmpty(row("Country").ToString),zipCountCountry.Select(Function(s) s("Country").ToString).First(),row("Country")),
		If(secCond AndAlso String.IsNullOrEmpty(row("Pin").ToString),pin.Select(Function(s) s("Pin").ToString).First(),row("Pin"))
	}
Select dt_result.Rows.Add(ra)).CopyToDataTable()

This is something I came up with on the spot so its a little bulky.

I’m sure you can reduce things here and there and make it more readable, but Dot Net syntax is very bulky so I’d recommend using VBA for excel automation instead.

Problem is if you decide to add/omit columns tomorrow, you have to edit the code. VBA is easier to work with imo.

Here is the workflow if you are interested.
MergeThreeTables.xaml (7.1 KB)

Kind Regards,
Ashwin A.K

[kumar.varun2 (Varun Kumar)]

Hi @lakshmi_narayana_ch

It can approached using the Join

The LINQ used

dt =

(
	From r3 In 
		(
			From r1 In dt1
			Join r2 In dt2
			On r1("ID").ToString.Trim Equals r2("ID").ToString.Trim
			Let carr1 = r1.Table.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).ToArray 
			Let arr = carr1.Select(Function(c) If(String.IsNullOrEmpty(r1(c).ToSTring.Trim), If(r2.Table.Columns.Contains(c), r2(c), r1(c)), r1(c))).ToArray
			Select dt.Rows.Add(arr)
		)
	Join r4 In dt3
	On r3("ID").ToString.Trim Equals r4("ID").ToString.Trim
	Let carr2 = r3.Table.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).ToArray
	Let rarr = carr2.Select(Function(c) If(String.IsNullOrEmpty(r3(c).ToSTring.Trim), If(r4.Table.Columns.Contains(c), r4(c), r3(c)), r3(c))).ToArray
	Select dt.Rows.Add(rarr)
).CopyToDataTable

Output

Please refer the xaml file

DT_MergeThreeDTs.xaml (7.3 KB)