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?
Amount.xlsx (10.1 KB)
@indrajit.shah For Converting negative numbers to Positive, you can use Math.Abs(yourNumber)
Ya but how can i utilize it in DT and write the new data in a sheet .
@indrajit.shah Can you follow these Steps :
- Use Read Range Actvity read your Excel File, Get Output Datatable, say DT
- 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))
- 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
I am looking for suggestion other than using loop.
but its good solution.
The Linq can be used to update the column .
Do you mind sharing?
as once the data is updated i have take the data to work further.
You can easily do this using linq
Check this workflow for better understanding
-ve to ve.xaml (6.8 KB)
I think if you only want this to be applied to your excel file, just use the ABS function in that column.
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.
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
My Date column has been messed up
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
See this excel sheet I need to change - to +
And + to - how can we acheive this
For writing query
I try your above query however - value is changed to + but + value is not at all changed
The =SUBSTITUTE(SUBSTITUTE(A1,"-",""),"+","") can be used to substitute the + and - ve value and use the Autofill range to fix the rest of the values.