How to update\replace the data in data table for particular column

Hi Team ,

I have a data in data table (Dt).
Need to replace the particular column data (Total order quantity).
1,325.00 ----Value in Excel
1.325.00-----required .

Can some one please help on this .

Regards ,


Hi @Thumu_Suresh

Please try below steps,

  1. Read the excel sheet data in the datatable variable name dt.
  2. Drag and drop the invoke code activity.
  3. In edit arguments, create a variable dt, set direction as in/out and pass value as dt.
  4. In edit code type the following,

dt.asenumerable.tolist.foreach(sub(row) row(“Total order quantity”)= row(“Total order quantity”).tostring.replace(“,”,“.”))

Write the datatable back into the excel using write range activity.

We have to use this inside invoke code as it does not produce a output.

Hope this helps.


HI @Thumu_Suresh

You can try with Find and Replace activity



Find And Replace.xaml (10.6 KB)


1 Like

Another way @Thumu_Suresh

You can try with BalaReva Find And Replace activity


Please find the Linq Query (2.7 KB)

Please provide as solution if your condition is been met

Hello @Thumu_Suresh , Try this method.

  1. Read Excel File and store in a variable , For Example InputDT.

  2. Using for each data table, Loop the proccess.

  3. Use assign activity
    CurrentRow("Total order quantity")=System.Text.RegularExpressions.Regex.Replace(CurrentRow("Total order quantity").to string,",",".")

  4. Finally write the data in another sheet or same sheets.

  5. Now data will change in certain column.

Happy Automation

Gokul Jai

Hi @Thumu_Suresh ,

kindly find below mothered.

read Excel (dtTest)
create new dt (Result)
and user below execration

(From row In dtTest
Select Result.Rows.Add({row(“No”).tostring,row(“Name”).tostring.replace(“a”,“”),row(“E_Code”).tostring,row(“Proj”).tostring,row(“Dec”).tostring,row(“Status”).tostring,row(“Count”).tostring})).copytodatatable


Hi Jayakumar,
While reading the excel in data table can’t see the data with “,” but in same position i have to replace with “.”.

But in excel data it has “,”.

Need help on this.

Regards ,
Suresh .T

Did you read the file using Workbook activity? then try using excel application scope and excel read range and enable the preserve format option in the read range property panel

Hi Fasil ,
I tried with your approach while using excel scope and excel read range in data table it is showing has below .
Data is in double code while converting into CSV file or edit with notepad it is show in double code but we don’t need that double code “” .

we need remove the “,” and replace with “.” and “.” should be replaced with “,”.
EX:-2,686.000 ---->2.686,000

I have used below code it was working for only one column .
How we can use it for multiple column .

can you please help on that fasil.

dt.asenumerable.tolist.foreach(sub(row) row(“Total order quantity”)= row(“Total order quantity”).tostring.replace(“,”,“.”))