Announcement

Collapse
No announcement yet.

US Date format "bias" when importing text file

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • US Date format "bias" when importing text file



    Hi guys,

    I'm having incredible difficultly with the bias towards US date formats in excel VBA.

    I'm trying to build a macro to import a text file into excel, where the number and contents of the columns is not known, and the delimiters and qualifiers are not known. In general, I've got it working correctly, but it really doesn't like dates.

    I create a variant array, and put the text values from the text file into this array as strings, which works perfectly, but when I move the array into a range, dates always appear in the "mm-dd-yyyy" format, with dates such as "13-07-2011" appearing as text values because they are not valid date strings.

    here is a code snippet that clearly show the problem

    Code:
    Dim vArray As Variant
    vArray = Sheet1.Range("I3:K5").Value
    vArray(1, 1) = "Date"
    vArray(1, 2) = "Name"
    vArray(1, 3) = "DoB"
    vArray(2, 1) = "07-06-2011"
    vArray(2, 2) = "David Richardson"
    vArray(2, 3) = "13-07-1981"
    vArray(3, 1) = "05-06-2011"
    vArray(3, 2) = "Bob Geldof"
    vArray(3, 3) = "01-09-1950"
    Sheet1.Range("I3:K5").Value = vArray
    The only progress I've managed to make on this is set the numberformat of the destination cells to text, which means they display properly, but they are no longer date values, and changing the numberformat back doesn't convert them.

    Any ideas?

  • #2
    Re: US Date format "bias" when importing text file

    I'd say you will have to convert the strings to a date. something like:

    Code:
    vArray(2, 3) = CDate("13-07-1981")
    If you dont know which columns are date then you will have to check first something like:

    Code:
    vArray(2, 3) = IIf(IsDate("13-07-1981"), CDate("13-07-1981"), CStr("13-07-1981"))
    Code:
    vArray(2, 3) = IIf(IsItADate("13-07-1981"), CDate("13-07-1981"), CStr("13-07-1981"))
    '// manual function to check for 2 "-" which signal a date
    Function IsItADate(x As String)
        IsItADate = (Len(x) - Len(Replace(x, "-", "")) = 2)
    End Function
    Reafidy

    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

    Comment


    • #3
      Re: US Date format "bias" when importing text file

      hmmmm, thanks Reafidy, that does work, but unfortunately in the date column, blank dates are shown as " - - " and it doesn't like that

      I can code around this specific example, but a commonly exported field is "comments" and I'm sure at some point some of those will have 2 dashes in them, however this is a lot closer than I've come before, and I'm sure I can get there from here

      Will update this when I've got it working correctly, for anyone else that has this same problem.

      Comment


      • #4
        Re: US Date format "bias" when importing text file

        Okay, if you get stuck and need more help attach an example so I can see what your dealing with.
        Reafidy

        Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

        Comment


        • #5


          Re: US Date format "bias" when importing text file

          GOT IT!!!!

          thanks heaps Reafidy for the right direction, I actually have 2 separate solutions to the problem now.

          Solution 1 (Full credit to Reafidy for this one)
          code to add any value into the array
          Code:
          on error resume next
          varray(i,j) = stemp
          if Len(stemp) - Len(Replace(stemp, "-", "")) = 2) then varray(i,j) = cdate(stemp)
          The "on error resume next" avoids the problem of invalid dates that just happen to have 2 dashes

          Solution 2 (which I prefer, but wouldn't have found without Reafidy nudging me)

          Code:
          set RE = new RegExp
          RE.Global = true
          RE.Pattern = "([0-9][0-9])[-/]([0-9][0-9])[-/]([0-2][0-9][0-9][0-9])"
          If RE.Test(sContents) Then sContents = RE.Replace(sContents, "$2-$1-$3")
          scontents is obviously a string variable holding the entire contents of text file, and the above regexp simply flips the day and month component in every date before it does anything else, which caters to the US bias.
          You would need the to add Microsft VBScript Regular expression to the reference for this.
          Last edited by richadj4; June 7th, 2011, 13:09. Reason: forgot a line!

          Comment

          Working...
          X