ACCESS IMPORT DATE/TIME IN CSV FILE

R

ryden

I'm importing a CSV file into ACCESS, but the date/time field (format:
9/26/2005 22:00) won't import as a date/time, only text. Any suggestions?
It imports fine into EXCEL as a date/time field, but the files I want to use
are over 700,000 rows long.
 
J

Jerry Whittle

Inport the all the CSV data into a 'working' table. Put that date data into a
text field. Create a query with all the fields in the working table and
surround the date field with the CDate function. Use this query as the basis
of an append query to the final resting place for the data.

One problem with CDate is that it will bomb if any of the data can not be
evaluated as a proper date. Therefore I suggest first checking the data using
the IsDate function. It will tell you if any of the 'dates' can't be
converted to a date.

IIf(IsDate([YourTextDate]) = True, CDate([YourTextDate]), #1/1/1950#)

In the example above if the date can be evaluated as a date, it is converted
to a date. If not the bogus date of 1 January 1950 is inserted instead. You
could then search for these bogus dates (or any other valid date value that
you chose) and fix them manually.
 
J

John Vinson

I'm importing a CSV file into ACCESS, but the date/time field (format:
9/26/2005 22:00) won't import as a date/time, only text. Any suggestions?
It imports fine into EXCEL as a date/time field, but the files I want to use
are over 700,000 rows long.
Click to expand...

Odd! How are you doing the import? With a text-file wizard?

Try *linking* to the file, and running an Append query into a pre-made
table with your date/time field predefined. It should convert
correctly.

John W. Vinson[MVP]
 
Top