Importing .txt file with time of day field into table

[pilk2008]

I'm able to do the import, but I can't get the time of day to show up in military format, which seems more useful when I'm wanting to filter out certain blocks of time later on.

The attached jpg shows the format of the source txt file. The time is shown as 09:30 AM for instance. The attached screen shot for the import text wizard doesn't mean much to me at this point, because no settings seem to work. I've also attached the Import Specification window.

What's been happening is I've been getting a table with field2 showing time in this format 09:30:00 AM where the AM is random and unreliable followed by field3 showing the correct AM/PM designation. This is when I just let all the text wizard stuff run by default settings.

I can then go into the table in design view and change the format of the time of day to hh:nn which gives me 09:30, but then I'm still stuck with the problem of converting all the figures in fields 2 and 3 into something I can work with. BTW, Hh:mm:ss defaults to hh:nn.

Would it be better perhaps to first convert my source file to another format such as csv?

I'm an access beginner so a lot of the solutions I see on some of the web sites are over my head.

Any help is sincerely appreciated!

 

[DCrake]

Simple Software Solutions

Hi

The first issue is that the import spec needs to know that the second parsed field is a date and time field. It is reading it as a string - this is due to the AM/PM suffix. What you have not shown is the format of the time field. This should be Short Time, and the input mask should be 00:00:00;0;_ .

Try this and see if it works. If not, luckily the txt file is propotionally spaced so as a last resort you will have to resort to VBA to individually import each line of text as a record one row at a time. By doing so you can convert the incoming time into a more meaningfull time as per your request.

CodeMaster:

 

[pilk2008]

Thanks - it works now!

Your comment helped me to realize maybe all I needed to do was delete that one field break marker - the one shown in my jpg between the time and the AM/PM designator. Once I deleted that extra marker, the time imported perfectly.

Thanks again!

Pilk2008

 

[DCrake]

Simple Software Solutions

Glad to be of help.