.txt Dates Import Issue 1

[newguy86]

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

 

[SkipVought]

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?

 

[newguy86]

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

 

[SkipVought]

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

 

[newguy86]

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

 

[dhookom]

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

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

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

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

Perfect. Exactly what I was looking for.

Thanks!

Travis
Charter Media