problem with importing test file data

[Tom]

I have a dozen or so text file which I need to import. All but one are error
free. I have a text file with a date and time in the 5th & 6th fields
respectively. It is both these fields that Access chokes upon. I thought that
perhaps it was because in all but one case the *preceding* line had spaces for
the date & time. (Although thousands of rows have spaces for the date and time,
and don't cause any problem.) In the one remaining case, the *following* row
had the spaces for the date & time.

I've created the spec file for importing into an existing table. Note that my
text file is some 56,000 records in size. And only *these* records Access
claims are bad. And in all cases, Access reports *both the date and time* are
bad (conversion error).

Here's my "bad" rows of data:

"S","101158001
","01","06","052599","052599081631","00099","M","F","00000064.0000","00000064.0000","0001.0000","00000000.0000","01","000000000","
5.81","0"," .00"," .00"," .00","END="
"S","101158001
","03","06","052599","052599081631","00099","M","F","00003732.0000","00003798.0000","0001.0000","00000066.0000","12","000000066","
..00","B"," .00"," .00"," .00","END="
"S","101206004
","01","08","081399","081399153715","00099","M","F","00000467.0000","00000469.0000","0001.0000","00000002.0000","01","000000002","
5.81","0"," .00"," .00"," .00","END="
"S","101206004
","03","08","081399","081399153715","00099","M","F","00017256.0000","00017874.0000","0001.0000","00000618.0000","12","000000618","
47.54","0"," .00"," 3.33"," .00","END="
"S","101220507
","01","07","072199","072199085906","00099","M","F","00000108.0000","00000118.0000","0001.0000","00000010.0000","01","000000010","
15.05","0"," .00"," .00"," .00","END="
"S","101220507
","03","07","072199","072199085906","00099","M","F","00030870.0000","00031539.0000","0001.0000","00000669.0000","12","000000669","
52.04","0"," .00"," 3.64"," .00","END="
"S","101234009
","01","08","073099","073099110702","00099","M","F","00000969.0000","00000973.0000","0001.0000","00000004.0000","01","000000004","
7.13","0"," .00"," .00"," .00","END="
"S","101234009
","03","08","073099","073099110702","00099","M","F","00036080.0000","00037359.0000","0001.0000","00001279.0000","12","000001279","
109.16","0"," .00"," 7.64"," .00","END="
"S","101234010
","01","08","082499","082499165509","00099","M","F","00000973.0000","00000975.0000","0001.0000","00000002.0000","01","000000002","
5.81","0"," .00"," .00"," .00","END="
"S","101234010
","03","08","082499","082499165509","00099","M","F","00037359.0000","00037902.0000","0001.0000","00000543.0000","12","000000543","
42.26","0"," .00"," 2.96"," .00","END="
"S","101238001
","01","11","102996","102996083605","00099","M","F","00000069.0000","00000071.0000","0001.0000","00000002.0000","01","000000002","
5.81","0"," .00"," .00"," .00","END="
"S","101238001
","03","11","102996","102996083605","00099","M","F","00006382.0000","00006654.0000","0001.0000","00000272.0000","11","000000272","
24.97","0"," .00"," 1.50"," .00","END="
"S","101242002
","01","07","071399","071399155659","00099","M","F","00000017.0000","00000018.0000","0001.0000","00000001.0000","01","000000001","
5.81","0"," .00"," .00"," .00","END="
"S","101242002
","03","07","071399","071399155659","00099","M","F","00004207.0000","00004582.0000","0001.0000","00000375.0000","12","000000375","
31.39","0"," .00"," 2.20"," .00","END="
"S","101260007
","01","07","072099","072099094603","00099","M","F","00000811.0000","00000817.0000","0001.0000","00000006.0000","01","000000006","
9.77","0"," .00"," .00"," .00","END="
"S","101266010
","03","09","092199","092199092430","00099","M","F","00007358.0000","00007516.0000","0001.0000","00000158.0000","12","000000158","
17.34","0"," .00"," 1.21"," .00","END="
"S","101304003
","01","03","031896","031896074925","00099","M","F","00000412.0000","00000412.0000","0001.0000","00000000.0000","01","000000000","
11.50","0"," .00"," .00"," .00","END="
"S","101306009
","03","03","031896","031896074925","00099","M","F","00018516.0000","00018645.0000","0001.0000","00000129.0000","11","000000129","
16.24","0"," .00"," .97"," .00","END="
"S","101336007
","01","01","011996","011996121339","00099","M","F","00000858.0000","00000859.0000","0001.0000","00000001.0000","01","000000001","
5.75","0"," .00"," .00"," .00","END="
"S","101336007
","03","01","011996","011996121339","00099","M","F","00003035.0000","00003066.0000","0001.0000","00000031.0000","11","000000031","
9.35","0"," .00"," .56"," .00","END="
"S","101336009
","01","01","011698","011698131239","00099","M","F","00000868.0000","00000868.0000","0001.0000","00000000.0000","01","000000000","
5.81","0"," .00"," .00"," .00","END="
"S","101336009
","03","01","011698","011698131239","00099","M","F","00005080.0000","00005105.0000","0001.0000","00000025.0000","11","000000025","
8.81","0"," .00"," .62"," .00","END="
"S","101530005
","01","07","071999","071999102807","00099","M","F","00000424.0000","00000438.0000","0001.0000","00000014.0000","01","000000014","
20.33","0"," .00"," .00"," .00","END="
"S","101530005
","03","07","071999","071999102807","00099","M","F","00051570.0000","00053195.0000","0001.0000","00001625.0000","12","000001625","
214.46","B"," .00"," 15.01"," .00","END="
"S","101530006
","01","09","092099","092099092430","00099","M","F","00000439.0000","00000440.0000","0001.0000","00000001.0000","01","000000001","
5.81","0"," .00"," .00"," .00","END="
"S","101530006
","03","09","092099","092099092430","00099","M","F","00005257.0000","00007268.0000","0001.0000","00002011.0000","12","000002011","
182.37","0"," .00"," 12.77"," .00","END="
"S","101576014
","03","09","091499","091499150743","00099","M","F","00008259.0000","00008898.0000","0001.0000","00000639.0000","12","000000639","
49.39","0"," .00"," 3.46"," .00","END="
"S","101636008
","01","08","081696","081696081029","00099","M","F","00000039.0000","00000040.0000","0001.0000","00000001.0000","01","000000001","
5.81","0"," .00"," .00"," .00","END="
"S","101636008
","03","08","081696","081696081029","00099","M","F","00018021.0000","00018047.0000","0001.0000","00000026.0000","12","000000026","
8.79","0"," .00"," .53"," .00","END="
"S","101638002
","01","01","011696","011696094635","00099","M","F","00000245.0000","00000250.0000","0001.0000","00000005.0000","01","000000005","
8.39","0"," .00"," .00"," .00","END="
"S","101638002
","03","01","011696","011696094635","00099","M","F","00001443.0000","00002626.0000","0001.0000","00001183.0000","11","000001183","
87.73","0"," .00"," 5.26"," .00","END="
"S","101654012
","03","05","051399","051399154441","00099","M","F","00032573.0000","00032784.0000","0001.0000","00000211.0000","11","000000211","
20.98","0"," .00"," 1.47"," .00","END="
"S","101656005
","03","09","083099","083099101528","00099","M","F","00006938.0000","00008009.0000","0001.0000","00001071.0000","12","000001071","
88.35","0"," .00"," 6.18"," .00","END="
"S","101456006
","03","09","082698","082698073447","00099","M","0","00000000.0000","00000000.0000","0001.0000","00000000.0000","12","000000000","
7.11","0"," .00"," .50"," .00","END="

I've isolated these 'bad' rows, placing them in a separate text file, and tried
to import it. (In case Access was erroneously reporting what rows had
problems.) Every row failed the import test. So *apparently*, there *is*
something about these records. But *what* that is, I can't see.

I'd appreciate any help you can provide. I'd *really* like to find out what the
problem is, and fix it...if I can. I have a *lot* of this old data I need to
import, and don't really want to lose any of it. If possible.

Thanks in advance,

Tom

 

[Klatuu]

The problem is Access can't convert these fields to a date. A fairly simple
workaround would be to import from the text file into a temporary file that
has those fields identified as Text. Then you are going to have to
manipulate those fields into date data types.
for demo purposes, lets say x = field 6 in your first row ("052599081631").
Here is a couple of functions that will convert it into an Access date/time
field:

Dt = DateSerial(mid(x,4,2),left(x,2),mid(x,3,2))
Tm = timeserial(mid(x,7,2),mid(x,9,2),right(x,2))
Dtm = Dt + Tm

Dtm will now be 5/25/1959 8:16:31 AM and acceptable as a date field.

 

[Tom]

Well, gee. Hmmm... Okay, I can see what you are suggesting & how it would
work. So I guess from a "pragmatic" standpoint, I could do as you recommend,
and get the job done. (And probably will do so.)

....However...

Let's leave being pragmatic behind, for the moment, shall we?

Why should "052599081631" (which I equate to 05/25/99 08:16:31), be a problem?
It's a valid date and time. Just as "090899142849" (09/08/99 14:28:49) is.
(And this converted successfully.) And unless I'm wrong, which I don't think I
am, all the "rejected" dates and times are "good" (valid). This being the case,
isn't this really a (gulp) "bug" in Access?

I would like to know *why* Access has a problem with this data, because as far
as *I* can tell, it is problem free.

 

[Klatuu]

I really don't know. Maybe it has to do with planetary and lunar alignment?
It would seem that if one comes in correctly, they should all come in
correctly. I did try using "090899142849" and it would not convert to a
date, either.
It does seem Superfluous, but as a matter of practice, I alway import via a
temporary file and validate and format the data into the permanent table. I
guess I have had to go through this more than once.

 

[John Nurick]

Why should "052599081631" (which I equate to 05/25/99 08:16:31), be a problem?
It's a valid date and time. Just as "090899142849" (09/08/99 14:28:49) is.
(And this converted successfully.) And unless I'm wrong, which I don't think I
am, all the "rejected" dates and times are "good" (valid). This being the case,
isn't this really a (gulp) "bug" in Access?

I would like to know *why* Access has a problem with this data, because as far
as *I* can tell, it is problem free.

It doesn't look problem-free to me.

"090899142849" could be
9 August 1999, 14:28:49
8 September 1999, 14:28:49

"052599081631" could be
25 May 1999, 08:16:31
25 May 9908, 16:31

If you want to be sure that these strings will be interpreted the way
they appear to you, you need to take control of the import process as
Klatuu suggests.

 

[Tom]

1.) I've already copied my table to a "temp" version with the dates redefined
as text, so I'm doing as suggested.

2.) Now about your "could be"s...

Since I created a spec file for the importation process, where I've TOLD ACCESS
that the DATE FORMAT is "MMDDYY", it darn well had better be using my spec.
Otherwise, why did I bother?

I would agree with you...IF...I wasn't using a spec file.

< < < time out - let me check > > >

Okay. Would you believe that we were *both* right? (well...sort of.)

I found the source of my problem. It was...(gulp)...my spec file. Instead of
the date format being "MMDDYY", as I mentioned above, it *was* in fact,
"DDMMYY". Once I fixed this, and tried the import again, every record came in
successfully. (Check, check and...check again! )


I thank you both for your help,

Tom