Merge columns of two DataTables using loop in ASP.Net using C# and VB.Net

Hi hafifiw,

Refer below code.

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    DataTable dtExcelFamily = new DataTable();
    dtExcelFamily.Columns.Add("ID");
    dtExcelFamily.Columns.Add("Name");
    dtExcelFamily.Columns.Add("Date");
    dtExcelFamily.Columns.Add("Place");
    dtExcelFamily.Columns.Add("Father");
    dtExcelFamily.Columns.Add("Mother");
    dtExcelFamily.Rows.Add(5561, "Ali ", "10/10/1966", "Singapore", "Ahmed", "Nur");
    dtExcelFamily.Rows.Add(9910, "Rick", "12/1/1951", "Thailand", "Lee", "Mei");
    dtExcelFamily.Rows.Add(1920, "Mill", "1/7/1985", "Malaysia", "Zoey", "Lian");

    DataTable dtExcelFamilyDetails = new DataTable();
    dtExcelFamilyDetails.Columns.Add("ID");
    dtExcelFamilyDetails.Columns.Add("ChildName");
    dtExcelFamilyDetails.Columns.Add("ChildAge");
    dtExcelFamilyDetails.Rows.Add(5561, "Joe", 12);
    dtExcelFamilyDetails.Rows.Add(5561, "Zac", 17);
    dtExcelFamilyDetails.Rows.Add(9910, "Trichia", 21);
    dtExcelFamilyDetails.Rows.Add(9910, "Efrin", 29);
    dtExcelFamilyDetails.Rows.Add(9910, "Jack", 18);
    dtExcelFamilyDetails.Rows.Add(1920, "Nene", 9);
    dtExcelFamilyDetails.Rows.Add(1920, "Piney", 5);
    dtExcelFamilyDetails.Rows.Add(1920, "Rio", 3);
    dtExcelFamilyDetails.Rows.Add(1920, "Tini", 1);

    DataTable dt = CombineDataTable(dtExcelFamily, dtExcelFamilyDetails, "ID");

    string csv = string.Empty;
    foreach (DataColumn column in dt.Columns)
    {
        csv += column.ColumnName + ',';
    }
    csv += "\r\n";

    foreach (DataRow row in dt.Rows)
    {
        foreach (DataColumn column in dt.Columns)
        {
            csv += row[column.ColumnName].ToString().Replace(",", ";") + ',';
        }
        csv += "\r\n";
    }
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=DataTableExport.csv");
    Response.Charset = "";
    Response.ContentType = "application/text";
    Response.Output.Write(csv);
    Response.Flush();
    Response.End();
}

public DataTable CombineDataTable(DataTable dt1, DataTable dt2, string keyColumn)
{
    DataTable dtNew = new DataTable();
    foreach (DataColumn col in dt1.Columns)
    {
        dtNew.Columns.Add(col.ColumnName);
    }
    foreach (DataColumn col in dt2.Columns)
    {
        if (!dtNew.Columns.Contains(col.ColumnName))
        {
            dtNew.Columns.Add(col.ColumnName);
        }
    }

    for (int i = 0; i <= dt2.Rows.Count - 1; i++)
    {
        DataRow dr = dtNew.NewRow();
        foreach (DataColumn col in dtNew.Columns)
        {
            if (dt2.Columns.Contains(col.ColumnName))
            {
                dr[col.ColumnName] = dt2.Rows[i][col.ColumnName];
            }
            else
            {
                dr[col.ColumnName] = dt1.Select(keyColumn + "='" + dt2.Rows[i][0] + "'")[0][col.ColumnName];
            }
        }
        dtNew.Rows.Add(dr);
        dtNew.AcceptChanges();
    }

    return dtNew;
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim dtExcelFamily As DataTable = New DataTable()
    dtExcelFamily.Columns.Add("ID")
    dtExcelFamily.Columns.Add("Name")
    dtExcelFamily.Columns.Add("Date")
    dtExcelFamily.Columns.Add("Place")
    dtExcelFamily.Columns.Add("Father")
    dtExcelFamily.Columns.Add("Mother")
    dtExcelFamily.Rows.Add(5561, "Ali ", "10/10/1966", "Singapore", "Ahmed", "Nur")
    dtExcelFamily.Rows.Add(9910, "Rick", "12/1/1951", "Thailand", "Lee", "Mei")
    dtExcelFamily.Rows.Add(1920, "Mill", "1/7/1985", "Malaysia", "Zoey", "Lian")

    Dim dtExcelFamilyDetails As DataTable = New DataTable()
    dtExcelFamilyDetails.Columns.Add("ID")
    dtExcelFamilyDetails.Columns.Add("ChildName")
    dtExcelFamilyDetails.Columns.Add("ChildAge")
    dtExcelFamilyDetails.Rows.Add(5561, "Joe", 12)
    dtExcelFamilyDetails.Rows.Add(5561, "Zac", 17)
    dtExcelFamilyDetails.Rows.Add(9910, "Trichia", 21)
    dtExcelFamilyDetails.Rows.Add(9910, "Efrin", 29)
    dtExcelFamilyDetails.Rows.Add(9910, "Jack", 18)
    dtExcelFamilyDetails.Rows.Add(1920, "Nene", 9)
    dtExcelFamilyDetails.Rows.Add(1920, "Piney", 5)
    dtExcelFamilyDetails.Rows.Add(1920, "Rio", 3)
    dtExcelFamilyDetails.Rows.Add(1920, "Tini", 1)

    Dim dt As DataTable = CombineDataTable(dtExcelFamily, dtExcelFamilyDetails, "ID")

    Dim csv As String = String.Empty
    For Each column As DataColumn In dt.Columns
        csv += column.ColumnName + ","c
    Next
    csv += vbCr & vbLf
    For Each row As DataRow In dt.Rows
        For Each column As DataColumn In dt.Columns
            csv += row(column.ColumnName).ToString().Replace(",", ";") + ","c
        Next
        csv += vbCr & vbLf
    Next
    Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", "attachment;filename=DataTableExport.csv")
    Response.Charset = ""
    Response.ContentType = "application/text"
    Response.Output.Write(csv)
    Response.Flush()
    Response.End()
End Sub

Public Function CombineDataTable(ByVal dt1 As DataTable, ByVal dt2 As DataTable, ByVal keyColumn As String) As DataTable
    Dim dtNew As DataTable = New DataTable()
    For Each col As DataColumn In dt1.Columns
        dtNew.Columns.Add(col.ColumnName)
    Next
    For Each col As DataColumn In dt2.Columns
        If Not dtNew.Columns.Contains(col.ColumnName) Then
            dtNew.Columns.Add(col.ColumnName)
        End If
    Next
    For i As Integer = 0 To dt2.Rows.Count - 1
        Dim dr As DataRow = dtNew.NewRow()
        For Each col As DataColumn In dtNew.Columns
            If dt2.Columns.Contains(col.ColumnName) Then
                dr(col.ColumnName) = dt2.Rows(i)(col.ColumnName)
            Else
                dr(col.ColumnName) = dt1.Select(keyColumn & "='" & dt2.Rows(i)(0) & "'")(0)(col.ColumnName)
            End If
        Next
        dtNew.Rows.Add(dr)
        dtNew.AcceptChanges()
    Next

    Return dtNew
End Function