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