INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

.txt Dates Import Issue

.txt Dates Import Issue

(OP)
I have a .txt file that I'm trying to import into access. The import works well and the data comes through just fine except that the only way that I can get the date/time stamp for each record to appear is if I import that field as a text field.

The issue I run into with that is that we need the date/time stamp to be setup as a date/time field so that we can evaluate reporting periods and turnaround time for our contracts/orders.

Now I know there's a way to convert a text field into a date/time field but access doesn't seem to recognize the date/time format in the .txt file.

Here's what it looks like:

2014-02-20 09:44:58.237

So what options do I have to resolve this? Or are there any other approaches I should look into?

Previously we have tried to have the system we receive this from output the data as a .csv file but that proved problematic because the system was setup to automatically seperate multiple contracts (that are tagged to the same change/order request) by a comma and from what I've been told we can't change that.

Travis
Charter Media

RE: .txt Dates Import Issue

Hi,

By what method are you importing this .txt file into Access?

Are you using any delimiter?

Can you COPY several rows and post here?

RE: .txt Dates Import Issue

(OP)
Hello Skip,
I have a linked table to the .txt file that I use to upload the information.

The file is setup as tab delimited.

Here's a sample of what the file looks like:

ID ContractNo Name StatusDesc UserName UpdateDateTime
59342 11664803 National Submitted to Traffic grice 2014-02-20 09:44:58.237
59344 11664803 National Submitted to Traffic grice 2014-02-20 10:15:55.827
59345 11664803 National Traffic Updates Accepted mzobrist 2014-02-20 10:36:00.317
59931 11664873 National Submitted to Traffic kerskine 2014-02-21 08:18:18.577
59932 11664849 National Submitted to Traffic kerskine 2014-02-21 08:21:03.887
59933 11664870 National Submitted to Traffic nwalchak 2014-02-21 08:24:39.450
59934 11664869 National Submitted to Traffic nwalchak 2014-02-21 08:24:50.740
59935 11664882 National Submitted to Traffic nwalchak 2014-02-21 08:36:38.123
59936 11664882 National Submitted to Traffic nwalchak 2014-02-21 08:36:38.127
59994 11664849 National Traffic Updates Accepted mdiesel 2014-02-21 08:49:07.493
60063 11664882 National Submitted to Traffic kerskine 2014-02-21 09:11:43.007
60064 11664882 National Submitted to Traffic kerskine 2014-02-21 09:11:43.010
60066 11664870 National Submitted to Traffic nwalchak 2014-02-21 09:23:38.393
60068 11664869 National Submitted to Traffic nwalchak 2014-02-21 09:24:02.347
60069 11664869 National Traffic Updates Accepted mdiesel 2014-02-21 09:25:16.367
60070 11664870 National Traffic Updates Accepted bprosperi 2014-02-21 09:25:34.140
60071 11664873 National Traffic Updates Accepted mdiesel 2014-02-21 09:41:10.843
60072 11664882 National Traffic Updates Accepted bprosperi 2014-02-21 09:57:53.963
60073 11664882 National Traffic Updates Accepted bprosperi 2014-02-21 09:57:53.977

Travis
Charter Media

RE: .txt Dates Import Issue

Then how do you delimit the text fields in you IMPORT SPEC?

RE: .txt Dates Import Issue

(OP)
I'm not exactly sure what you're referring to but I'll take a stab at it.

When I linked the .txt file to access, a wizard came up and I told it at that time that the data is Tab Delimited.

Travis
Charter Media

RE: .txt Dates Import Issue

I would create an append query to uses the CDate() function on everything to the left of the period.

CODE --> debug

? CDate("2014-02-21 09:24:02")
2/21/2014 9:24:02 AM 


CODE --> expression

CDate(Left([UpdateDateTime], Instr([UpdateDateTime],".")-1)) 

Duane
Hook'D on Access
MS Access MVP

RE: .txt Dates Import Issue

(OP)
Perfect. Exactly what I was looking for.

Thanks!

Travis
Charter Media

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close