topic RE: Can't to get the last 0 from the Cell Excel in Product Forum

Can't to get the last 0 from the Cell Excel

Souziala — Mon, 04 Mar 2024 16:40:00 GMT

Hi Evryone,

When I whant tO get Collection from excel I got all data but when the data is Number I can't to get 0 in the last of Number like this 185.170 >> I Got juste 185.17 No 0 in the last of the Number .

Thank you for your helping

------------------------------
SLIMANE OUZIALA
Consultant
Arondor Capture
Europe/Paris
------------------------------

RE: Can't to get the last 0 from the Cell Excel

Daniel_Sanhueza — Mon, 04 Mar 2024 19:34:00 GMT

Hello Slimane,

I don't think the issue lies with the "Get Collection" action. Instead, I would suggest looking into the format of the values in the Excel file. I notice that '13656.29' has decimals, so the first dot '.' is acting as the decimal separator. "Get Collection" may be rounding the number.

Following this logic, '185.17' is equivalent to '185.170'; it's just rounded.

What I would do is change the format of the column in the Excel file and then obtain it with the action.

Edit: Sorry, I didnt reply with the workaround, so here it goes. Try to use FormatCell action and in the input cell type "B:B" or "B1:Bn" (where 'n' is the number of total rows) and the format if I remember correctly is "@" to set as text. Then use get collection and see what it brings

Regards.

------------------------------
Daniel Sanhueza
RPA Professional Developer
Deloitte
America/Santiago
------------------------------

RE: Can't to get the last 0 from the Cell Excel

michaeloneil — Tue, 05 Mar 2024 10:38:00 GMT

Hi @SLIMANE OUZIALA

It looks like some of the cells in excel are text and some are number which is why you have a couple of zeros and others are blank in the collection. As @Daniel_Sanhueza mentioned you will need to format the column of the ones you need zeros in. Use the Excel VBO action 'Format Cell' and you can set an entire column using this as B:B for example and either try text "@" and if you still dont get the result you need then try setting it as number "0.000"

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Can't to get the last 0 from the Cell Excel

Anton__Hosang — Tue, 05 Mar 2024 10:59:00 GMT

Excel formatting is a FACADE. Unless setting the text format FIRST (before data is even entered into that cell by whoever) in Excel, and then capturing any trailing zeroes, Excel will always transfer the value to BP as a numerical. No trailing zeroes.

If you want the text as formatted in Excel, you can make a new Excel action that asks for the Excel Text property of a call instead of the value. It will return whatever is displayed in the cell.

WARNING, where text is compressed in the Excel workbook and appears like this "##########", that is also what you will get!!

------------------------------
Anton Hosang
Director
Omega RPA Ltd
------------------------------

RE: Can't to get the last 0 from the Cell Excel

michaeloneil — Tue, 05 Mar 2024 11:48:00 GMT

I think transferring the data to bp collection as a numerical wouldnt always be the case where the format is general in excel or there are a mix of formats in column. The easiest and simplest solution would always be to format the cells to get the desired outcome, the default format in excel is General and if you enter a number in these cells the value will be missing the trailing zero and when taken as a collection and it will be shown as text. As you can see from the example screenshot below the first column was formatted as text, second as General and the third column as a number format. The text and general columns are displayed as text in the collection and only the number formatted column is taken as a number in the collection but Text and Number produce the desired outcome of showing the trailing zero.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------