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.
Any solution guys, how can I preserve this format or how can I put the same format from code like CurrentRow(“columnname”).ToString(“D10”).
When you read in the excel, you can check the preserve format property. The values will be read as is in UiPath
Can you give a try with Read range properties with the preserve format option checked?
I am looking for something similar to this
It’s working to convert into currency format.
Hi @jeevith ,
Already tried, not working.
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:
in some scenarios forceing Excel with '000110 (the starting ') to keep values as text was accepted by some clients and was working
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.
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)
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.
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.
Thanks @ppr , I am not allowed to use third party packages.
@pravin_calvin , @pravin_calvin , @ppr
I have also tried interop code but i get stuck what format should i use for this.
Anyone have any idea here.
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
Were you looking for
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)
@ermanoj3101 - have you tried pad left?
the link article was about different approaches and was mentioning more then one
PadLeft will do
thanks @prasath17 , I will check this.
thanks @lukasziebold , I will check and let you know.
@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.