Import date field problem.

[Thief_]

I need to analyse a 19MB CSV file, so i'm trying to import it into Access
2003. I can't get the Date/Time field to import- all I get is a blank field
and the following in a seperate table (I import using Get External Data):

MessageLog_ImportErrors Error Field Row
Type Conversion Failure Date 386
Type Conversion Failure Date 387
Type Conversion Failure Date 388
Type Conversion Failure Date 389
Type Conversion Failure Date 390


Here's a part of my CSV file:

"Date","Server","School#","Type","#","File Name","Status"
"19/07/2004 7:00:09
AM","014968FS001","4968","HRS526","66","014968200407190700097230.txt","FINIS
H"
"19/07/2004 7:00:10
AM","014968FS001","4968","HRS526","67","014968200407190700101470.txt","FINIS
H"
"19/07/2004 7:00:14
AM","014968FS001","4968","HRS526","68","014968200407190700145530.txt","FINIS
H"
"19/07/2004 7:00:15
AM","018804FS001","8804","HRS526","116","018804200407190700153330.txt","FINI
SH"
"19/07/2004 7:00:17
AM","014980FS001","4980","HRS526","14","014980200407190700178500.txt","REJEC
T"

The only way I can get the date field to import is to import it as Text but
I need it in a Date/Time field.

I'm importing using:
* Delimited
* By Comma
* First Row contains Field Names
* In New Table
* Date column as Date/Time
* Let Access add Primary key

 

[Steve Schapel]

Thief,

There may be other ways around this, but the first thing I would try
here is to do it in 2 steps, i.e. import into an Access table with the
Date data as text, and then run an Append Query to move it to the table
with the correct data types etc. That would mean using a CDate()
function to change it from string to date.