Combining two c# datatables into one

Question

I have two datatables in my ASP.NET application that are filled from csv files and I am trying to combine the two into one.

Heres what the interface looks like:

When I click the 'Merge Data' button it should merge the test1.csv and test2.csv which kind of works but looks like this:

So my question is how do I align these two datatables so that all the data is on the same row?

Below is the code for the Merge Data Button:

        List<string> filepaths = new List<string>();
        List<DataTable> allTables = new List<DataTable>();
        DataTable mergedTables = new DataTable();
        int rowCount = grdFiles.Rows.Count;

        for (int i = 0; i < rowCount; i++)
        {
            string filename = grdFiles.Rows[i].Cells[0].Text;
            filepaths.Add(Server.MapPath("~/Uploads/") + filename);
        }

        foreach(string path in filepaths)
        {
            DataTable dt = new DataTable();
            //converts csv into datatable
            dt = GetDataTableFromCsv(path, true);
            //add table to list of tables
            allTables.Add(dt);
        }

        foreach(DataTable datatable in allTables)
        {   
            //Merge each table in the list to the mergedTables datatable
            mergedTables.Merge(datatable);
        }
        csvUploadResults.DataSource = mergedTables;
        csvUploadResults.DataBind();

Thanks in advance for any help :)

Then how do you plan on doing the merging? How do you know how to connect a row from test1 to a row in test2? — Kvam, Nov 05 '15 at 11:41
So you don't want to merge by a column name which both tables share? Just by index? — Tim Schmelter, Nov 05 '15 at 11:52
Yes that sounds like what im trying to do lol however I can add an ID column if it makes things easier (see edit). there is no real requirement as to how it should be done, just as long as it aligns correctly — Conor Bradley, Nov 05 '15 at 12:04

Suprabhat Biswal · Accepted Answer · 2015-11-05T13:06:02.180

3

If your objective is just to merge data without considering the relationship between the two data then you can add two more columns into first datatable and through loop get data from second table and assign them to first datatable columns. The way the data is received will be the way data will be saved in first datatable.

public DataTable MergeData(DataTable dtFirst,DataTable dtSecond)
{
    dtFirst.Columns.Add("LocalAuthority");
    dtFirst.Columns.Add("AverageSpeed");
    for (int i = 0; i < dtFirst.Rows.Count; i++)
    {
        dtFirst.Rows[i]["LocalAuthority"] = dtSecond.Rows[i]["LocalAuthority"];
        dtFirst.Rows[i]["AverageSpeed"] = dtSecond.Rows[i]["AverageSpeed"];
    }
    return dtFirst;
}

Now , you need to pass datatable as parameter in following method.

MergeData(allTables.ElementAt(0), allTables.ElementAt(1));

edited Nov 05 '15 at 13:06

answered Nov 05 '15 at 12:48

Suprabhat Biswal

3,033
1
21
29

Worked perfectly, nice and simple, just what i was looking for. Thanks :) – Conor Bradley Nov 05 '15 at 13:18
very concise explanation. =) +10 – Ian Dec 18 '15 at 07:25
But it should be said, that in a DBMS the rows have no order and relying on a basically random retrieval index is a mistake even if the result may look right. – TaW Jul 06 '18 at 12:59

score 0 · Answer 2 · edited May 23 '17 at 11:44

0

You're going to need a unique key on both datatables and merge them together. You could add the SchoolName to your second datatable and merge the two tables on the postcode. Or more preferably, add an id to both of the datatables and merge the two datatables on the id.

edited May 23 '17 at 11:44

Community

1
1

answered Nov 05 '15 at 11:47

Janneman96

374
1
8
24

I was unaware the two datatables needed a common column to merge correctly. Do you know of any resources which show you how this is done? – Conor Bradley Nov 05 '15 at 11:51
If you could give some information about where the datatables come from, I could search for a solution for you. Did you create the datatables in C# code? Do they come from an SQL select query? I have no idea. – Janneman96 Nov 05 '15 at 11:52
There's another very cheap solution to this problem, you could [add an id to both tables after they are created](http://stackoverflow.com/questions/2839168/how-to-add-identity-column-to-datatable-using-c-sharp) and merge them on that id. – Janneman96 Nov 05 '15 at 11:56
They are created from csv files which are uploaded. the 'GetDataTableFromCsv' method shown in the question then creates the datatable from the csv data – Conor Bradley Nov 05 '15 at 11:56
I have added an ID column to the datasets (See edit). My question now is how to merge based on those id's? – Conor Bradley Nov 05 '15 at 11:59
@robert573: look at my answer on a similar question here: http://stackoverflow.com/questions/12278978/combining-n-datatables-into-a-single-datatable – Tim Schmelter Nov 05 '15 at 12:02
Don't know is this worth to have something like this but you could merge two datatable by creating one other datatable with number of columns equal to the sum of number of columns in first datatable and number of columns of second datatable. Iterate both the datatables and copy the data to the third datatable. – Rojalin Sahoo Nov 05 '15 at 12:05

Combining two c# datatables into one

2 Answers2

Linked