How to write data to excel sheet as we manually paste with "paste as value" option of pasting

Hi,
I have a requirement where i need to copy data from multiple excel sheets and paste it into another excel , but while pasting have to use “Paste as Value” option. I had used the normal Read Range and Write Range activities but write range activity is not doing a "paste as value " , hence the format of data while pasting is getting changed.

All the columns in the source excel is of “General” Type and all of the source data files comes daily from another source. But while pasting i need to paste it without changing the ‘actual’ expected format of the value in the columns. Say a column named ‘company code’ has value as ‘0282’ , when i read this with Read Range activity the output data table treats it as a number type and removes the starting 0 and the value becomes 282. I cannot change the source files column types as get it daily from another source and there are lot of files, the destination file column format setting is also difficult as there are lot of columns in multiple tabs.

So is there a way in UiPath to do the “Paste as Value” action.
@ovi, @badita, @andrzej.kniola @Gabriel_Tatu @balupad14 @Rammohan91 @vvaidya @Lucas.Pimenta @ClaytonM @arivu96

Hi Bejoy,

I’m thinking you can format the destination Excel file before pasting. So if you pre format it as Text type, your values will be displayed as they are. Could you please give it a try?

Best

Hi.

Here’s the thing… whether you preformat a file to have Text type columns or use UiPath to format it, the value will still be without the leading zero. Like this is an Excel feature, and typically you would need to change the value to have a ’ character infront or use a formula like =“0282”. Using the formula method will change it back if you were to use copy and paste as value.

I can tell you that this has been a challenge for everyone, even outside of RPA with just simply using Excel manually. For example, a business with CSV files will have all the zero-filled values, but as soon as someone opens it in Excel all the leading zeros are gone. Additionally, Excel has a max number of digits it can hold in a cell before it changes the cell to an exponential format (even though the value is still correct).

So, I feel like there are 4 options:

  1. If the user does not require the leading zeros in order to do their job, then don’t sweat about it and just let Excel do what it does, like auto remove leading zeros (there might actually be an Excel setting to turn this off, but maintaining the setting might not be reliable throughout RPA environments). Also, always code your bots to NOT require the leading zeros when processing data, because you can’t always rely on a file being saved in a certain format.

  2. If the user just requires that the format shows the leading zeros (not necessarily the actual value), then either preformat a template file with Text format or use TypeInto with Alt-key combos to access the Cell format features. Then, when you use the Write Range, it will keep the format that you are wanting.

  3. Change the value of each cell to have a single quote ’ character infront of the value with the leading zeros. This is normally the manual solution in business if they want the actual value of the cell to be correct rather than only the format.

  4. Simply use CSV. CSVs are text-based so when you Read CSV, the value with leading zeros and format are maintained, and when you Write CSV the leading zeros and format are maintained. However, it won’t work with Excel Scopes, since it opens the file in Excel therefore removing all the formats automatically. If you open the file in Notepad, you will see that all the values are correct with the leading zeros and format that you wanted (also with Dates). Additionally, you can open a File in Excel (or with Excel Scope), format that file maybe using the TypeInto methods, then Save As to a .CSV file… this will actually convert all the values to whatever it shows in the cell, whereas if it stays as a .xlsx, the value is not necessarily what the formatted cell shows.

Sorry for my long and drawn out reply.

I hope this helps give you some ideas to solve the formatting challenge that everyone struggles with.

Regards.

2 Likes

Thank you @ovi and @ClaytonM for the suggestions and guidance.
So the bottom line is there is no action in UiPath which we can use to perform a “paste as value” action so we need to do the workarounds as either format destination columns before pasting or add a quote character in front of value if we want the value to be treated as text.
CSV is not an option as we are doing the actions in Excel Scope only.
Thanks again, Really Appreciate the support.

@BejoyEdison yeah, that is correct from my understanding.

But, also wanted to mention that “paste as value” is the equivalent of Write Range or Write Cell, cause it places the value (retaining the formatting of the range). If you do want to perform the “paste as value” action, however, you can get to it using the Alt-key combo like: "[k(ralt)]hvv" (ie Alt+hvv)

Regards.