Remove columns from DataTable which are not in List

Question

We have this List<string>

List<string> A = ['a','b','c']

And another Datatable [B] with the following columns

'a' 'b' 'c' 'd' 'e' // the columns of DataTable B

How do we remove all of the columns which are not found in List<string> A from Datatable B?

Intersection of two list. https://stackoverflow.com/a/7188030/2845389 — Kaushik, Sep 23 '19 at 06:27
@Kaushik I couldn't understand from the answer how to achieve this... can you give an example with the list of string and datatable specifically? — , Sep 23 '19 at 06:31

ArcX · Accepted Answer · 2019-09-23T07:19:57.027

1

If you just want to remove all the columns not found in list 'A'

var A = new List<string> { "a", "b", "c" };
var toRemove = dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName).Except(A).ToList();

foreach (var col in toRemove) dt.Columns.Remove(col);

edited Sep 23 '19 at 07:19

answered Sep 23 '19 at 07:04

ArcX

687
8
20

Thanks, I get a "Collection was modified; enumeration operation may not execute" error... – Sep 23 '19 at 07:18
1

@levi Try adding `.ToList()` to copy the value, will update answer – ArcX Sep 23 '19 at 07:19

Dmitry Bychenko · Answer 2 · 2019-09-23T07:39:23.813

I suggest a simple for loop:

  DataTable table = new DataTable();

  table.Columns.Add("a", typeof(string));
  table.Columns.Add("b", typeof(string));
  table.Columns.Add("C", typeof(string));
  table.Columns.Add("d", typeof(string));
  table.Columns.Add("e", typeof(string));
  table.Columns.Add("F", typeof(string));

  var A = new List<string> { "a", "b", "c" };

  // HashSet is a better collection in the context:
  //  1. We can specify comparer (e.g. we can ignore case)
  //  2. It's faster if the collection has many items
  // Sure, you can put A.Contains instead of columnsToKeep.Contains
  HashSet<string> columnsToKeep = 
    new HashSet<string>(A, StringComparer.OrdinalIgnoreCase);

  // For i-th column we should either either keep column (do nothing) or remove it
  for (int i = table.Columns.Count - 1; i >= 0; --i)
    if (!columnsToKeep.Contains(table.Columns[i].ColumnName))
      table.Columns.RemoveAt(i);

Cyrille Con Morales · Answer 3 · 2021-03-24T08:17:42.020

0

You can do something like this

int numCols = dt.Columns.Count;

foreach(String str in A){//loop all strings in arrays

 for(int i=0;i<numCols;i++){//loop all columnames in dataTable B
      string columnName =dt.Columns[i].ColumnName.ToString();//get column Name
      //check if columnNames in B are equal to 
      if(!str.Equals(columnName){
        //remove column
         dt.Columns.RemoveAt(i);
      }else{
       //column name is equal to the string array
      }
   }
}

edited Mar 24 '21 at 08:17

answered Sep 23 '19 at 06:37

Cyrille Con Morales

918
1
6
21

Ive edited my answer, feel free to approach and give the output – Cyrille Con Morales Sep 23 '19 at 07:47
I think it should be string columnName = dt.Columns[i].ColumnName.ToString() – raw_hitt Mar 24 '21 at 07:59

score 0 · Answer 4 · answered Sep 23 '19 at 06:45

You can try below code.

 List<string> A = new List<string>() { "a", "b", "c" };

            DataTable dt = new DataTable();
            dt.Columns.Add("a");
            dt.Columns.Add("b");
            dt.Columns.Add("c");
            dt.Columns.Add("d");
            dt.Columns.Add("e");
            foreach (string col in A)
            {
                dt.Columns.Remove(col);
            }
            foreach (DataColumn column in dt.Columns)
            {
                WriteLine(column.ColumnName);
                WriteLine(" ");
            }

The question is not how to remove the columns in the list (a), it's how to keep only them... — , Sep 23 '19 at 06:48

Remove columns from DataTable which are not in List

4 Answers4