Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Sep 2004
    Location
    Evesham, Worcestershire, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CSV Date import (Office 2000)

    I have a strange problem that I believe is due to incorrect Regional Settings at install. The problem:

    When a user imports a CSV file, the system tries to import dates in US format (mm/dd/yy). Being British we use dates in the correct format as dd/mm/yy. So when the dates are imported all dates with the day from 1 to 12 are recognised as dates (but with days and months transposed) and those with days 13 to 31 are left as General format. This problem only manifests on one PC. The Regional settings on the PC in question are set to United Kingdom, but I know that they were at Default Windows (that is US) when Office was installed. Uninstalling and reinstalling seems not to work because Office seems to remember the original setting.

    I cannot find any information on the internet on changing the Office regional settings other than simple instructions that state that changing the regional settings in Control Panel will change the Office Regional setting. I think I will need a Reg Hack to fix this. Does anyone know where I need to look in the Registry to find Office region settings?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CSV Date import (Office 2000)

    Try changing the extension from .csv to .txt. You'll have much more control over how data are imported. There have been threads about .csv files and UK-format dates before, and we didn't find a solution that worked consistently for everyone.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CSV Date import (Office 2000)

    Reggie

    This seems to be the bane of my life at the moment as I have been doing a major extract project from legacy systems to SQL servers. Hans is correct for the date issue as sometimes putting date formats ina csv file can cause problems especially if sending to SQL servers. I had a problem that the SQL server had a field Date/Time that allowed null values, it bulked. Changing the csv to txt allows excel to see it as text string. when importing the data into excel, Excel sees the string and makes the correct assumption ( from set up) that the string is a date and converts to D41 format. I found this method was the best way, as for me, SQL allowed the import.

    Also as Excel only allows 65536 rows of data txt allowed me more flexibility
    Jerry

  4. #4
    Lounger
    Join Date
    Sep 2004
    Location
    Evesham, Worcestershire, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Date import (Office 2000)

    Thank you for your replies. At least a consistant answer [img]/forums/images/smilies/ohmy.gif[/img]).

    This only re-emphasises my favouring of writing dates in 28-Jul-05 style, where there is no confusion. Though I'm actually getting to like SQLs standard yyyy-mm-dd hh:mm:ss as at least its a logical progression from large to small and appears to be consistent across platforms.

    I don't think my user will be so pleased. I'm sure to get the "but it works on so and sos PC". Hey ho! the fun of life as an IT manager.
    Thanks again - Reggie

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CSV Date import (Office 2000)

    Just a note: SQL (like VBA) is very US-centric, it requires mm/dd/yy or mm/dd/yyyy format.

  6. #6
    Lounger
    Join Date
    Sep 2004
    Location
    Evesham, Worcestershire, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Date import (Office 2000)

    The SQL databases I use are MySQL, Access and a Borland DB2. In my experience if the driver won't translate a normalised date (e.g. 1-Jul-2003) then best bet is to use yyyy-mm-dd hh:mm:ss format. The SQL books I have indicate this is the standard format for most SQL databases. I'll bow to your greater experience with MS SQL. In VBA and VBScript I tend to use month(), day() and year() a lot so I know exactly what I'm getting out.

    0nly goes to further prove what a pain dates are.

    Thanks again for the feedback

Posting Permissions

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