Problem with Date/Time Field; Importing Text File

[dlager]

Hi,
I am having some difficulties with a Date/Time Field. I am importing a | delimted text file into a table and the Date Field is resulting in a Type Conversion Error.

In the raw text file, the Date Field has the following Format (example):
01/03/2013 03:11 PM

My import Spec is as follows:
File Format: Delimited
Field Delimter: |
Language: English
Code Page: OEM United States
Date Order: MDY
Date Delimiter: /
Time Delimter: :
Four Digit Years: TRUE
Leading Zeros in Dates: TRUE
Decimal Symbol: .
Field Info (below):
FieldName1: RecordID
DataType1: Long Integer
Indexed: Yes (No Duplicates)
FieldName2: Date Opened
DataType2: Date/Time
Indexed: No

The only thing I can think of, is that the mix of Leading Zeros in the Time AND AM/PM is causing a problem. But, I do not see a way to address this with an import spec.

The odd thing is that if I import the DateOpened Field as Text, THEN change the DataType to Date/Time AFTER import, then save the table, it recognized/converts the DateOpened Fields correctly.

I'd LIKE to get the import spec correct (I have to update twice daily), But, barring that, if I could import as Text then build a Macro that would:
1) import text file(s)
2) change certain fields datatypes to Date/Time
3) Save Table(s)

That would suffice. I could then use VBS (and perhaps windows scheduler) to run the macro when needed.

 

[Cronk]

I get around any such difficulties by importing the data into a temporary table using mostly (if not all) text fields. (Empty the temp table before each import.)

Then I run whatever checks required to ensure the data is in the correct format.

Then I run an append query converting text fields to whatever format the main table requires.

This process can be put into a VBA module and executed either by a button or automatically when the database is open first each day.