Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

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

  1. #1
    Join Date
    6th September 2007
    Location
    Palmerston North New Zealand
    Posts
    360

    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

    VB:
    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?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,921

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

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

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

    VB:
    vArray(2, 3) = IIf(IsDate("13-07-1981"), CDate("13-07-1981"), CStr("13-07-1981")) 
    
    
    VB:
    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 
    
    

  3. #3
    Join Date
    6th September 2007
    Location
    Palmerston North New Zealand
    Posts
    360

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,921

    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.

  5. #5
    Join Date
    6th September 2007
    Location
    Palmerston North New Zealand
    Posts
    360

    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
    VB:
    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)

    VB:
    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 at 13:09. Reason: forgot a line!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: January 19th, 2011, 01:19
  2. Replies: 1
    Last Post: July 22nd, 2006, 01:20
  3. Replies: 6
    Last Post: March 24th, 2006, 11:54
  4. Replies: 2
    Last Post: August 27th, 2005, 18:19
  5. Replies: 6
    Last Post: August 3rd, 2005, 20:54

Bookmarks

Posting Permissions

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