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.
@Sisay_Dinku Try with
inputstring.TrimStart(‘0’) -string
Number-Inside for each row in datatable activity, Assign Row(0)=Cint(Row(0).ToString)
Hey @Sisay_Dinku ,
While reading the data from excel using read range activity check the Preserve Format property and try.
Thanks,
Sanjit
@sangeethaneelavannan1 thank you. I forgot to mention but not all numbers begin with zero (0), some are just natural numbers like 92145 (five digit)
Hi,
Can you try to use Workbook-WriteRange activity?
Regards,
@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?
@Sisay_Dinku
Check if row (0). first == 0 then do the truncate otherwise leave it as it is.
Hi,
How about to use FormatCell in advance, as the following? Please also check the following sample.
Sample20221129-3.zip (11.2 KB)
Regards,
@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
@Sisay_Dinku Did you try this
Check if row (0). first == 0 then do the truncate otherwise leave it as it is.
@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.
@Sisay_Dinku
how about this?
@sangeethaneelavannan1 I tried that as I was suggested by @Yoichi. However, I could not see any changes.
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
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
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
Hi
Here is the screenshot. FYI, the final output is in CSV format. Thanks
I did but it throws an error. If you do not mind, can you show me the full assign using an example? Thanks!
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
