CODEWITHSUNDEEP
sql-server-2008ms-accessdatetimeimport
user3249299
user3249299

Reputation: 43

Importing a datetime field from csv/txt into Access

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:

For the 2 datetime columns:

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

Upvotes: 4

Views: 17774

Answers (6)

Beeru
Beeru

Reputation: 1

I had faced the same issue while importing CSV file with datetime. What I had done to solve this issue was by splitting date and time and merge.

Convert(varchar, yourdatecolum, 101 ) + ' ' + Convert(varchar, yourdatecolumn, 108)

Upvotes: 0

user2063329
user2063329

Reputation: 453

Access does import correctly the date and time information in the data. You need not drop (or remove) the time information. I have not tested for fractional seconds. However, if you have duplicates in the data column you may need to fix it and also you should correctly set the date format (YMD, DMY, etc.) Here is a recent link to a correctly imported text file. http://hodentekmsss.blogspot.com/2017/02/importing-text-file-into-ms-access.html

Upvotes: 0

user6030624
user6030624

Reputation: 1

If your output is CSV file, change the output format to "MM/DD/YYYY", and drop the time from the date. It will import just fine. If you need to use the time, make another column for it, and strip out the date leaving only the time. I was able to use hundreds of hours with two places.

Upvotes: 0

Jon Miller
Jon Miller

Reputation: 51

I found if you use "M/d/yyyy HH:mm:ss" as the date format (.NET), Access will import the date/time correctly, at least for if you are in the United States. Note, the hour is the 24 hour time. It won't work in 12 hour time with the AM/PM indicator. This is different than Excel, which does work with the 12 hour time. If you include milliseconds, it won't work. You can leave the time off if you don't need it.

Upvotes: 0

Johnny Bones
Johnny Bones

Reputation: 8402

I'm just adding this as an answer, since it ended up being the one that worked:

If you have control over the output from SQL Server, you can change the format of the date field in your view/stored procedure. Something like 

SELECT CONVERT(VARCHAR(10), [Field1], 101) AS Date1

That will strip the time off it, and Access should recognize it.

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123549

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.

Upvotes: 2

Related Questions