Text Data Import with General Dates

[kkitt]

I have a text file that I am trying to import that has the date in the following format "05/14/03 04:54:00 PM". I have the advance import specs setup as MDY, with the / as date sep, unchecked 4 digit years and checked leading zeroes in the date.

Every time I import I get a type conversion error on these dates. If I open in Excel first and save as a Excel speadsheet then import this, then the dates are brought in correctly.

I am tring to automate this and need to be able to import the text file without the Excel step.

Thanks in advance.

 

[M8KWR]

have you tried just importing the first 8 characters to see if that works?

 

[kkitt]

I have other fields that have just the date in them and they import correctly. Also this is in a tab delimited file so all the columns do not match up so the first 8 char are not always in the same columns.

 

[M8KWR]

why don't you link the text file to access, this way you will never have to re-import the file, it will always be linked, everytime the file changes or you get an updated version, just overwrite the file (but always keep a backup, just incase)

What are you doing with this data when you get it into access. Ammend it to a table, run queries on it????

In a query you could format the field that is giving you hassle, something like

Format([Date FIELD],"Short Date")

 

[kkitt]

This data will added to a historical table that later queries will be run against for week, month, ect. reports.

This data needs to reside in the database for a couple of years and then will be deleted.

 

[PHV]

Have you tried to import this column as text and then play with the Left and CDate functions ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kkitt]

Not really, these date/time fields will be used in queries as part of the selection criteia and i was hopeing to not have to add any additional overhead by having type conversion done.

Also indexing/sorting may become an issue if these are text fileds opposed to dates.

 

[PHV]

I suggested a work around during the import phase only.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244