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:
NEW_DATE: CDate(Mid$([DATE],4,3) & Left$([DATE],3) & Right$([DATE],4))
- 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.
- 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))
- Run the query to validate that the formula is entered correctly.
- Once verified, change the Query Type to a Make-Table Query.
- Give it a name
- Run the query
- Confirm the number of new rows
- Open your newly created table and again, you can validate your data -- your date is now in mm/dd/yyyy format.
- For clean-up sake, you can delete the query and the originally imported table.
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)
Comments