28 July, 2008

Convert Date/Time Format With MS Access

I haven't had to use MS Access in 7 or 8 years, so recently when I had to do some simple data conversion, it took me longer than it should to figure out how to best accomplish the task. I needed to run some reports against some Salesforce.com data, and the data set was too large for the built-in report tool to handle, so I decided to import the data into Access to perform the reporting.

I quickly learned that the date/time format from Salesforce.com is not in the same format as Access. The solution is actually quite easy... transform the data when you import it. I began with my Salesforce.com data in the form of a CSV file (too big for Excel). Here are the steps I used:
  1. Link to the CSV data source
  2. Create a Make Table Query against the CSV datasource, selecting all data columns -- of course you only need to select the columns that you want to import
  3. Replace every date/time field with a forumla that parses the format and writes it in the format Access will accept.
    • Salesforce.com format: 2008-03-09T19:04:02.000Z
    • Access format: 2008-03-09 19:04:02
    • Formula: NEW_COLUMN_NAME: CDate(Left$([CREATEDDATE],10) & " " & Mid$([CREATEDDATE],12,7))
      NEW_COLUMN_NAME is the name of the column you want in your imported data
      CREATEDDATE is the name of the column of the data you are parsing
      CDate converts the string to a date
      Left$ and Mid$ are standard string parsing functions
      & " " & joins the string functions with a space in between
That's it. Now I have a table in Access with the correct date/time format so I can run my reports.

3 comments:

shells said...

hey there.

i have a different problem. i have a csv file with a date column, and i would want to change the current format (d/m/yy) to (m/d/yyyy)

any idea how?

CHRISdotTODD said...

shells,
See my new post for one way to accomplish this at: http://www.chrisdottodd.com/2008/08/convert-date-format-of-csv-and-import.html

Mark Iannucci said...

Just wanted to say thank you. I modified your formula for a different conversion issue I faced and it worked like a charm.