How to Edit DataTable Column Value

Question

Excel column name may contain trailing spaces which would hit exception if the column don't match due to spaces.

I am finding ways to handle trailing spaces in the column name in datatable.

foreach (DataRow row in caseTable.Rows)
{
    foreach (DataColumn column in caseTable.Columns)
    {
        if (!(string.isNullOrEmpty(column.toString())))
        {
            //Cannot assign value to 'column' because it is in a 'foreach iteration variable'
            column = column.ToString().TrimStart().TrimEnd(); 
            trimmed = 1;
        }
    }
    while (trimmed == 0) ;
}

...

//errored out due to 'Excel.firstName' value not existing in DataTable due to trailing spaces
if (row[Excel.firstName].ToString().Trim() != "")
{
    caseEntity.Attributes[Case.firstName] = row[Excel.firstName];
}

You should use proper methods such as `string.IsNullOrEmpty` and `string.IsNullOrWhitespace` — Michał Turczyn, Apr 17 '20 at 15:00

Rubidium 37 · Accepted Answer · 2020-04-17T15:57:59.507

There are two mistakes in your code:

You are iterating a sequence with foreach and trying to modify the iterated items; thus, the exception that is throwing.
You are trying to replace the column object instead of its name.

Additionally, you are abusing ToString and, as already stated by Michal Turczyn, you are not using built-in String methods explicitly designed to test for empty or null strings.

You can try replacing the code inside the inner loop with

var oldName = column.ColumnName;
if (!string.IsNullOrEmpty(oldName))
{
    var newName = oldName.Trim();
    if (newName != oldName)
    {
        column.ColumnName = newName;
        trimmed = 1;
    }
}

From this replacement code You can see that:

You should read/write ColumnName property instead of using ToString.
You can avoid invoking TrimStart and TrimEnd one after another (in any order) by using Trim.
You should use string.IsNullOrEmpty instead of checking for null andempty string.
By declaring oldName and newName, you can track a column name as trimmed only when that's really true.

More over, if you are assigning only values 0 and 1 to trimmed variable, then you should consider declaring it as bool and assign false or true. Otherwise you can keep trimmed variable as a number, but increasing its value rather than assigning always the same constant (1).

If what you are trying to change is not the column name, but is the cell value in the current row for such column, then you are missing to get (and later set) the cell value (you are evaluating and trying to change only the column name). In that case, the inner loop code become:

var oldValue = row[column] as string;
if (!string.IsNullOrEmpty(oldValue))
{
    var newValue = oldValue.Trim();
    if (newValue != oldValue)
    {
        row[column] = newValue;
        trimmed = 1;
    }
}

How to Edit DataTable Column Value

1 Answers1