Hi All,
I have struggled with a function to replace values in a DataTable and after 3 days of searching I have discovered two which I like to share with the community.
- To replace all values which meet a specific criteria in a DataTable on all columns I have used the following:
(From r In InitialDT.AsEnumerable
Select ia = r.ItemArray.ToList
Select ic = ia.ConvertAll(Function (e) system.Text.RegularExpressions.Regex.Replace(e.ToString.Trim, “^$”, “0”)).ToArray
Select ResultDT.Rows.Add(ic)).CopyToDataTable()
Where:
InitialDT is the DataTable where you have not altered data
ResultDT is the DataTable where you would like to have the result after replacement
The “^$” in the replace is a Regex expression to identify String.Empty. You can replace this with another regex expression you would like.
I used this regex replace instead of String.Replace because the String.Replace does not accept an empty string as input so in my case I wanted to replace all the empty spaces with something else.
The expression can be used in an assign activity like
ResultDT=Expression
- To replace all the values in a specific column I used the following expression
InitialDT.AsEnumerable.Where(Function (c) c(“<Name of the column>”).Equals(“”)).Select(Function(c)
c(“<Name of the column>”)=“0”
Return c
End Function).ToList()
Unfortunately this expression can’t be used in an assign and needs to be used in an Invoke code
The InitialDT is an IN/OUT argument of the invoke code activity and it will replace the table provided as input.
My intent was also to replace an empty space with 0 in this case
I hope this will help many of you working with DataTable in the future.
