Replace one value with another in a specific column

Help Community
,
1

image
Hi All,

I tried most of expressions

(From r In dtTempFilter.AsEnumerable Select ia = r(0).ToString.trim.Replace(" Total",“”) )
The above one gives an array of strings, I donno how to convert to datatable

(From r In dtFilterTable.AsEnumerable Select ia = r.ItemArray.toList Select ic = ia.ConvertAll(Function (e) e.ToString.Trim.Replace(" Total",“”)).toArray() Select dtResult.Rows.Add(ic)).CopyToDataTable()
The above one changes all ‘Total’ values into “” including last column.

Please help me with the suitable one.
Please refer the image.

Thanks and Regards,
Sunil M.

2

we would recommend to loop over the data table e.g

  • for each row acitvity - row is the loop variable for the row
    • Assign Activity: row(“Col1”) = row(“Col1”).toString.Replace(" Total",“”)

for learning purpose A LINQ within the datatable reconstruction approach would look like:

dtResult = dtData.Clone

dtResult =

(From d in dtData.AsEnumerable
let c1 = d(0).toString.Replace(" Total",""))
Let ra = d.ItemArray.Skip(1).Prepend(c1).toArray
Select r = dt.Result.Rows.Add(ra)).CopyToDataTable
1 Like
3

Hi @Sunil_Kumar_Marri1

How about this expression

(From d In DtBuild.AsEnumerable()
Let ra = d.ItemArray.Select(Function (x) x.toString.Replace("Total","")).toArray
Select rc = DtOut.Rows.Add(ra)).CopyToDataTable

Regards
Gokul

4

For learning purpose and explore the different options to update a column value have a look here:

And fo LINQ here:

this statement is replacing in all columns as it is driven by the itemArray and is similar to

(From d In DtBuild.AsEnumerable()
Let ra = d.ItemArray.Select(Function (x) x.toString.Replace("Total","")).toArray
Select rc = DtOut.Rows.Add(ra)).CopyToDataTable

As applied here:

5

Check out the XAML file

Find&Replace.xaml (11.3 KB)

image
image527×502 15.6 KB

Output

image

Regards
Gokul

6

Another Solution Using Studio X activities

Before that Make sure you must enable the Modern activities

image
image306×573 16.6 KB

Check out the XAML file

Find And Replace.xaml (11.0 KB)

image

Regards
Gokul

7

Hi @Sunil_Kumar_Marri1
please follow these steps.
1- read data through read range and stored in DT1
2- Use for each over datatable
3-Use * Assign Activity: row(“Col1”) = Spilt(ow(“Col1”).toString,“ ”)(0)

8

Thank you so much…)

9

Thank you so much sir…)

10

It is worked on first column, but what if that column in middle and one more thing

(From d in dtData.AsEnumerable
let c1 = d(0).toString.Replace(“”,“Mapping Not Found”))
Let ra = d.ItemArray.Skip(1).Prepend(c1).toArray
Select r = dt.Result.Rows.Add(ra)).CopyToDataTable

I’m doing this for empty row replace with “Mapping Not Found”,but gets an error.
It would be helpful for my project.
Please help me with this.

Thanks in advance…)

11

@Sunil_Kumar_Marri1 Then instead of d(0) you can put other column number

12

Let ra = d.ItemArray.Skip(1).Prepend(c1).toArray

Here, we skip the first column and prepend our manipulated data, I asked this.
Please help if u know about it.
And
Check this too…

(From d in dtData.AsEnumerable
let c1 = d(0).toString.Replace(“”,“Mapping Not Found”))
Let ra = d.ItemArray.Skip(1).Prepend(c1).toArray
Select r = dt.Result.Rows.Add(ra)).CopyToDataTable
13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.