Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Thursday, July 15, 2010 1:20 PM
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?
All replies (6)
Monday, July 19, 2010 10:32 PM âś…Answered
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.
Thursday, July 15, 2010 2:30 PM
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:36 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 6:32 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:36 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:43 PM
Strange, I wonder why the other DB imports correctly with the same format. I will see if I can control the export output.