Using this data, I have to take some actions and am (please) looking for advice on the most efficient way of doing this
- Under the column ‘Account Number’ I need to make up each account number to 10 digits by adding 0’s in front of the number. So for ‘4544’ I would need to add six zeros for ‘12’ I would need to add eight zeros.
I thought I could use a while loop where the length < 10 and add zeros. However I have seen this C# command - public string PadLeft (int totalWidth, char paddingChar);
Could I use this? how would I use code to ensure that each number is made up to ten digits?
- Under Payment Account, I need to add a decimal place before the last 2 digits e.g 200.02 and 100.02 etc and I also need to add a £ at the beginning e,g £200.02 and £100.02. What would be the quickest way of doing this?
@Palaniyappan you are always super hopeful, I hope you don’t mind me asking tagging you
use for each row acitivity then inside
CurrentRow("Account Number") = "'"+CurrentRow("Account Number").toString.PadLeft(10, "0"c)
CurrentRow("Payment Amount") = "£" + (CInt(CurrentRow("Payment Amount")) / 100).ToString
Let’s say input datatable is dtInput. And then use For Each Row activity to iterate one by one row.
For Each row in dtInput
row("Payment Amount") = "£"+row("Payment Amount").ToString.Trim.Insert(row("Payment Amount").ToString.Trim.length-2,".")
row("Account Number") = "'"+Row("Account Number").toString.PadLeft(10, "0"c)
And then use Write Range activity and pass above dtInput to write the data back into the excel file.
Hey @lakshman looks perfect!
I do have one more cheeky question (please)
Also, for the Payment amount figure, I need to add comma’s in to split up in to thousands after the decimal place has been added
I mean (for example)
100.00 remains as 100.00
1000.00 needs to become 1,000.00
10000.00 needs to become 10,000.00
100000.00 needs to become 100,000.00
Is there a way to do this with similar code?
Hey @jack.chan makes perfect sense
Thanks for your helpful reply
reformat cell after writing to excel. We can use Balareva / newer UiPath activities
as given like above or using some of the approaches as described here:
How to Update Data Column Values of a Data Table | Community Blog
Yes, its possible.
row("Payment Amount") = "£"+CDbl(row("Payment Amount").ToString.Trim).ToString("N2")
Thanks for your help. Using suggestions here and speaking to devs I use this code
(sample data table)
For each row
row in Dtdata
row(“AccountNumber”) row(“AccountNumber”).ToString.PadLeft(10, "0"c)
Thanks for the help,
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.