Writing to Excel drops leading 0s

Help
1

I have a set of data that I am having to read from one excel file split it up by certain filter criteria and then write it into smaller excel files. One of these data columns is zip codes and I am having issues with leading 0s from zip codes in some states. When I try to write back to excel I lose the leading 0 if I just use the write range feature.

The main solution I can find in the UIPath forums is to add an ’ to force excel to ignore the leading 0. This is not really feasible for my process as it would require to loop over the entire excel file which can be very large (last week’s file was 10k rows) and would add considerable amount of time to the process. Unless I have missed a method to easily append a ’ to every cell in a specified column that would be faster than a for each loop.

I remember reading somewhere that using csv was supposed to also fix the problem but it definitely does not unless there is a special encoding that is needed (when I tried csv I lost leading and trailing 0s actually as well as - in 9 digit zip code formats).

Does anyone know of any solution that would fix this problem?

If only it was possible to have a check box to tell excel to not change my data.

2

Hi, welcome to the community!
When you open your excel file, can you see the leading zeroes there? cause if you do, then reading it into uipath will also bring the leading zeroes, so to write back you would have to do so in a text column and not a number one so those would be naturally kept.

3

Hey thanks for the quick reply. I have tried formatting columns as a number value but when it is wrote back to excel the cell format defaults to General. It does not seem like excel is accepting any form of formatting from the UIPath data.

I have the leading 0 in the data table when I read excel and then I keep that leading 0 all the way to the write range stage and when it is written back to excel the 0 is lost.

4

@thomas.j.wilson

May be know the Type of the column in data table is it string?

Other way to can do it very simple using Execute non query

Did you tried execute non query method?

5

@thomas.j.wilson,

Include this package in your process and try with Change Format Activity of that cell.

02
02265×570 20.9 KB

01

6

I set the data type of that column to Int32 and it still went through as general format in Excel.

I will try the Non query method thanks!

7

@thomas.j.wilson

column type should be in string

8

That’s what I tried originally but did not work without the apostrophe.

9

What i tried to advise you is exactly the opposite, there are no numbers with zeroes to the left, if you need this, then you must use text format… forget about how your data looks, just use it as text…

10

@bcorrea Sorry about that I completely misread your suggestion. Is there a specific data type that should be used such as a string builder? I tried string but it still was recognized as General when it went to excel. I managed to get the change format option that @sarathi125 recommended working so that is what I am currently using.

11

If your column held simple text like “Car”,“Bus”,“Helicopter” you would have no problem whatsoever, like if you want to store something like “000123”, then just treat your data the same way, neve work with it as 000123 or it will become 123…

12

This is the solution: using Write Range activity from System >> File >> Workbook . You should use the Read Range activity under the System too.

1 Like
13

I need to write a Single text value in one cell, I can’t use workbook for this. it’ll ask for the datatable to insert always. How can i wrote 00123 in that specific cell?