Replace values in a DataTable without ForEach (loop)

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.

  1. 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

  1. 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.

2 Likes

@CristianZachiteanu
thanks for sharing it with the community
I did also updated the post with the simplified version:

2 Likes

Neither of the version posted are working with Empty Space so this is why I searched for an alternative solution.

" " is not “”

However your solution was an inspiration for seeking alternatives. :slight_smile: