07-12-06, 04:40 #1Registered User
- Join Date
- Jan 2004
- Jersey, UK
Unanswered: CSV File Open Changes Date Format
I've searched the 'net high and low for a solution to this problem and "still haven't found what I'm looking for" (as U2 would sing).
The problem is the US/UK date conflict and it's easy to replicate (at least if you're on a UK Regional Setting - I haven't tried it on US settings for obvious reasons). Here's how to replicate the problem:
create a new s/sheet;
enter dates like this '01/01/2001' through to '01/02/2001' (dd/mm/yyyy) so you'll have 32 records in total;
save the s/sheet as a CSV file and close;
open the CSV file in Notepad and note the date values - all as expected;
now open the s/sheet once inside Excel - note the date formats;
and now open from Windows Explorer - note the date format is different;
This is the problem. As an aside you can see what's happening to some fields if you use the ISTEXT() function - some fields are not being recognised as dates. This is because Excel is being a prat and is trying to format data into a US date format even when I haven't asked it to.
Does anyone know how to turn off this feature. I know it's possible because it used to work on another pc I used (for another employer). And I know about all the other workarounds, e.g. .txt extension, Clarion date format, etc, etc. I just want the feature turned off.
Regards - RC
07-16-06, 22:07 #2(Making Your Life Easy)
Provided Answers: 10
- Join Date
- Feb 2004
- New Zealand
Have had the same problem with date in US/UK
what I have done is put the month as the name
'01 jan 2001' not 01/01/01hope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON