Convert negative string to positive in Excel without using Loops

Hi,

In my working sheet, the column name is “Amount” and it holds number in positive as well as negative.

-I am trying to convert all the -ve numbers in that column to positive as I have to compare those data with another data.
-I am reading the full excel sheet and storing it in a DT, so how can I convert the value?

image

Amount.xlsx (10.1 KB)

@indrajit.shah For Converting negative numbers to Positive, you can use Math.Abs(yourNumber)

@supermanPunch
Ya but how can i utilize it in DT and write the new data in a sheet .

@indrajit.shah Can you follow these Steps :

  1. Use Read Range Actvity read your Excel File, Get Output Datatable, say DT
  2. Use For Each Row Activity For that DT
    Inside For Each use An Assign Activity and Assign row(“Amount”) = Math.Abs(Convert.ToDouble(row(“Amount”).ToString))
  3. Use Write Range Activity and Write the DT back to the Same Excel File

For Clarification Purpose, I recommend to use a Different Excel File as Output

3 Likes

@supermanPunch
I am looking for suggestion other than using loop.
but its good solution.

1 Like

The Linq can be used to update the column .

1 Like

@Priyanka_Ramesh
Do you mind sharing?
as once the data is updated i have take the data to work further.

How to drag a formula in the entire column in excel
Can you try this. Even in Linq the For loop has to be used .

1 Like

Hello @indrajit.shah

You can easily do this using linq

Check this workflow for better understanding
-ve to ve.xaml (6.8 KB)

3 Likes

I think if you only want this to be applied to your excel file, just use the ABS function in that column.

2 Likes

@vickydas
cool, I didn’t tried linq in UiPath.
i have a question too.

-The output of the “Output Data Table” Activity is string and so i can’t able to write the output in new excel.

hello @indrajit.shah

Output Datatable activity is used to convert the datatable to String
Why are you using Output Datatable activity if you don’t want it in string

Delete the Output Datatable activity and use write range activity with the Datatable type variable as Input

1 Like

Thankyou.

How stupid of me :sweat_smile:

@vickydas
My Date column has been messed up

Hello @indrajit.shah
this is happening because of your excel sheets as the cells are formatted in Date so you get the date with time when you write it using Write Range Activity

Replace the String.Join(Query) in the code with this

String.Join("|",DT.Columns.Cast(Of DataColumn).Select(Function(col) If(CStr(col.columnname)="Amount",x,If(CStr(col.columnname)="Date",CDate(roww.Item("Date")).ToShortDateString,roww.Item(col.columnname))))).Split("|"c)

and run your code let me know about it

1 Like