Copy specific columns from one DataTable to another

Question

Have some read in data (from excel file) in a DataTable and now I want to filter this and copy only specific columns to the other one!

dataTable format:

some data 
ColA|ColB|ColC
xxxx|xxxx|xxxx
some data

some data represents other table data not related to ColA-ColC

How can I copy ColA-ColC with xxxx to the new DataTable?

Thx

score 50 · Answer 1 · answered Aug 23 '13 at 12:06

50

You can simply do it by using DataView.ToTable() :

System.Data.DataView view = new System.Data.DataView(yourOriginalTable);
System.Data.DataTable selected = 
        view.ToTable("Selected", false, "col1", "col2", "col6", "col7", "col3");

answered Aug 23 '13 at 12:06

Mohammad Arshad Alam

9,694
6
38
61

System.ArgumentException: Column 'xxx' does not belong to underlying table – leon22 Aug 23 '13 at 12:11
2

It means `xxx` column is not present in your `DataTable`. – Mohammad Arshad Alam Aug 23 '13 at 12:12
I think the problem is that I have this other data (some data) in header of DataTable and the desired columns are below. Column xxx is definitely in table! – leon22 Aug 23 '13 at 12:16
i think this one is the good answer out of these,but unfortunately the guy ticked other.Any way nice approach Arshad – ranjenanil Dec 04 '13 at 06:31
this does not work for me and I don't know how to fix the problem – user1298925 Dec 03 '14 at 15:05

Giannis Paraskevopoulos · Accepted Answer · 2013-08-23T12:08:40.570

31

Copy the whole table and remove the columns you don't want.

DataTable copyDataTable;
copyDataTable = table.Copy();
copyDataTable.Columns.Remove("ColB");

or

int columnIndex = 1;//this will remove the second column
DataTable copyDataTable;
copyDataTable = table.Copy();
copyDataTable.Columns.RemoveAt(columnIndex);

edited Aug 23 '13 at 12:08

answered Aug 23 '13 at 12:03

Giannis Paraskevopoulos

18,261
1
49
69

2

I used this to copy the columns only by adding a ".clear()" statement after doing the copy(). – Enrico Nov 21 '14 at 10:39
What if we have a large set of columns. It would be a hassle to remove them one by one. Isn't there a way to just copy the column we need? – Rami Zebian Aug 09 '19 at 07:30
1

@RamiZebian you should probably check out the following answer https://stackoverflow.com/a/18402444/2401981 – Giannis Paraskevopoulos Aug 09 '19 at 09:53
A gotcha is that calculated columns need to be removed after the columns that the calculation uses. – jo0ls Apr 02 '20 at 17:42
Using remove or removeAt is suitable when you have few columns but not when you have many columns and you only want a few. So how to do the opposite, I mean, to only select a few columns you want? – Willy Apr 22 '20 at 09:01
@Ralph maybe you want to check Arshad's answer https://stackoverflow.com/a/18402444/2401981 – Giannis Paraskevopoulos Apr 23 '20 at 08:12

score 3 · Answer 3 · edited May 23 '17 at 12:34

3

please check this

  foreach (DataRow dr in dataTable1.Rows) {
    if (/* some condition */)
        dataTable2.Rows.Add(dr.ItemArray);
     }

The above example assumes that both table have the same number, type and order of columns.

here is the actual link

edited May 23 '17 at 12:34

Community

1
1

answered Aug 23 '13 at 12:08

Vijay Singh Rana

1,060
14
32

score 2 · Answer 4 · edited Oct 31 '13 at 20:23

Define your copy DataTable with only the interest columns. You can loop on columns of source row and set the value to the target row with this sample code :

public void IntegrateRow(DataRow p_RowCible, DataRow p_RowSource)
        {
            try
            {
                foreach (DataColumn v_Column in p_RowCible.Table.Columns)
                {
                    string ColumnName = v_Column.ColumnName;
                    if (p_RowSource.Table.Columns.Contains(ColumnName))
                    {
                        p_RowCible[ColumnName] = p_RowSource[ColumnName];
                    }
                }
            }
            catch (Exception e)
            {
...

score 1 · Answer 5 · answered Sep 04 '18 at 07:28

It can be achieved using LINQ

Assume we have two DataTable. 1. dtSource and 2. dtDestination.

if dtDestination has no rows then use below code to generate blank rows.

dtSource.AsEnumerable().All(row => { dtDestination.Rows.Add(); return true; });

Below code will copy particular DataColumn data to DataColumn of another DataTable. Assume both Tables have same number of rows.

int rowIdx = 0;
dtDestination.AsEnumerable().All(row => { row["colName"] = dtSource.Rows[rowIdx++]["colName"]; return true; });

C.Poh · Answer 6 · 2015-12-01T07:00:16.307

This method receives a data table (TextFileTable) as a parameter and proceeds to copy the selected contents of TextFileTable into tblFormat table.In the add row statement the number of columns, must match the number of columns in the target ted table,even though the the two table can be of different sizes.

public DataTable CopyTable (DataTable TextFileTable)
        {
            DataTable tblFormat = new DataTable();

                tblFormat.Columns.Add("ColumnA");
                tblFormat.Columns.Add("ColumnB");
                tblFormat.Columns.Add("ColumnC");
                tblFormat.Columns.Add("ColumnD");
                tblFormat.Columns.Add("ColumnE");

                for (int i = 0; i < TextFileTable.Rows.Count; i++)
                {

                    tblFormat.Rows.Add(new string[] { TextFileTable.Rows[i][0].ToString(), TextFileTable.Rows[i][1].ToString(),
                    TextFileTable.Rows[i][2].ToString(), TextFileTable.Rows[i][8].ToString(), TextFileTable.Rows[i][9].ToString() });

                }


            return tblFormat;
        }

score 0 · Answer 7 · answered Jun 19 '20 at 05:54

0

Same fix in vb.net

Dim view As DataView = New DataView(TB) Dim selected As DataTable = view.ToTable("Selected", False, "id", "col1", "col2")

answered Jun 19 '20 at 05:54

Chevelle

248
5
13

Copy specific columns from one DataTable to another

7 Answers7

Linked