Change Default Format Number

Help
, , ,
#1

Hello,

I have to copy a large matrix from a csv file to an excel file. The csv has the “,” as decimal separator and doesn’t have any group separator.
My xaml has two activity:

  • Read csv
  • Write range.

When I launch my process, the output excel file has a wrong number format, for example:
csv number --> 3,6000
excel number --> 36000.

I changed the decimal and group separator from my Control Panel, but I had the same result. “It seems that UiPath doesn’t consider this modify”.

Does anyone have any suggestions?

PS: I have a large matrix and I can’t format number by number (for reason of time).

Thankyou
Roberto

#2

Hi,

It seems it’s locale matter and is very complicated sometimes.
I suppose there are some workarounds.

First workaround will be the following.

  1. Change OS or EXCEL setting for decimal separator to “,” in advance.
  2. Use Start Process Activity and directly set your csv filename to FileName property.
  3. Then you can see the value as decimal in your EXCEL.
  4. Save this book as xlsx
  5. Finally you can handle it using Read Range Activity.

Second Workaround is the way to replce “,” to “.” in csv.

  1. Read whole csv as string using Read Text File Activity.
  2. Replace “,” to “.” using String.Replace method or System.Text.RegularExpressions.Regex.Replace method.
  3. In general, these method process fast.
  4. Write this string to text file using Write Text File Activity.

Regards,

#3

Hi,

I try the second workaround. I have to use the regular expression substitution because I have to replace only commas in number (in my csv are present some customerID and description with commas, so I can’t include these in the substitution).
How can I create a regex to perform it?

For example:
my starting string --> customerID;here I have the customer description with some commas,;3,6000;3,6000;3,5900
desired output string --> customerID;here I have the customer description with some commas,;3.6000;3.6000;3.5900

Thankyou
Roberto

#4

Hi,

You can get it using Replace activity with the following settings.
Pattern: "(?<=;\d+),(?=\d+)"
Replacement: "."

If your value is fixed length like n.nnnn, the Pattern property might be "(?<=;\d{1}),(?=\d{4})"

Regards,