25 August, 2008

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.

No comments: