Importing Dates

[Anton1945]

I upgraded to Access-2010 and the Text File Import function will not recognize dates in the format YYYY-MM-DD. The import dialogue sees enough to recognize the field as a date, but then every date encountered is written to the Import-Errors table. This is true whether the file has a .txt or .csv extension. The actual file format is .csv.

 

[pr2-eugin]

So is this import going to be a periodic upload?

 

[Anton1945]

This is a test of a biweekly massive data transfer from an Oracle Business Intelligence database with which I do not have ODBC connectivity.
------------
Anton

 

[pr2-eugin]

All I can think of is import the dates as text, then you can use a simple UPDATE Query.. but I am sure there is a logical explanation to this.. So I am not going into detail of my idea..

Wait for a few hours more experienced users will have a look at it and would be guiding you in this..

 

[Steve R.]

1. Import the dates as text, as pr2-eugin suggests.
2. Then run a program that will very the validity and/or correct the text field so that it has your desired format of YYYY-MM-DD and then saves it into a text field.

The code below is one line in a text import program. BField15 is a text field holding a date value (10122013). There are no delimiters in this text field. D7Field15 is a date field. You will need to modify the code to match what you wish to accomplish.

If Not IsBlank(SourceRS!BField15) Then DestinationRS!D7Field15 = ReturnDateValue(SourceRS!BField15)     'BField15

The code below checks date values. It also adds the "/" delimiter. If the date can't be parsed, the function returns a date of 1/1/1900. This function returns your text date as a date date.

Public Function ReturnDateValue(strInput As String) As Date
    Dim bolDateOK As Boolean
    bolDateOK = True
    strInput = Trim(strInput)
    ReturnDateValue = #1/1/1900#
    If Val(Left(strInput, 2)) = 0 Or Val(Left(strInput, 2)) > 12 Then bolDateOK = False
    If Val(Mid(strInput, 3, 2)) = 0 Or Val(Mid(strInput, 3, 2)) > 31 Then bolDateOK = False
    If bolDateOK And Len(strInput) = 8 Then ReturnDateValue = CDate(Left(strInput, 2) & "/" & Mid(strInput, 3, 2) & "/" & Right(strInput, 4))
End Function

 

[Anton1945]

I discovered that the ACCESS default date format is MM/DD/YY. In order to get around this I will have to build an import record layout for each of my downloads, that's over 800 fields in 12 tables. I was hoping to avoid the work.
----------------
Thanks for the code snippet,
Anton

 

[namliam]

While doing the import in "Advanced" you can change the order and delimiters for dates, which indeed by default is MMDDYYYY with a / as delimiter.
But you can change it to most any format and delimiter you like and have the file imported without issues/errors.

You can save these settings inside your DB as a file spec and use that on any future (automated) import of a simular file.