Date format in .CSV file

[Deepak Sharma]

Hello Everyone, Hope you all are doing good.

I am facing some issue in CSV file. When changing date format to yyyy-mm-dd format in csv file. When I again open it and check it converts date format to dd-mm-yyyy format.
Many times I have changed formating & saved it. but couldn't get the solution. There are actually 50 files.

I am unable to attach the file also here, because it doesn't allows the extension. So attaching a simple .xlsx file. In this file it saves and shows actual format when I open it but saving this as .CSV it converts again into normal format.

Can someone assist on this please.

Thanks & Regards,
Deepak Sharma

 

[Hui]

That's correct and as it should be

Excel imports dates to a Number

It is only how that number is displayed that shows it as yyy-mm-dd or dd-mm-yyy etc

When you save it to a text file it is saving the number in either the default system date format or in the format it was imported in.

 

[Deepak Sharma]

Thanks Hui, But actually I need to upload data to database for all 50 files...database accepts only yyyy-dd-mm format & only csv files.

As I am amazed because last week it was saving in yyyy-dd-mm format but not now. How to get rid of this ?


Thanks !!

 

[NARAYANK991]

Hi ,

What exactly is your problem ?

1. When a .csv file is imported into Excel , it will have a certain date format , which depends on your system date format. If these are all valid dates , recognized by Excel as such , then you can format them to display in any format that you want.

2. When a .csv file is opened using a text editor such as Notepad , it shows the data in the same format in which it is stored on disk.

Do you have a problem with (1) or with (2) ?

When I opened your Excel workbook , and then saved it as a .csv file , and then opened it with Notepad , it shows all the dates to be in yyyy-mm-dd format ; is this how you want it ?

Narayan

 

[Deepak Sharma]

Hi Narayan,

My system date format is DD-MM-YYYY

File which I have attached is .xlsx format and has correct date format (i.e. YYYY-MM-DD), which I need in .CSV file also.

When I SAVE AS this file in .CSV format it gets saved but when I open this .CSV file from saved location THEN the date format gets changed to DD-MM-YYYY format automatically.
I need this formatting as it was at the time of saving in .csv

.XLSX file is showing exact format when I RE-OPEN it BUT .csv is not showing it.


Please suggest

Thanks & Regards,

 

[NARAYANK991]

Hi ,

I do not know what your problem is.

I am uploading 4 files , TEST1.xlsx and TEST1.csv , and TEST2.xlsx and TEST2.csv

The date formats in each .xlsx and .csv file match ; thus if the date format in the .xlsx file is dd-mm-yy format , then the .csv file dates are also in dd-mm-yy format.

if the date format in the .xlsx file is yyyy-mm-dd format , then the .csv file dates are also in yyyy-mm-dd format.

Since the forum does not allow uploading .csv files , I have renamed them with the .txt extension ; after you have downloaded the two .txt files , rename them to .csv

Ensure that you open the .csv files in Notepad.

Narayan

 

[Deepak Sharma]

Thanks Narayan,

They are opening in Notepad in .txt format correctly but not in excel as .csv

Anyways Thank you very much for your efforts.

Regards,
Deepak Sharma