Trouble with time import from txt file. AM hours don't work? 1

[PenelopeC]

Hi,
First I'm gonna say THANKS because I know you all have helped me through some sticky wickets and you probably will again.

I'm importing a file of call detail records. I've manually imported the file numerous times, have finally come up with a Spec that consistently works except for one field. The "STARTTIME" and "ENDTIME" fields in the text file look like this...

125833 (for 12:58.33)
93214 (for 9:32.14)

I specified DATE/TIME format and it works perfectly for any time that has two digits for the hour. Anything from one minute after midnight to 9:55.59 doesn't import - it ends up in the "file import error" table. What gives?

I've searched HELP and read FAQs this morning til my eyes crossed. I'm thinking of adding a zero to those time that would only have one digit in the hour. (It seemed to work in a test import). I'm a technical user and would struggle with coding something to do this, but it has to be done every month and I would like to automate. I did a macro to import files, etc. in another app. Can someone point me to a resource that would help me through this?

I'm using W2K and Access97. TIA!

PenelopeC
~~~>-/O~~~~~swimming right along

 

[RJFost]

What about using a 24-hour time format (e.g., 2:37 AM = 0237, 7:19 PM = 1919, etc.)? You could pass the 24-hour value & massage it into 12-hour (AM/PM) during the import [or afterwards].

 

[PenelopeC]

I'm sorry - I didn't think to say that the time IS currently in the 24 hour format. I should have said "from 00:01.01 AM to 9:59.59 AM the times don't convert; they end up in the file import error table."

Thanks so much for trying to help...hopefully this starts a new tact???

PenelopeC
~~~>-/O~~~~~swimming right along

 

[RJFost]

When all else fails, try brute force- use Format(<time field>, &quot;hh:nn:ss&quot in your query (or export spec or whatever). That'll ensure that you get what you want- a 24-hour time w/ seconds (e.g., 20:33:40).

Alternately, to save work at query time, you could put a text field at the end of the record & drop in the 24-hour version of the misbehaving timestamp when the record's created.

 

[PenelopeC]

RJ -
I found my problem....it was that I opened the file before in Excel! Excel dropped the zeros. When I went back to the original file everything was fine.

The file is from someone else so I don't have any way to force the export. I was thinking I would have to do some manipulation in Excel before importing -- turns out I just needed some time away from the problem.

I did find a check box in the Advanced portion of the import wizard that says &quot;Date/Time has leading zeros&quot; that helped too.

Thanks for coming to my aid! I appreciate it very much.

PenelopeC
~~~>-/O~~~~~swimming right along

 

[officework13]

penelopeC
i am having trouble with importing dates from text format.
I have the 24:00 time format.
however the time is in text format & the time 113 represents 1:13 am, as well the time &quot;1&quot; represents 00:01 I am having trouble converting these numbers to time.

do you know of an easy way to do this. if so please advise.

 

[RJFost]

As a general rule, I try to avoid Excel as much as possible. I'll be the first to admit I don't know it very well (you could write everything I know about Excel in a matchbook with a laundry pen), but it always seems to cause me more problems than it's worth. Glad you found your answer.

 

[PenelopeC]

officework13,
The advanced tab of the import wizard has some great little check boxes. They are what I used to solve my problem. Read them carefully, there are many options.

RJ,
I'm kind of a software idiot savant...I just bumble around til I get it right. Excel ain't so bad once you know how to tame it. Thankfully, I can retain and recall what I learn -- it would take a much finer pen for me to fill the matchbook <grin>


PenelopeC
~~~>-/O~~~~~swimming right along

 

[RJFost]

I've been at this for 25+ years, so I'm just your garden-variety idiot. I suppose Excel's left a bad taste because so many business users regard it as the axis upon which the Earth turns. I've seen it used as a surrogate for every other Office program; when it (inevitably) blows up, I have to go fix it. It gets old after a while, trying to explain that it was never meant to have 250,000 rows dumped into it- that's what Access is for. Excel was meant for lots of columns over comparitively few rows; Access is just the other way 'round.

 

[PaulBricker]

Officework, have you tried the format
Format([Field],&quot;00:00&quot
In the debug window
Format(1,&quot;00:00&quot returns
00:01
Format(10,&quot;00:00&quot returns
00:10
Format(113,&quot;00:00&quot returns
1:13
Format(2113,&quot;00:00&quot returns
21:13

I'm not sure where that leaves you but if you don't have any &quot;:&quot; notations in your text, just import it and then try formatting once you get it to Access.

Paul

 

[officework13]

Paul
thanks
that worked by formatting the text.