Force excel to recognize numbers as text when pasting from datatable

Help
, ,
1

Does anyone know of a way to force excel to recognize numbers as strings when using the write range activity with UiPath?

I am getting inputs from an excel file and saving it to a datatable variable. In a new workbook, I have a date field going into SAP that requires 8 characters in MMddyyyy format. If the month starts with a 0, excel is automatically removing the leading 0.

This is being done with many files, so manually opening excel and using UI to change the range to “text” format would take too much time/resources.

I believe the datatable coming from the excel input file is automatically converting it to a double or decimal type. Therefore, the only way I can think of is to clone the table, change the column datatype to string, then loop through each row and bring values over to the cloned table. I haven’t actually tested this though, thinking there may be other options. Any ideas?

EDIT: My attempt at changing the datatype to string did not work. Excel is still recognizing it as a number

2

Hi.

Typically, what I do is I format the value correctly before using the value in my process.

Whenever you use an Excel scope it will format the cells by default. What you can do though is if it is a CSV or text file, it will keep the leading 0s, so if you format the Excel file (using Select Range and the Format in the ribbon to change it to your desired format) then Save As to a .csv file it will keep whatever the formatting was when you saved it. Once you have a correct csv file, just use Read CSV or Read Text file to get the correct values. EDIT: also keep in mind if you manually open CSV in Excel, it will format cells, so you need to open it in Notepad.

As far as I know, there really isn’t a good way to force Excel to read those values with the leading zeros. I hope my 2 suggestions help, where you can just format the value as you process it, or Save it to a CSV to preserve the leading zeros.

Regards.

1 Like
3

Ya unfortunately preformatting won’t work in this instance, and it needs to be in excel rather than a simple CSV.

I did find a workaround that isn’t usually workable, but is ok in this specific instance. I simply added a single quote ' before the date in the datatable MyDate.ToString("\'MMddyyyy"), which forces excel to read it as text and SAP ignores the single quote.

4

Hey folks, the most easy way is to have an empty excel with the name temp.xlsx and set the column formats as required in your output excel. Now simply write range your Data Table in it and rename it.
for eg if you want to preserve your first col as text then in temp.xlsx set it as text and when you write the data table to it your format is preserved.

1 Like
5

using Write Range activity from System >> File >> Workbook . You should use the Read Range activity under the System too.

2 Likes