Search code examples
c#excelforeachdatatable

Remove space in column names from DataTable

I have a DataTable which I populate from an excel sheet. The column names in the excel contain space between the two words i.e.

  1. PRODUCT TYPE
  2. SERIAL NUMBER

And I'm trying to achieve

  1. PRODUCTTYPE or PRODUCT_TYPE
  2. SERIALNUMBER or SERIAL_NUMBER

The DataTable columns are dynamically generated from the excel. I'm trying to remove the space between the words but I can't seem to get it to work. My current code for removing the white spaces looks like 

foreach (DataRow dr in cloned.Rows)
{
    foreach (DataColumn col in cloned.Columns)
    {
        dr[col] = dr[col].ToString().Replace(" ", "");
    }
}

can someone tell me where I'm going wrong please.

Solution

  • You don't need to loop the rows if you want to modify the columns:

    foreach(DataColumn c in cloned.Columns)
    c.ColumnName = String.Join("_", c.ColumnName.Split());

    Use String.Join("", c.ColumnName.Split()) if you want to remove the spaces.