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

Help Studio
, ,
1

Hi team,

The number 0 keeps getting truncated when trying to work with the number starting with 0. I tried to use text format in the input excel file, but the issue still persisted.
Can anyone help with this?

Here is the screenshot for your visibility.
image (25)

1 Like
2

@Sisay_Dinku Try with
inputstring.TrimStart(‘0’) -string
Number-Inside for each row in datatable activity, Assign Row(0)=Cint(Row(0).ToString)

1 Like
3

Hey @Sisay_Dinku ,

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

Thanks,
Sanjit

1 Like
4

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

1 Like
5

Hi,

Can you try to use Workbook-WriteRange activity?

Regards,

1 Like
6

@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?

1 Like
7

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

1 Like
8

Hi,

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

image
image691×869 28.2 KB

Sample20221129-3.zip (11.2 KB)

Regards,

2 Likes
9

@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

1 Like
10

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

1 Like
11

@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.

1 Like
12

@Sisay_Dinku
how about this?

image
image966×742 31.5 KB

1 Like
13

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

1 Like
14

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

1 Like
15

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

16

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

17

Hi

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

18

Hi @Sisay_Dinku

Did you try this?

=TEXT(“02345”,“00000”)

cheers

19

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

20

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

Cheers