Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108

    Unanswered: CSV File Open Changes Date Format

    Hi all,

    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

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,472
    Provided Answers: 10
    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/01
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •