How to achieve format with preceding 10 zeros to any digit in excel?

Help Studio
, ,
1

Hi Experts,

I have an excel file with below format column and when i read it through data table and putting it back to other sheet the format get change.

image

Any solution guys, how can I preserve this format or how can I put the same format from code like CurrentRow(“columnname”).ToString(“D10”).

Thanks

2

Hi @ermanoj3101,

When you read in the excel, you can check the preserve format property. The values will be read as is in UiPath

3

Hi @ermanoj3101

Can you give a try with Read range properties with the preserve format option checked?

Regards

4

Tried not working.

5

I am looking for something similar to this

image
image996×167 23 KB

It’s working to convert into currency format.

6

Hi @jeevith ,

Already tried, not working.

7

working with excel read range / write rang can have some uncontrolled result (interpretating 000…123 String as a number and removes the 0 on start.

  • better results we achieved on using EAS for read /write range
  • if needed we can do some corrections (LINQ, Datacolumn Expressions)
  • reformating Cell format with following activity:
    grafik

in some scenarios forceing Excel with '000110 (the starting ') to keep values as text was accepted by some clients and was working

1 Like
8

Hi @ermanoj3101,

My bad I did not read the last part where you try to write back to another excel file.

The issue here is Auto Format option in the new excel file where you are trying to paste the values. The new file automatically reads the 00000000007 as int and not as string (text). And zeros will be removed.

  1. One alternative way is to ensure the new file does not Auto Format your columnsm, but this can be risky if you have many other columns they all will be saved a text. Undo automatic formatting in Excel - Excel (microsoft.com)

  2. Another way is to copy a predermined excel file which already has a text formatting for all of its cells. This way when UiPath writes the range, the excel file will keep the formating. In this approach, you will have to take a copy of the excel file. TextPreFormattedExcel.xlsx (8.4 KB) In short, treating this file as a template file and writing to copy of this file so that future executions can still use the template.

1 Like
9

Hi @jeevith ,

Thanks for the suggestion.

The problem is i am getting a new file every time on email and i have to start working on it then i have to do some calculation on each columns.
So for that i am reading the whole excel file and putting back data to the same file and their it’s change in format.

I am not sure template is a good idea.
I have almost achieve all formats but only this is remaining.

Thanks for the suggestion, if nothing works i will try this.

10

Thanks @ppr , I am not allowed to use third party packages.

11

@pravin_calvin , @pravin_calvin , @ppr

I have also tried interop code but i get stuck what format should i use for this.

image
image1180×783 173 KB

Anyone have any idea here.

12

10 times 0 for the format string could work

13

Thanks @ppr , the way he described is to create new column with formula = TEXT ( B3, “000000” ) but when i am trying to copy it’s value to original column then again the format get changed.

Anyway i will keep looking for better solution for now i am going with switch condition to add preceding 0 as per the digit count.

Thanks all for Your comments :slightly_smiling_face:

14

Were you looking for .ToString("00000000000") ?

See Custom numeric format strings - .NET | Microsoft Learn

Excel might still want to interpret the value as an integer though. Refer to above solutions (Add ’ in front or change Excel column format)

Happy automating

15

@ermanoj3101 - have you tried pad left?

16

the link article was about different approaches and was mentioning more then one
Approach e.g.

0000000000
leading ’

PadLeft will do
grafik

17

ok, let me check

18

thanks @prasath17 , I will check this.

19

thanks @lukasziebold , I will check and let you know.

20

@ppr , @prasath17

Tried padleft but when i am putting back it to excel the format again get change.
It’s not working.

Thanks for your suggestions.