Keeping Data Columns by name and removing the others within a DataTable

Question

I have a csv file with 35+ columns, it then gets placed into a data table using my csv reader, I want to find 4 columns by name then remove the rest, the 4 columns could be at random indexes so I want to search for the name of the header.

So far I have this:

DataTable dataTable = new DataTable();
dataTable = CSVReader.CSVInput(filepath);

foreach(DataColumn dataColumn in dataTable.Columns)
{
    if(dataColumn.ColumnName != "Cat" || dataColumn.ColumnName != "Dog" || dc.ColumnName != "Turtle " || dc.ColumnName != "Lion")
    {
        dataTable.Columns.Remove(dataColumn)
    }
}

Cat, Dog, Turtle and lion are the examples of the columns I want to keep

This just removes the first column and displays an error saying the "Collection was modified".

I want to do this so I can then sqlbulkcopy the datatable.

EDIT

creating another data table will run out of memory

Answer 1

You can't change a collection while you are enumerating on it. You can change your code to a standard for loop with a backward indexing like this

for(int x = dataTable.Columns.Count - 1; x >= 0; x--)
{
   DataColumn dc = dataTable.Columns[x];
   if(dc.ColumnName != "Cat" && dc.ColumnName != "Dog" && 
      dc.ColumnName != "Turtle " && dc.ColumnName != "Lion")
   {
       dc.Columns.Remove(dataColumn)
   }
}

The looping in reverse is required to avoid jumping columns when you remove an item from the collection. Also, as explained, in the comment below, you need to use the && logical operator to remove ALL the columns that don't have a name like the four one you want to preserve. Using the || logical operator will remove all of your columns because the column named "Lion" will be removed because its name is not "cat" (or anything else in the if condition).

There is also the possibility to use a DataView to extract only the columns you need, but this has the drawback to require a second datatable in memory and you could encounter problems if your data set is really big.

DataTable datatable = CSVReader.CSVInput(filepath);
DataView dv = new DataView(datatable);
DataTable newTable = dv.ToTable(false, new string[] {"cat", "dog", "turtle", "lion"});