Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    dates corrupted on import (2000 SP3)

    Have recently upgraded to Office 2k from 97, and possibly wish I hadn't.
    I have an application which exports as a csv a load of data about people, dates, and money.
    To analyse it and do useful things with it, I import it monthly into an ambitious (my style) workbook, replacing the previous month's data.
    This is done with a 'point and shoot' macro, slightly tidied and the usual screenupdating = false trick applied.
    It seems a 'feature' of Excel 2000 that much of the dates part of the data is corrupted in this process (didn't happen with '97!) Days and months get inverted.
    I've tried emptying the columns in the target workbook and formatting them as dd/mm/yy 'in advance' but that doesn't stop bad things happening.
    Crazily enough, if the dates in the csv are copied and pasted 'by hand' into the target, there's no problem. But I'd rather fix it (with help from here.)
    The attached file shows the unwanted behaviour.
    The macro code (don't laugh!) is in the text file.
    The danger of this, for which I won't thank Mr Gates, is that I could get sent to prison for alleging that someone's reneging on a loan, when they're not!

  2. #2
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates corrupted on import (2000 SP3)

    Sorry, here's the code

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: dates corrupted on import (2000 SP3)

    John, I suspect the translation failure is because the import is assuming the US mm/dd/yy system, when the incoming data is dd/mm/yy. Hence dates which would be invalid in the US, with any day higher than 12, are treated as text, and in the conversions of 'apparently' valid dates, the month and day are transposed.. What do you have set as the OS default short date under Control Panel, Regional settings?
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates corrupted on import (2000 SP3)

    Yes, have checked this.
    'True Brit'. dd/mm/yyyy as short date format and dddd d mmmm yyyy for the real one.
    I notice Andrew is online but in prolonged blink mode.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: dates corrupted on import (2000 SP3)

    John, according to <post#=206918>post 206918</post#> and <post#=196619>post 196619</post#>, 2000 doesn't play well with non-US dates. You may have to tweak your code.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: dates corrupted on import (2000 SP3)

    John, try this code to convert your dates; test it carefully. You will have to modify it to fit your actual workbook and worksheet names, etc.

    Sub mmddyy2ddmmyydates()
    Dim rngDates As Range, rngCell As Range
    Dim varDate As Variant
    Set rngDates = Intersect(ActiveSheet.UsedRange, Columns("I:J"))
    If Not rngDates Is Nothing Then
    For Each rngCell In rngDates
    If InStr(rngCell.Value, "/") Then
    varDate = Split(rngCell.Value, "/", , vbTextCompare)
    rngCell.Value2 = DateSerial(varDate(2), varDate(1), varDate(0))
    End If
    Next rngCell
    rngDates.NumberFormat = "dd/mm/yy"
    End If
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates corrupted on import (2000 SP3)

    John

    Another take on the same thing. (As a New Zealander I have the same issues you do plus some (Dictionaries)) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    If I have control of writing the file - its easy. Write the dates with a format that includes alphas e.g. 01Jan04 or any sensible variants.
    Excel then cannot screw up as the dates are
    a) recognised, and
    [img]/forums/images/smilies/cool.gif[/img] unambiguous.

    Another trick that works is to use Word to pre-alter the text as above before import (Replace all "/01/" with "Jan" ...)

    HTH

  8. #8
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates corrupted on import (2000 SP3)

    John, Andrew,
    Thanks very much for your trouble, knowledge and imagination. I'll try John's solution first, as that was the first to show (and will also be a better 'learning experience': I think I can figure (pun) what it's doing, the 'datevalue' is the giveaway.)
    For a really clunky solution, I was thinking about using LEFT, RIGHT and MID formulas to split the dates into three elements (3 new columns for each column of dates), letting neo-colonialist Excel do its stuff on the import, then when safely there, concatenate the d, m and y bits and seeing if that was recognised as a date.
    I have got control of the file, so there's no problem about Andrew's method if I go that way.
    In the meantime, life's too short, so I'll go for a prebuilt solution.
    Thanks again.

  9. #9
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates corrupted on import (2000 SP3)

    John
    One more thought - although this is untested. My experience with VBA CSV imports suggests that the following occurs for each date

    a) if the date can be interpreted as US it will be - e.g. 01/03/2004 will be stored in Excel's internal date form as 3rd Jan 2004
    [img]/forums/images/smilies/cool.gif[/img] if it cannot be interpreted as US it is left as text e.g. 25/03/2004

    Your problem is therefore twofold

    a) decode the string ones (easy-peasy) , converting to a date format
    [img]/forums/images/smilies/cool.gif[/img] recode the date ones to their correct month

    I think John's code achieves the first, but not the second - I'd expect an Else on the IF statement.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates corrupted on import (2000 SP3)

    Hi Guys,

    The very best way around this problem is to NOT do a csv import.

    The steps I'd suggest:

    - temporary rename the file to another extension (txt)
    - switch on the macro recorder
    - open the txt file in Excel and choose the proper format.
    - on the final step, make sure to select the column with the dates and choose the proper date format
    - stop recording the macro, and check out the WOrkbooks.OpenText method. Adapt it to accept the normal csv filename.

    Of course this is not tested, so maybe it still fails, since VBA speaks American...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates corrupted on import (2000 SP3)

    Thanks for all the great ideas, chaps. As there's no great rush on this (it's no big deal to do the copy + paste by hand once a month) I'm going to have some fun with it (and maybe learn a bit.)
    Am trying turning the dates which are dates (ie not text) into serial number dates and defying xl, or vba, to 'Americanise' them, before applying a date format the way round I want.
    I figure that what we need is a 'Copy Special' a la WYSIWYG just as we have 10 kinds of Paste Special *grin*.
    Am curious about the New Zealand dictionaries: have the real ones got all the words upside down?

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates corrupted on import (2000 SP3)

    Since this is a once-a-month thing, consider using Data, Get External data, new Text query to import your data into a sheet directly.

    Next month, you select the sheet with the data and hit refresh data. You'll be prompted for a filename.

    You might have to rename the file to txt first though.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates corrupted on import (2000 SP3)

    Yes, Jan Karel, thought of this too.
    A potential difficulty is that the monthly csv goes to sundry other people too, to refresh the big application which they need to interrogate, too.
    That's why the macro is set up to read from a floppy disc: that's how it goes to two of the users, the others get it by email and they all know how to detach an email attachment to a floppy.
    I'm not too confident about their being able to detach it confidently to a specified folder with a specified path on their hard drives.
    Doesn't the 'get external data' route depend on access to the external data every time the analysis file is opened?
    I'm going to play with this in a number of ways over the next few days. Meanwhile, a domestic crisis intervenes....
    The odd thing is, I don't ever recall having this difficulty when the analysis tool was in xl 97. So apparently xl VBA comes in flavours?

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates corrupted on import (2000 SP3)

    It doesn't matter if the file is on floppy, once refresh is hit, let them select the file on the FDD.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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