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

[Thumu_Suresh]

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 ,
Suresh.

Example:-

[muhamed.fasil]

@Thumu_Suresh

lumn data

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.

Thanks

[Gokul001 (Gokul Balaji)]

HI @Thumu_Suresh

You can try with Find and Replace activity

Reference:

Find And Replace.xaml (10.6 KB)

Regards
Gokul

[Gokul001 (Gokul Balaji)]

Another way @Thumu_Suresh

You can try with BalaReva Find And Replace activity

Uipath Excel Find | Search | Find and Replace (BalaReva)

Find and replace didn't replace(Balareva)

I’m trying to use balareva find and replace excel activity but it didn’t replace cell value is there something wrong in my workflow? thanks. Find and replace test.xaml (5.9 KB) Test.xlsx (8.7 KB)

Regards
Gokul

[muhamed.fasil]

@muhamed.fasil

Total order quantity

Please find the Linq Query
ReplaceColumn.zip (2.7 KB)

Please provide as solution if your condition is been met

[Gokul_Jayakumar (Gokul Jai)]

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

Regards,
Gokul Jai

[RajKumarDuraiFAS (RajKumar Durai)]

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

thanks,
RajKumar

[Thumu_Suresh]

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.

@RajKumarDuraiFAS
Regards ,
Suresh .T

[muhamed.fasil]

@Thumu_Suresh

me position i have to replace with “.”.

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

[Thumu_Suresh]

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 “” .
“2,686.000”

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(“,”,“.”))

@RajKumarDuraiFAS

Thanks,
Suresh.T