CSV Dates sometimes changing format upon save

%3CLINGO-SUB%20id%3D%22lingo-sub-2082982%22%20slang%3D%22en-US%22%3ECSV%20Dates%20sometimes%20changing%20format%20upon%20save%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2082982%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20stumper%20for%20me.%20I%20have%20two%20columns%20of%20type%20Date%3A%20ValidStartDate%20and%20ValidEndDate.%20Each%20one%20has%20the%20same%20respective%20date%20for%20each%20record%3A%201%2F1%2F2021%20and%2012%2F31%2F9999.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20I%20save%20the%20file%20as%20a%20standard%20CSV%20(not%20UTF-8%20or%20other%20variants)%2C%20when%20I%20open%20it%20in%20Notepad%2B%2B%20or%20any%20other%20text%20editor%2C%20it%20contains%20the%20dates%20as%20displayed%20in%20Excel%3A%201%2F1%2F2021%20and%2012%2F31%2F9999.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20gave%20this%20file%20to%20a%20user%20to%20update%2C%20and%20they%20accidentally%20saved%20the%20second%20column%20as%2012%2F31%2F1999%20instead%20of%2012%2F31%2F9999.%20The%20first%20column%20was%20untouched%20(so%20stayed%20as%201%2F1%2F2021).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20when%20the%20file%20is%20saved%20as%20CSV%20and%20opened%20in%20a%20text%20editor%2C%20it%20contains%20the%20dates%20formatted%20with%20a%20truncated%20year%3A%201%2F1%2F21%20and%2012%2F31%2F99.%20This%20is%20a%20problem%2C%20as%20the%20file%20is%20being%20directly%20picked%20up%20from%20the%20save%20directory%20and%20processed%20by%20a%20data%20mapping%20application%20that%20expects%20a%20specific%20format%20(M%2Fd%2Fyyyy).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20more%20or%20less%20understand%20why%20this%20would%20happen%20for%20the%20column%20that%20was%20changed%2C%20but%26nbsp%3B%3CSTRONG%3Enot%3C%2FSTRONG%3E%20the%20column%20that%20was%20unchanged.%20Does%20Excel%20do%20some%20evaluation%20of%20whether%20it%20is%20possible%20to%20shorten%20the%20years%20to%20the%20last%20two%20digits%20across%20the%20spreadsheet%20(i.e.%20there%20would%20be%20no%20ambiguity%20across%20columns%20if%20the%20year%20format%20was%20shortened)%3F%20Or%20how%20is%20this%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUpon%20changing%20the%20second%20column%20back%20to%2012%2F31%2F9999%20(and%20again%20leaving%20the%201%2F1%2F2021%20untouched)%2C%20the%20file%20again%20saved%20with%201%2F1%2F2021%20and%2012%2F31%2F9999%20respectively%2C%20the%20full%20year%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20you%20would%20like%20screenshots%20or%20file%20examples%20-%20I%20can%20post%20those%20if%20needed.%3C%2FP%3E%3CP%3EThanks%20so%20much%20in%20advance%20for%20the%20assistance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlex%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2082982%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2084125%22%20slang%3D%22en-US%22%3ERe%3A%20CSV%20Dates%20sometimes%20changing%20format%20upon%20save%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2084125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F937767%22%20target%3D%22_blank%22%3E%40ahaugstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20clarify%20what%20do%20you%20mean%20here%20%22%3CSPAN%3Ewhen%20the%20file%20is%20saved%20as%20CSV%20and%20opened%20in%20a%20text%20editor%22%20under%20text%20editor%3F%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EIf%20you%20open%20csv%20file%20in%20Excel%20by%20double%20click%20it%20transform%20dates%20into%20the%20short%20format%20date%20at%20it%20is%20defined%20in%20your%20OS%20settings.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

This is a stumper for me. I have two columns of type Date: ValidStartDate and ValidEndDate. Each one has the same respective date for each record: 1/1/2021 and 12/31/9999.

 

After I save the file as a standard CSV (not UTF-8 or other variants), when I open it in Notepad++ or any other text editor, it contains the dates as displayed in Excel: 1/1/2021 and 12/31/9999.

 

I gave this file to a user to update, and they accidentally saved the second column as 12/31/1999 instead of 12/31/9999. The first column was untouched (so stayed as 1/1/2021). 

 

Now, when the file is saved as CSV and opened in a text editor, it contains the dates formatted with a truncated year: 1/1/21 and 12/31/99. This is a problem, as the file is being directly picked up from the save directory and processed by a data mapping application that expects a specific format (M/d/yyyy).

 

I can more or less understand why this would happen for the column that was changed, but not the column that was unchanged. Does Excel do some evaluation of whether it is possible to shorten the years to the last two digits across the spreadsheet (i.e. there would be no ambiguity across columns if the year format was shortened)? Or how is this possible?

 

Upon changing the second column back to 12/31/9999 (and again leaving the 1/1/2021 untouched), the file again saved with 1/1/2021 and 12/31/9999 respectively, the full year format.

 

Please let me know if you would like screenshots or file examples - I can post those if needed.

Thanks so much in advance for the assistance!

 

Alex

4 Replies

@ahaugstad 

Could you please clarify what do you mean here "when the file is saved as CSV and opened in a text editor" under text editor?

If you open csv file in Excel by double click it transform dates into the short format date at it is defined in your OS settings.

@Sergei Baklan 

 

Yes definitely - what I am doing is constructing my file in Excel, then saving it as a CSV (see screenshot) and opening it in a completely different application (Notepad++).

 

ahaugstad_0-1611261540675.png

 

I do this to ensure that the formatting is not occurring upon opening the file back up again in Excel, since I know it does a lot of formatting to CSV files upon opening, as you mentioned. This would be conditional formatting upon saving. The data before saving in Excel looks like the attached file. 

 

Then I open in Notepad++ and the file looks like so:

ahaugstad_1-1611261606680.png

 

Let me know if that still is not clear/you have additional questions.

Thank you for the assistance!

Alex

 

@ahaugstad 

And what is your OS short date format? For Windows it's here

image.png

@Sergei Baklan 

 

Mine is set to M/d/yyyy:

 

ahaugstad_0-1611262731267.png