topic RE: A Number formatted as using a 'dot' for decimal point (XXX.XX) on Excel is INTERMITENTLY read with comma for decimal point (XXX,XX) by Blue Prism. in Product Forum

A Number formatted as using a 'dot' for decimal point (XXX.XX) on Excel is INTERMITENTLY read with comma for decimal point (XXX,XX) by Blue Prism.

AlfredLefosa — Thu, 21 Jul 2022 09:52:00 GMT

Hi Guys,

We have recently encountered the following issue in on Bot running in Production;
A number formatted as XXX.XX (e.g. 913.24) is intermittently read by the Bot as XXX,XX (e.g. 913,24) - i.e. comma replaces a dot as decimal point indicator.
The number in question is read from MS Excel document using Get Cell Value action of MS Excel VBO.

The strange thing is, not only is the occurrence of the issue intermittent, it also never occurs on consecutive bot runs (Bot runs daily).
The template that the bot reads the values from does not change, so from excel point of view the formatting is always the same.

The problem with formatting 913.24 as 913,24 is that when such a value is cast to a number, it becomes 91324 - which leads to incorrect computation further down the process.

NB: Blue Prism Version = v6.8.1

------------------------------
Alfred Lefosa
------------------------------

RE: A Number formatted as using a 'dot' for decimal point (XXX.XX) on Excel is INTERMITENTLY read with comma for decimal point (XXX,XX) by Blue Prism.

RonanConsidine — Thu, 21 Jul 2022 10:29:00 GMT

Hi Alfred,

What format is the excel data in? If it is in xls or xlsx format it may be down to the format of the data in particular cells.

Another check to do would be to see if the column you are bringing the data into is set to number or text, if your collection does not have defined fields this may assist.

One way around this would be to check if this error occurs if you change the file to a .csv and use the file management action "get CSV text as Collection"

Hope this helps,

------------------------------
Ronan Considine
Senior Business Analyst
Blue Prism
------------------------------

RE: A Number formatted as using a 'dot' for decimal point (XXX.XX) on Excel is INTERMITENTLY read with comma for decimal point (XXX,XX) by Blue Prism.

AlfredLefosa — Thu, 21 Jul 2022 12:29:00 GMT

Hi Ronan,

Thank you for your response. See below my feedback.

Q: What format is the excel data in? If it is in xls or xlsx format it may be down to the format of the data in particular cells.

Ans: The Ms Excel document is of type '.xlsx'. Please see image below the Cell Formatting.

Q: Another check to do would be to see if the column you are bringing the data into is set to number or text, if your collection does not have defined fields this may assist.

Ans: The data is read as text (using Get Cell Value) and cast to Number data item. The same problem is observed where a Get Worksheet as Collection is used which. So the problem (on the day it surfaces) it seems to affect all actions that read data from this excel file.

------------------------------
Alfred Lefosa
------------------------------

RE: A Number formatted as using a 'dot' for decimal point (XXX.XX) on Excel is INTERMITENTLY read with comma for decimal point (XXX,XX) by Blue Prism.

AndrewPascal — Wed, 27 Jul 2022 11:54:00 GMT

Hi Alfred,

It sounds very much like regional/language settings. If the cell contains a number, then Excel will present it as "913,24" if the localization settings are European, but "913.24" for UK or the rest of the world. Is it possible that you have multiple bots or multiple machines which might have different localization settings in Windows? Or that the localization settings are not being applied consistently? This is usually handled by a group policy, but in multinational organizations it gets complicated and can sometimes go wrong...

------------------------------
Andrew Pascal
------------------------------

RE: A Number formatted as using a 'dot' for decimal point (XXX.XX) on Excel is INTERMITENTLY read with comma for decimal point (XXX,XX) by Blue Prism.

RonanConsidine — Thu, 28 Jul 2022 14:48:00 GMT

Hi Alfred,

The format should always be Number and the top choice as shown here

Andrew has pointed out it may be down to regional difference using a ',' or '.' to separate before decimals.

When you use the action get worksheet as collection are you mapping to a pre-configured collection with the data type set to Number or to a blank collection?

I would first ensure the whole column for the data is set to Number as above and that should ensure more standardised data flowing into BluePrism.

------------------------------
Ronan Considine
Senior Business Analyst
Blue Prism
------------------------------

RE: A Number formatted as using a 'dot' for decimal point (XXX.XX) on Excel is INTERMITENTLY read with comma for decimal point (XXX,XX) by Blue Prism.

ShwetaSingh — Thu, 18 May 2023 02:22:00 GMT

Hi Ronan,
I have a similar requirement where I need to format the column as:
Category as number, Decimal places as 0, Selecting "use 1000 separator" and in the negative numbers I have to select : -1,234

Could you please help me how can I achieve this? I need to select the entire pivot table and then do this formatting.

Thanks.

------------------------------
Shweta Singh
System Engineer
Xavient Digital
UTC
------------------------------