Leading 0s get eleminated when a csv file is opened in excel

Help Studio
1

I want to keep the precedings 0’s before the value but when the csv file is opened in the excel it gets eliminated
i.e.,
00123 = 123

2

Hi @palla_shreeya ,

Welcome to the Community!

Try looping through all the rows and update the value of that particular column something like this,
CurrentRow(“Column1”)= “‘“+CurrentRow(“Column1”).tostring

And then try writing the data.

3

Thanks for response,

I have used the loop and used the suggestion and written the data in new csv file but there is no change its the same as before

4

Can you try surrounding it with ‘’ (single quotes) something like this

CurrentRow(“Column1”)= “‘“+CurrentRow(“Column1”).tostring +"’"

Later while reading the data you can trim the ’ quote wherever you want to.
DataTable
image
Logic

image
image710×638 18 KB

Output
image

Whenever I would be using the data from this file I’ll trim down the “'” .

Also If you want to use the CSV file in some kind of upload utility where this trailing ’ might result in an error.
So Once you have written the data into the CSV file, if you don’t open it in Excel, the formatting would always be there(i.e. all your preceding zeros would still be there) and you can upload it.
Just like this
image

5

I tried the solution its working but i tried with a csv file that I received from mail and 0’s are not coming
I need to read the csv file and keep the preceding 0’s and save in excel

Thanks for response

6

So when you’re downloading the file from Mail, the zeroes are gone, did you try to open it in Notepad?
you can try saving it in excel by adding “'”+currentRow(“Column1”).toString

And save as a .xlsx file, there your zeroes won’t be gone if they are already present in your csv when opened with notepad.

7

You can’t do it directly. The data is lost upon opening the csv in excel. Excel likes to think for us, most often wrongly. Here it sees values containing only digits, forgoes any format retention and looses the information for the sake of changing it into a number.
Any post processing of the data will have to go from the assumption that the leading 0’s are already gone.

If it is UiPath you read the files with, you can try reading them with a different activity: read csv

  • Option A: idon’t use excel, but read the .csv as a textfile and process it after that.
  • Option B: modify your .csf file using a tex editor. If you replace 000123 with `000123 excel maintains the format when you read it. It will not treat the leading ’ character as an actual character, returning only the full 000123 value.
  • Option C: post process your values. If you know that for example the length of your value including 0’s is always 6 characters, just append these leading 0’s again after reading the file using some form of loop.
    MyFullValue = right(“000000” + MyValueStrippedOfZeroes, 6) will turn 123 into 000123 again.

None of them pretty, but it is the result that counts.

1 Like
8

@palla_shreeya

Welcome to the community

  1. After reading the data from cav can you please open datatable and check if the data contains 0’s in it?
  2. Try using use excel file to open cav file and then use save excel file as activity and save as excel and check
  3. If that does not work…we can try usignexcel formula to add leading zeros if we know how many maximum characters would be there

If according to 1 if you see 0’s then we can resolve the issue of writing data with zeros if not need to use 3rd option of using excel formula

Alternately we can format the column using format cells activity as well to display properly

Cheers