How to solve: 0 (zero) gets truncated with the number beginning with 0 in my output excel file

[Sanjit_Pal]

Hey @Sisay_Dinku ,

While reading the data from excel using read range activity check the Preserve Format property and try.

Thanks,
Sanjit

[Sisay_Dinku]

@sangeethaneelavannan1 thank you. I forgot to mention but not all numbers begin with zero (0), some are just natural numbers like 92145 (five digit)

[Yoichi]

Hi,

Can you try to use Workbook-WriteRange activity?

Regards,

[Sisay_Dinku]

@Yoichi
I use modern design
I write from data table to excel (using Write Data table to excel activity). Is there anything I can do while reading before writing?

[sangeethaneelavannan1 (Sangeetha Neelavannan)]

@Sisay_Dinku
Check if row (0). first == 0 then do the truncate otherwise leave it as it is.

[Yoichi]

Hi,

@Sisay_Dinku

modern design

How about to use FormatCell in advance, as the following? Please also check the following sample.

Sample20221129-3.zip (11.2 KB)

Regards,

[Sisay_Dinku]

@Yoichi
It looks like it did not work. If I may ask, why do we use the second sheet (sheet2)? Is there any way that I can use on the same sheet1

[sangeethaneelavannan1 (Sangeetha Neelavannan)]

@Sisay_Dinku Did you try this
Check if row (0). first == 0 then do the truncate otherwise leave it as it is.

[Sisay_Dinku]

@sangeethaneelavannan1 I do not want to do any truncation. I just wanted the input 5 digit zip code to be as is when taken to output. The only issue I have is when there is 0 at the beginning, it gets truncated and only four digits loaded.

[sangeethaneelavannan1 (Sangeetha Neelavannan)]

@Sisay_Dinku
how about this?

[Sisay_Dinku]

@sangeethaneelavannan1 I tried that as I was suggested by @Yoichi. However, I could not see any changes.

[Anil_G (Anil Gorthi)]

Hi @Sisay_Dinku

Just include a single apostrophe (‘) before the number value and then write the cell. Then it wont truncate zero . Excel will assume the cell as text and will not change the format

Cheers

[Sisay_Dinku]

Hi

But the (') is going to be loaded into the output file in addition to the Zero (0). I need clean number starting with 0

[Anil_G (Anil Gorthi)]

Hi @Sisay_Dinku

Can you check that because generally when we load data to system with this it would not create any issue as excel will ignore it

Alternately you can try giving the value as formula like this =TEXT(“02345”,“00000”)

cheers

[Sisay_Dinku]

Hi

Here is the screenshot. FYI, the final output is in CSV format. Thanks

[Anil_G (Anil Gorthi)]

@Anil_G

=TEXT(“02345”,“00000”)

Hi @Sisay_Dinku

Did you try this?

=TEXT(“02345”,“00000”)

cheers

[Sisay_Dinku]

I did but it throws an error. If you do not mind, can you show me the full assign using an example? Thanks!

[Anil_G (Anil Gorthi)]

Hi @Sisay_Dinku

This is not in assign.

This the formula for excel.

You have to use it in write cell activity to set the formula in excel

Write Cell

Cheers

[Anil_G (Anil Gorthi)]

@Anil_G

00000”)

Hi @Sisay_Dinku

Please see how to use

I gave double double quotes(“”) to escape because even in excel formula we need double quotes again.

If you want to include a variable this is how you do it

"=TEXT(""" + Variable + """,""00000"")"

cheers