Ms-access – Importing a datetime field from csv/txt into Access

datetimeimportms-accesssql-server-2008

I have the output of a SQL Server 2008 query saved to a text file and I am trying to link it to an Access database (Access 2007-2010).

The text file contains two date columns in the SQL Server 2008 datetime format, like this:

EFFECTIVE START DATE:
---------------------
2013-07-01 00:00:00.000 

EFFECTIVE END DATE:
-------------------
2014-06-30 00:00:00.000

In the Import wizard, I click Advanced and change the following:

  • Code Page = Western European (Window) instead of Unicode (UTF-8)

For the 2 datetime columns:

  • data type = Date/Time
  • date order = YMT
  • Date delimiter = –
  • Leading zeros in dates = ticked

While still in the import wizard the data looks fine but as soon as the import is finished and I open the table in Access, I see #Num! in the two date columns.

I have tried a combination of settings in the import wizard (leave the code page as Unicode, not change the date order, not ticking the leading zeros in dates, etc) but in the end I had to import the dates as text, which stops me from doing any calculations now.

Many thanks in advance for the help

Best Solution

in the end I had to import the dates as text, which stops me from doing any calculations now.

True, you can't manipulate the text fields as true dates, but you can run a make-table query to convert them to real Date/Time values like so:

SELECT 
    ID, 
    CDate(Left([Field1],InStr([Field1],".")-1)) AS Date1, 
    CDate(Left([Field2],InStr([Field1],".")-1)) AS Date2 
INTO SqlDataWithRealDates
FROM SqlData;

The issue with the original import is that Access Date/Time values do not support fractional seconds so Access will never recognize the values as such if they include 00:00:00.000. The above query removes the trailing .000 before passing the strings to CDate().

Another alternative as suggested by Johnny Bones in the comments below is to alter the SQL Server query to use something like CONVERT(VARCHAR(10), [Field1], 101) AS Date1 to remove the time component from the strings that will eventually be imported.