Importing CSV into Access - Losing Date Format

[mcgeejdw]

I'm trying to import a csv file into access to create a table...however
I am loosing date/timestamp values because Access does not have a
matching Date/Time format. The format in the csv file (which can't
change) is yyyy-MM-dd HH:mm:ss.SSS. Is there a way to create a custom
date format or is there a way that I can convert the values during
import?

 

[Douglas J. Steele]

As long as Access is recognizing the input field as a date/time, you should
be okay. How Access displays a date/time field has nothing to do with how it
stores it: date/time fields are stored as 8 byte floating point numbers,
where the integer portion represents the date as the number of days relative
to 30 Dec, 1899, and the decimal portion represents the time as a fraction
of a day.

Unfortunately, you won't be able to use the .SSS fractional seconds: the
resolution of a date/time field isn't sufficiently fine to be able to rely
on fractional seconds.

 

[John Vinson]

I'm trying to import a csv file into access to create a table...however
I am loosing date/timestamp values because Access does not have a
matching Date/Time format. The format in the csv file (which can't
change) is yyyy-MM-dd HH:mm:ss.SSS. Is there a way to create a custom
date format or is there a way that I can convert the values during
import?

The format of the Date/Time field is irrelevant: that just controls
how it's displayed. One problem is that Access date/time values are
stored accurate to the second; there's no provision for your .SSS
portion.

I'd suggest *linking* to the CSV file, and running an Append query to
append it to a pre-created table with a date field. You may need to
use

CDate([datetimestamp])

or even

CDate(Left([datetimestamp], 19))

to generate a date/time value.

John W. Vinson[MVP]

 

[28luliying]

·¢·ç¸ñ·ç¸ñ·ç¸ñ·ç¸ñ

 

[ivan_haohao]

As long as Access is recognizing the input field as a date/time, you should
be okay. How Access displays a date/time field has nothing to do with how it
stores it: date/time fields are stored as 8 byte floating point numbers,
where the integer portion represents the date as the number of days relative
to 30 Dec, 1899, and the decimal portion represents the time as a fraction
of a day.

Unfortunately, you won't be able to use the .SSS fractional seconds: the
resolution of a date/time field isn't sufficiently fine to be able to rely
on fractional seconds.

 

[Larry Linson]

What format are you getting. If it is importing as a string, you can convert
that string to a Date/Time field. Take a look at the DateSerial and
TimeSerial functions in VBA Help, and the CDate function and he DateAdd
function for creating the time part of the Date/Time field.

Larry Linson
Microsoft Access MVP