Convert data in columns into having 2 decimal places (possibly using LINQ)

Hi, I want to change the data gathered from a csv file into having 2 decimal places at the end. This is because I have to compare them with an extracted datatable, which holds the same value but with proper 2 decimal places because it is based on the currency format (but I don’t think it is saved as such).

For example:
Extracted Datatable:
45.80
1232.60

CSV Datatable:
45.8
1232.6

The amounts with no cents at the end have no problem though. If there’s no way to exclude whole numbers like those with no cents value at the end, I’d have to do the same with the Extracted Datatable.

I need to do this for two columns, Deposit and Withdrawal. Because they both hold the values that I need to use to compare my datatables with.
Since I have to compare them but the CSV Datatable don’t have 0’s at the end, they are not detected by my RPA. I heard using LINQ is the fastest and easiest method but I’ve looked it up and attempted to do some based on certain posts to no avail. If someone would kindly guide me on how it can be done I would be very grateful. Thank you

1 Like

@beepboop You can use the below expression while comparing the Deposit and Withdrawl columns of CSV datatable with extracted data table

CurrentRow(0).ToString.PadRight(5, CChar("0"))

Please find attached workflow

Example.zip (2.5 KB)

This expression will add 0s at the end. Ref Below screenshot, input and output

1 Like

Can I ask what does the value 5 in Padright does?

Edit: Oh, does this mean that the table still hold the default values?

1 Like

@beepboop

CurrentRow(0).ToString.PadRight(5, CChar("0"))
  • PadRight is the string method that add 0s or spaces or characters etc towards right.
  • 5 is the total length of the input string
  • 0 is the one that adds toward right, CChar is the one that is converting the 0 to char type

Ex: 45.2 - It adds 0 at the end - 45.20
49.27 - It doesn’t add anything here

Use below doc for ref

1 Like

use invoke code activity
set code to this

dt.AsEnumerable().ToList().ForEach(Sub(row) 
If row("Deposit").tostring.contains(".") Then
	row("Deposit")= Convert.ToDouble(row("Deposit")).ToString("N2")
End If
If row("Withdrawal").tostring.contains(".") Then
	row("Withdrawal")= Convert.ToDouble(row("Withdrawal")).ToString("N2")
End If
End Sub
)

then set arguments to this (make sure direction is in/out)

before:
Deposit, Withdrawal
67.5, 1,323.43
44, 12.1

after:
Deposit, Withdrawal
67.50, 1,323.43
44, 12.10

2 Likes

Hi Jack, I got this error?

It says add ‘,’ I’ve tried adding and I still get these errors.

your project is in c# not vbnet?..

1 Like

it’s in vb.net!

1 Like

no its c#

change to vbnet
image

1 Like

Do you think this code adds “,” for amounts that reaches thousands? Asking because this is the output of datatable that I got (I yellowed out the amounts that has “,” added.

image

Whereas before the invoke code, this is the output (ignore the yellowed cells, I did it to bring attention to amounts with no 0s after the decimal place.)

1 Like

change invoke code to this

dt.AsEnumerable().ToList().ForEach(Sub(row) 
If row("Deposit").tostring.contains(".") Then
	row("Deposit")=  Convert.ToDouble(Convert.ToDouble(row("Deposit")).ToString("N2")).ToString("N")
End If
If row("Withdrawal").tostring.contains(".") Then
	row("Withdrawal")=  Convert.ToDouble(Convert.ToDouble(row("Withdrawal")).ToString("N2")).ToString("N")
End If
End Sub
)
2 Likes

Hi, it still have the same problems.

image

you mean theres no comma ?

1 Like

Mean there is a comma to amounts that is in the thousands, it disallows the values from being compared, so it is not detected

whats the issue now??? ??? you want to add comma to csv table or remove it?

2 Likes

Oh was I not clear? Sorry, I meant I want it removed to allow comparison

then just remove the comma like this .

dt.AsEnumerable().ToList().ForEach(Sub(row) 
If row("Deposit").tostring.contains(".") Then
	row("Deposit")= Convert.ToDouble(row("Deposit")).ToString("N2").Replace(",","")
End If
If row("Withdrawal").tostring.contains(".") Then
	row("Withdrawal")= Convert.ToDouble(row("Withdrawal")).ToString("N2").Replace(",","")
End If
End Sub
)
2 Likes

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