Adding zeros to make up to 10 digits in a Datatable

Help Activities
, ,
1

Hi all

image

Using this data, I have to take some actions and am (please) looking for advice on the most efficient way of doing this

  1. 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?

  1. 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 :slight_smile:

Thanks all
Jordan

2

use for each row acitivity then inside
do assign

CurrentRow("Account Number") = "'"+CurrentRow("Account Number").toString.PadLeft(10, "0"c)

  1. CurrentRow("Payment Amount") = "£" + (CInt(CurrentRow("Payment Amount")) / 100).ToString

image

1 Like
3

@jordrowley

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.

2 Likes
4

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?

Thanks
Jordan

5

Hey @jack.chan makes perfect sense

Thanks for your helpful reply

1 Like
6

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

7

grafik

8

@jordrowley

Yes, its possible.

                    row("Payment Amount") = "£"+CDbl(row("Payment Amount").ToString.Trim).ToString("N2")
9

Hey all

Thanks for your help. Using suggestions here and speaking to devs I use this code

(sample data table)

image

For each row

row in Dtdata

Assign
row(“AccountNumber”) row(“AccountNumber”).ToString.PadLeft(10, "0"c)
Assign
row(“PaymentAmount”) (cdbl(row(“PaymentAmount”))/100).ToString(“C2”,CultureInfo.CreateSpecificCulture(“en-IE”))

Thanks for the help,

Jordan

1 Like
10

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