Join two data tables having common fields

Question

I have 2 datatable objects (dt1,dt2), which have a common filed called "File_Name". I want to create a datatable (dtFinal), combining the two datatables to be used in gridview of asp.net

Each of the tables (dt1,dt2) can have overlapping column header names, meaning if dt1 has a header as "plant_code" dt2 can also have the header as "plant_Code" but not mandatory.

My objective is to have a common datatable (dtFinal) with all the headers from all the individual datatables.

I tried something like this

  Dt1.PrimaryKey = New DataColumn() {Dt1.Columns(System.AppDomain.CurrentDomain.BaseDirectory & "\resources\files\" & "GRN" & ".csv")}
  Dt2.PrimaryKey = New DataColumn() {Dt1.Columns(System.AppDomain.CurrentDomain.BaseDirectory & "\resources\files\" & "Payment Data" & ".csv")}
  Dt1 = CsvToDataTable(System.AppDomain.CurrentDomain.BaseDirectory & "\resources\files\" & "GRN" & ".csv")
  Dt2 = CsvToDataTable(System.AppDomain.CurrentDomain.BaseDirectory & "\resources\files\" & "Payment Data" & ".csv")


  ds.Tables.Add(Dt1)
  ds.Tables.Add(Dt2)

  Dim drel As New DataRelation("EquiJoin", Dt2.Columns("File_Name"), Dt1.Columns("File_Name"))

  ds.Relations.Add(drel)

  Dim jt As New DataTable("Joinedtable")
  ds.Tables.Add(jt)
  For Each dr As DataRow In ds.Tables("Table1").Rows

      Dim parent As DataRow = dr.GetParentRow("EquiJoin")
      Dim current As DataRow = jt.NewRow()
      ' Just add all the columns' data in "dr" to the New table.

      For i As Integer = 0 To ds.Tables("Table1").Columns.Count - 1
          current(i) = dr(i)
      Next
      ' Add the column that is not present in the child, which is present in the parent.
      current("Dname") = parent("Dname")
      jt.Rows.Add(current)
  Next

  DtFinal = ds.Tables("Joinedtable")

Creating a dataset and a relation between dt1 and dt2 ...But this doesn't seem to work and giving me an error in line 1 instance of object to be declared using New.

Has someone tried something like this in Vb ? Or can my code be edited to make it work.

Further info: each datatable tb1 and tb2 have unique values in the File_Name column which is the first column. and hence can be selected as the primary key.

So you want to combine 2 tables into one if they have a matching value in the file_Name column? — Dylan, Jul 16 '15 at 21:05
no they would never have a matching value in the File_Name column.. this column is the only column that is mandatory and present in both the tables. so if we could join the 2 tables with File_Name as common column would all the headers in both the tables be present in the consolidated table ? if so then can my code be tweaked to do that ? — Mani kv, Jul 16 '15 at 21:09
Ok, if DataTable 3 already has the file_Name it shouldn't add it right? or do you not care about duplicates — Dylan, Jul 16 '15 at 21:14
yes it shouldnt add it. and there should not be duplicates as well — Mani kv, Jul 16 '15 at 21:58
You can enumerate all columns in `dt1` and add them to new table. And then, you can enumerate all columns in `dt2` and add them to new table. And then you can do `for each row-for each column` for both tables, to fill the data. — T.S., Jul 17 '15 at 05:26

T.S. · Accepted Answer · 2015-07-17T17:10:41.600

When you say, "Merge" it may mean one thing or another. For example, I may think of this method.

This is Working Code:

Public Sub  MergeTables()
    Dim t1 As New DataTable("T1")
    t1.Columns.Add("C1", GetType(String))
    t1.Columns.Add("C2", GetType(String))
    t1.Columns.Add("C3", GetType(String))

    Dim t2 As New DataTable("T2")
    t2.Columns.Add("C1", GetType(String)) 'same column
    t2.Columns.Add("C2", GetType(Integer)) ' same column, different data type
    t2.Columns.Add("C4", GetType(String)) ' different column

    Dim map As New Dictionary(Of String, String)

    Dim t3 As New DataTable("T4")
    MergeColumns(t3, t1, map)
    MergeColumns(t3, t2, map)

    Debug.WriteLine("Should be 5 columns and reality is: " & t3.Columns.Count)

    ' Add some data
    t1.Rows.Add({"data from t1 c1", "data from t1 c2", "data from t1 c3"})
    t2.Rows.Add({"data from t2 c1", 55, "data from t2 c3"})

    MergeRows(t3, t1, map)
    MergeRows(t3, t2, map)
    t3.AcceptChanges()


    For Each row As DataRow In t3.Rows
        Debug.WriteLine(String.Join(";", row.ItemArray.Select(Function(o) o.ToString()).ToArray()))
    Next

End Sub


Private Sub MergeColumns(totbl As DataTable, fromtbl As DataTable, map As Dictionary(Of String, String))
    For Each c As DataColumn In fromtbl.Columns
        If Not totbl.Columns.Contains(c.ColumnName) Then
            totbl.Columns.Add(c.ColumnName, c.DataType)
            map.Add(c.Table.TableName & "_" & c.ColumnName, c.ColumnName)
        ElseIf Not totbl.Columns(c.ColumnName).DataType.Equals(c.DataType) Then
            totbl.Columns.Add(c.Table.TableName & "_" & c.ColumnName, c.DataType)
            map.Add(c.Table.TableName & "_" & c.ColumnName, c.Table.TableName & "_" & c.ColumnName)
        Else
            map.Add(c.Table.TableName & "_" & c.ColumnName, c.ColumnName)
        End If
    Next
End Sub

Private Sub MergeRows(totbl As DataTable, fromtbl As DataTable, map As Dictionary(Of String, String))
    For Each row As DataRow In fromtbl.Rows
        Dim newRow As DataRow = totbl.NewRow()
        For Each c As DataColumn In fromtbl.Columns
            newRow(map(fromtbl.TableName & "_" & c.ColumnName)) = row(c.ColumnName)
        Next
        totbl.Rows.Add (newRow) 
    Next

End Sub

Result:

data from t1 c1 ; data from t1 c2 ; data from t1 c3 ; ; data from t2 c1 ; ; ; 55 ; data from t2 c3

But may be, you need something else. However, this is good example how to do thing like this

Thanks for the reply, This is exactly wat was required. I will modify this code as per my requirements with minute tweaks. Thanks :) — Mani kv, Jul 18 '15 at 05:51

score -1 · Answer 2 · edited May 23 '17 at 12:29

-1

You could try the DataTable Merge.

dt3 = new DataTable();

dt3 .Merge(dtOne);
dt3 .Merge(dtTwo);

Look at this post for more info/solutions. Merge 2 DataTables and store in a new one

edited May 23 '17 at 12:29

Community

1
1

answered Jul 16 '15 at 21:24

Dylan

1,068
12
25

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Cyril Durand Jul 17 '15 at 15:16
There ya go, added it . – Dylan Jul 17 '15 at 15:20
@Dylan thanks, This seem to work if they have similar column names. But in my situation its not the same. I tried the above but dt3 holds nothing . It cant merge dtOne and dtTwo. – Mani kv Jul 18 '15 at 05:49

Join two data tables having common fields

2 Answers2

Linked