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.
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.
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
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”
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
@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
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.
@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 ?
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.
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
ashokkarale
Anil_G
ppr
shyamala_shyamu
Josh_James
m-takeda1
Shahabaz
harshika.10732998
sharazkm32