problem with importing test file data

  • Thread starter Thread starter Tom
  • Start date Start date
T

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
 
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.
 
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.
 
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.
 
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.
Click to expand...

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.
 
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
 
Back
Top