Convert date format of CSV and import to MS Access

Similar to my prior post on Convert Date/Time Format with MS Access, this explains how to take a date in dd/mm/yyyy format and convert it to mm/dd/yyyy, so that MS Access will recognize it. It takes just a few easy steps:

1. Assume you start with your data in CSV format, the first step is to Import the data into Access (File > Get External Data > Import...). When importing the date field, import it as text, therefore loading all the data into your database.
2. Create a Query to convert your date data and change from text to date format.
   
   • From Design View, add all the fields except the field with your date data that you want converted.
   • Insert a new field, and add (Build...) the following formula (see explanation and picture below):
     NEW_DATE: CDate(Mid$([DATE],4,3) & Left$([DATE],3) & Right$([DATE],4))
3. Run the query to validate that the formula is entered correctly.
4. Once verified, change the Query Type to a Make-Table Query.
   
   • Give it a name
   • Run the query
   • Confirm the number of new rows
5. Open your newly created table and again, you can validate your data -- your date is now in mm/dd/yyyy format.
6. For clean-up sake, you can delete the query and the originally imported table.
   

Formula explained:
NEW_DATE: CDate(Mid$([DATE],4,3) & Left$([DATE],3) & Right$([DATE],4))

• NEW_DATE: Column name of the new date field.
• CDate: Converts the text field to a date field. This only works if the text is in a supported date format.
• [DATE]: The name of the field that is being operated on. In our example, it is the text field that has the original date format.
• Mid$([DATE],4,3): Gets three characters from the data field, beginning witht he forth character, e.g. 10/
  
• Left$([DATE],3): Gets the first 3 characters of our data field, e.g. 23/.
  
• Right$([DATE],4): Gets the right four characters from our data field, e.g. 2008.
• &: Joins the data together, e.g. (10/) & (23/) & (2008) = (10/23/2008)

I hope this is helpful.