Set column cells in Format Number

Hello firends, @rkelchuri, @Rammohan91, @loginerror, @ClaytonM, @balupad14, @MAHESH1

i have a problem when saving the output in Excel.
The number in one column of the file are save d in this format:
7,49875083514645E+22
Whe I try to expand the number it gives me 7,49875083514645000000, 00
which is uncorrect.
It seems that UiPath truncates the number when saving it in Excel.
it should be 74987508351464560864834.
How can I save the complete number in UiPath, not in exponential mode, so that it is all visible and saved correctly?
I’m using the Excel Application Scope with Write Range.
The problem is not present when using Write Range Activity in Workbook folder.

Thank you so much,
It’s very urgent.
camilla:)

Hi @CamiCat

It is not an issue with the UIPath. It is with Excel which is an expected behavior.
To avoid this behavior, please set the column to Text in Excel. So Excel will be prevented from automatic exponential conversion.

Regards,
Karthik Byggari

1 Like

I think CSVs would work better in these situations, but in my experience with long numbers in Excel, I had to set the column width wide enough before writing the table with the long numbers. That way, Excel doesn’t change the value before it gets written. But, even then, I think Excel has a limit of how long the number can be before it changes it to exponential form.

Yes, the other option is to change the values with a single quote ' infront of the values so Excel sees it as text or format the column to Text (although not the best workarounds)

1 Like

Hello @KarthikByggari.
Thank you so much.
Can you please help me in doing this?
How to set the format of a column in an excel file which has to be created by the process?
This is the output file.
Thank you,
Cami

Thank you so much @clayton.
How can I set the column width wide enough before writing the table with the long numbers?
Can you please attach some screenshots of how to do that?
Thank you so much,
Cami

I tested in Excel manually and it has a limit of about 20 digits before the value changes to exponential and about 15 before it replaces the last digits with zeros.

To format to text, you can use the Select Range to select column, then TypeInto to use the Alt-key combination that will change it in the Home tab of the top ribbon.

Here is a snippet that performs those steps but your Range and Type Into string will be different
image

I think your string will be:
[k(ralt)]hnText[k(enter)]"

1 Like

Thank you so much @ClaytonM.
I’ll try your solution and let you know as ssoon as possible.
Thank yuu so much for your precious help.
Camilla :slight_smile:

Cool :smiley:
I also forgot to tell you that you need to format the column before writing the data.

1 Like

Thank you so much @ClaytonM.
After pressing the keys "ralth"I get the following.
How to set the format?


Thank you so much,
Camilla :slight_smile:

Can you show what your TypeInto activity looks like?, cause I’m not sure if you did it right.

1 Like

Unless you mean that you did it manually. Then, you actually need to press Alt +h +n +Text, and those steps will set the formatting.

2 Likes

Hi, @ClaytonM
Can you show an example of the second method? The one with the single quote ’

1 Like

Hi @Christopher_Gomez

I have found that running a loop on a large set of data only takes 1-2seconds, now. So use a For each with an Assign inside:

For each row In dt1
    Assign activity: row("columnname") = "'"+row("columnname").ToString.Trim

So essentially, loop through each row and change the current value to have a ’ infront. And, you can do other adjustments to other values in the same row if needed.

After that, when you open the file in Excel, you should see a green tick mark next to each cell (because it is a number seen as text).

Hope that helps.

Regards.

2 Likes