How to keep text format in read range activity

Help
1

Hi I am new, when I use read range to read a data table and write range to anther excel files, it changes the text format into general format.

For example, in the original one it is “0123321”, after I read and write to a new workbook, it becomes “123321”. Therefore I need to add a 0 before the number. Because I have another case like “0032121” and it will become “32121”. In this case I need to add two zero before the number.

Could anyone help?

Many thanks.

2

@raymondhui
Just use following

YourRowString.padleft(7,‘0’)

3

Make sure the ‘PreserveFormat’ property is clicked in your read range activity. This will preserve it so the text value “0123321” will remain text, instead of converting to general, which is then converted to a number in excel (thus removing the leading 0’s)

4

I try that before but it doesn’t work, seems it only keep currency format but not text format

5

One thing I’ve noticed is that Excel write range and workbook write range handle the use of Excel formats differently. One keeps it text form (I think workbook write range) while the other pastes it in the general format. Try using whichever one your worklow is not using to see if that fixes it

6

Thx mate and I think it should be workbook write range coz I am using excel one. I will try it later

7

Sorry mate it doesn’t work

8

Hi it works after I add cchar in it.
However I would like to add 2 char because in the excel it won’t display 0 so I have to add ‘ before the integer to change it to text format. However I can’t add ‘0 for following
Cell = cell.tostring.padleft(7, Cchar(“‘0”))
The result becomes ‘’12345.

Thanks.

1 Like
9

@raymondhui
great…

10

Sorry mate would u please help
@rahatadi

11

hi @raymondhui

just add ’ in front of string
'01234
regards,
Aditya

12

But it won’t show ‘01234
It shows up ‘’1234 which is missing 0

13

@raymondhui

image
image586×557 19.5 KB

14

Yes that’s what I want man would u like to show me your assign activity?
Do u need to add 0 before following?
Cell = cell.tostring.padleft(7,cchar(“‘“))?

15

image
image3817×1111 2.65 MB

16

Hi. You did that wrong. PadLeft will fill in all characters up to 7 characters, so it is putting single quote to make it ''28784 instead of '0028784

You want to zero fill it with the “0” character, then just place single quote at the start.

Cell = "'"+cell.tostring.padleft(7,cchar(“0“))
3 Likes
17

Also, my 2 cents on this… if you are dealing with CSV files, they are text-based, so they have all their values already in the zero-filled format that you are looking for. However, if you open the CSV file in Excel, Excel will by Default change the format to a number rather than text.

So, you can use Write CSV instead of Write Range, if that’s an approach you would like to take.
(open the CSV file in Notepad, and you will see how the format is correct)
Note: if you do Write Range to Excel and lose format, you can have UiPath format the columns, then use SaveAs to save to CSV and it will keep the zero-filling when it creates the CSV file.

I normally try to avoid using workarounds like adding the single quote, which also require you to change every value that you have in your table. The other option is to format the Excel file with 0000000 formatting for a visual but the value won’t have the zeros, so that option may not be what you want either.

So, I guess it depends on what the end goal really is, and using CSVs are usually better for keeping zero-filled numbers and date formats intact.

Regards.

3 Likes
18

@raymondhuiMain.xaml (7.8 KB)
Book1.xlsx (8.6 KB)

refer this…!

19

thanks @ClaytonM

this would be great practice to use csv.

20

Thx for your advice and works finally it works fine!! Yes coz I have other works on the worksheet therefore I choose to use write range. Btw such a simple work but I miss the simple steps, thank you very much!