Leading 0s get eliminated while opening the CSV file in excel after writing to CSV

I am fetching data in approprate format from DB.
I have datacolumns with value 01/02/2022 and 03:56
After I write to CSV,when I open using noteapd I could view the leading 0s but when I open the same csv file using excel the columns are changed to date and time and 0’s are trimmed.

I need to keep this 0s when I view that csv in excel.
I don’t want to append ’ or space to the value as well.
Can anyone throw some light to handle this.

Any help is appreciated.

@Palaniyappan
@lakshman
@Srini84
@ppr
@HeartCatcher

Thanks and Regards,
Geetishree

1 Like

@geetishree.rao

Can you read that CSV file and assign it to DataTable variable and then check column values whether it has leading 0’s or not.

If it didn’t work then create one template file with required column format. And then copy the template file into required location and then write the data.

Hi Lakshman,

When I read the CSV file/open with notepad,I could see the leading 0s.
Only when I view it in excel by double clicking the csv file…the leading 0s are gone

Even when I use a csv template and format the columns,after save when i open the formatting is gone…I need to export to csv

Thanks and Regards,
Geetishree Rao

@geetishree.rao

It’s expected behaviour in excel file. If you want to see leading 0’s then you can add some special characters prior so that leading 0’s won’t be disappeared.

I added " " space to the sql query and then it works fine…But i becomes diff in csv when opened with notepad
But if I do so the csv when opened in excel looks fine but when I open in notepad
the below thing happens:

Before adding Space,when opened in notepad:
x,y,01/12/2022,01:22

After adding space,when opened in notepad
x,y,“01/12/2022”,“01:22”

Thanks and Regards,
Geetishree

I think it’s because of the format in excel column

As @lakshman said we need to have a template created to adapt that format change

So keeping a standard template as a output File will help you in this scenario

And I would suggest to choose one format like open either in notepad or with excel based on your need. As both are different when it displays the data in it

So choose which application you want to actually use in your process based on which prepare the template files

Cheers @geetishree.rao

@Palaniyappan .Thanks for the suggestion.
I have already tried with CSV template ,even after saving the format to text in template,when we open the csv template again the format will not be present.
So CSV template is not helping out
ANd as the output has to be csv ,cant use excel

Hi @geetishree.rao ,

Is it possible to Provide us a Screenshot of the data in csv file when Opened in Notepad, and when Opened in Excel ?


@supermanPunch have shared the screenshot for ur reference…

Thanks and Regards,
Geetishree

@geetishree.rao ,

I think few weeks back I faced the same issue, I tried as @Palaniyappan suggested at that time it worked for me.
Read excel and pass the dt value in message box(after converting into the string from datatable) and see the leading zero will/may appear.

Thanks.

@HeartCatcher I am exporting data to csv…not the other way round and While getting data I am having correct format.The only problem is while opening the csv file in excel…the 0s are removed…Thanks

Hi @geetishree.rao ,
Maybe I have understood the cause of the situation. The Reason might be due to the System format. It might be d/M/yyyy. Could you Check your System Date Formats ?

image

@geetishree.rao
Just give a try Copy data from csv to new excel and use the copied new excel for further use.
I think it will work.

thanks

Hi All…
Thanks …

I found a way to resolve this and thought of sharing ,it may help others.

If we add a TAB +convert(VARCHAR(10),Char(9)) at the end of the column value then it will not eliminate leading zeroes when csv is opened in excel and will not add qoutes as well when opened in notepad.

I did this from the sql side.

Thanks and Regards,
Geetishree Rao

2 Likes

@geetishree.rao

most of the things were touched within this discussion. But I would recommend to not missmatch EXCEL and CSV

In Excel we can use the prepending ’ trick for forcing a value to get interpretated as Text
In CSV we can surround with "

We also should keep in mind that excel can visualize values different as it is in the file / data
You reported it by having the correct result in notepad++ but not presented in Excel

In such a case we would recommend to type into the excel formula box field and to check the value there.

However keep in mind. Excel is not much recommended for CSV data. You can afterwards manually reformat the cell formatting to check if this will help. Then based on your business case you can check, if a reformat should be included into the automation process or not

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.