Excel activity - read cell display value

Feedback Activities
, ,
1

Hi,

It would be nice to add a third activity to the Read Cell current duo (Read Cell and Read Cell Formula) - Read Cell Display Value (it’s available in the original Interop API as cell.Text).

Reasoning:

Excel likes to format values and store them in its internal representation, which might produce “weird” (at least at first glance) results when reading. Being able to read cell display value on columns storing f.e. date would give a WYSIWYG experience would “gotcha” moments in execution.

Example:

Excel with 3 dates. All 3 cells are formatted as Date in the Excel (although some are not recognized as valid dates, because… excel).

Available reading techniques results:

(Type is checked as .GetType for Range, .GetTypeCode for individual cells to check the actual underlying value of the generic value)

From human perspective, all of those are valid dates, but depending on how Excel recognizes the value, it might be returned as a String or a Date. Also, since all dates stored by the robot are by default returned in US format from .ToString(), this can complicate the execution logic even further (and lead to a month/day swap very easily).

Having a Read Cell Display Text eliminates this as a whole and makes “copy-pasting” work that much simpler, especially in situations where the value has to be literally copy-pasted in the exact format it was in originally (f.e. as an in-between parser/translator task).

It could also help, or even enable at all, certain scenarios where a field has a specific format set. As an example - with current activities there is no reliable way to read the currency-formatted field, only the value (without the currency symbol).

Regards,

Andrzej

2 Likes
2

I have a similar problem due to culture format.

The date format in spain is dd/MM/yyyy and Excel Activity convert it to Date, if the day is 12 or less it reads the date as MM/dd/yyyy (swaps month and day) but when is more than 12 it reads it properly :frowning:

I will request UIPath to allow read all as string because i have a lot of problems (not only with dates also with money values)

Edit1: For all of you that would need to work with strings and datetime remember that you can use .tostring(“dd/MM/yy HH:mm:ss”) notation to convert to string in different formats. And also you can use DateTime.parse(dateTimeAsStringInAnyCultureFormat, new system.globalization.CultureInfo(“Es-es”)) to read any different culture dates. de “ES-es” could be changed to other formats just look in then MSDN

Edit2: I have been struggling with this problem again and a collegue recommend me to work with FormatValue activity that mostly resolves all this problems.

2 Likes