locked
Date/Time field not importing correctly from text file RRS feed

  • Question

  • I have a fixed-width text file that I'm importing data into an existing database. Originally, I setup the table structure manually, and when I tried to import the text file I kept getting errors on the field that holds the date (YYMMDD). So, using the import wizard I setup the "specs" using fixed width fields and let the wizard detect the data types. The wizard setup the field as a "number" and does pull the dates in, but not as a date/time data type. There are no delimiters (being fixed-width) nor any other characters separating the values (YYMMDD). I've specified that format and it still will not import as date/time. Any suggestions?
    Thursday, July 15, 2010 1:20 PM

Answers

  • I was able to fix this, in the specs I placed a " " <blank space> in the date delimeter field. Just FYI for anyone that might run across this thread down the road.
    • Marked as answer by Ji.Zhou Wednesday, August 11, 2010 8:58 AM
    Monday, July 19, 2010 10:32 PM

All replies

  • I'd import it to a temporary table as a text field.  Then use an update query to copy the data to your production table, using string and date functions to convert it to a date.  Something like this:

    thedate: CDate(Mid([TextDateField],3,2)  & "/" & Right([TextDateField],2) & "/" & Left([TextDateField],2))


    -- Roger Carlson
    MS Access MVP 2006-2010
    www.rogersaccesslibrary.com
    Thursday, July 15, 2010 2:30 PM
  • You can also create a function to convert a large number of string date formats to a date:

    '--------------------------------
    Function ConvertStringDateToDate(StringDate As String, StringFormat As String) As Date
    On Error GoTo Err_ConvertStringDateToDate

    Dim Newstring As String
    Dim tempstring As String, TempFormat As String
    Dim part1 As String, part2 As String, part3 As String
    Dim part1format As String, part2format As String, part3format As String
    Dim i As Integer

        'test for whether both string either have or do not have slashes
        If Not ((InStr(StringDate, "/") <> 0 And InStr(StringFormat, "/") <> 0) Or (InStr(StringDate, "/") = 0 And InStr(StringFormat, "/") = 0)) Then
            MsgBox "formats do not match"
            Exit Function
        End If
       
        tempstring = StringDate
        TempFormat = StringFormat
       
        If InStr(StringFormat, "/") <> 0 Then 'string has slashes
            part1 = Left(tempstring, InStr(tempstring, "/") - 1)
            part1format = Left(TempFormat, InStr(TempFormat, "/") - 1)
            tempstring = Mid(tempstring, InStr(tempstring, "/") + 1)
            TempFormat = Mid(TempFormat, InStr(TempFormat, "/") + 1)
            part2 = Left(tempstring, InStr(tempstring, "/") - 1)
            part2format = Left(TempFormat, InStr(TempFormat, "/") - 1)
            part3 = Mid(tempstring, InStr(tempstring, "/") + 1)
            part3format = Mid(TempFormat, InStr(TempFormat, "/") + 1)
       
        Else 'string does not have slashes
            part1 = Left(tempstring, 1)
            part1format = Left(TempFormat, 1)
            tempstring = Mid(tempstring, 2)
            TempFormat = Mid(TempFormat, 2)
           
            Do While Left(TempFormat, 1) = Left(part1format, 1)
                part1 = part1 & Left(tempstring, 1)
                part1format = part1format & Left(TempFormat, 1)
                tempstring = Mid(tempstring, 2)
                TempFormat = Mid(TempFormat, 2)
            Loop
           
            part2 = Left(tempstring, 1)
            part2format = Left(TempFormat, 1)
            tempstring = Mid(tempstring, 2)
            TempFormat = Mid(TempFormat, 2)
           
            Do While Left(TempFormat, 1) = Left(part2format, 1)
                part2 = part2 & Left(tempstring, 1)
                part2format = part2format & Left(TempFormat, 1)
                tempstring = Mid(tempstring, 2)
                TempFormat = Mid(TempFormat, 2)
            Loop
           
            part3 = tempstring
            part3format = TempFormat
           
       
        End If
       
        'put string together ---
        'Note: for International formats other than month/day/year,
        'you will have to modify this code to put them in the proper
        'order
       
        If Left(part1format, 1) = "M" Then
            Newstring = part1 & "/"
        ElseIf Left(part2format, 1) = "M" Then
            Newstring = part2 & "/"
        Else
            Newstring = part3 & "/"
        End If
       
        If Left(part1format, 1) = "D" Then
            Newstring = Newstring & part1 & "/"
        ElseIf Left(part2format, 1) = "D" Then
            Newstring = Newstring & part2 & "/"
        Else
            Newstring = Newstring & part3 & "/"
        End If
       
        If Left(part1format, 1) = "Y" Then
            Newstring = Newstring & part1
        ElseIf Left(part2format, 1) = "Y" Then
            Newstring = Newstring & part2
        Else
            Newstring = Newstring & part3
        End If
       
        MsgBox Newstring
        ConvertStringDateToDate = CDate(Newstring)
       
    Exit_ConvertStringDateToDate:
        Exit Function

    Err_ConvertStringDateToDate:
        If Err.Number = 13 Then 'type mismatch
            MsgBox "Not recognized as a date.  Check format"
            Resume Exit_ConvertStringDateToDate
        Else
            MsgBox Err.Description
            Resume Exit_ConvertStringDateToDate
        End If
    End Function
    '--------------------------------

    In a query, you'd call the function like this:

    thedate: ConvertStringDateToDate([TheTextDate], "YYMMDD")

    On my website, I've got a small sample database called ConvertStringStuff.mdb which illustrates this as well as other conversion functions.


    -- Roger Carlson
    MS Access MVP 2006-2010
    www.rogersaccesslibrary.com
    Thursday, July 15, 2010 2:36 PM
  • Thanks for your response, Roger. I guess what I'm looking for is the reason the data isn't being pulled in correctly. I have a similar database that is taking data from the same mainframe, in the same format, with the same delimiter parameters set in the import wizard and it brings in the dates as date/time data types. Is it a known bug?
    Thursday, July 15, 2010 6:32 PM
  • It's simply that Access does not recognize a six-digit number as a date. The date parser is pretty good, and can recognize formats such as "07-15-10" or "July 15 2010" or "7/15" as dates - but a generic string of six digits will not be.
    John W. Vinson/MVP
    Thursday, July 15, 2010 6:36 PM
  • Strange, I wonder why the other DB imports correctly with the same format. I will see if I can control the export output.
    Thursday, July 15, 2010 6:43 PM
  • I was able to fix this, in the specs I placed a " " <blank space> in the date delimeter field. Just FYI for anyone that might run across this thread down the road.
    • Marked as answer by Ji.Zhou Wednesday, August 11, 2010 8:58 AM
    Monday, July 19, 2010 10:32 PM