Wrong EOF when linking table

Hello:

I'm trying to make this process for an end-user as smooth as possible. I need to be able to skip the wrong EOF.
1. The user exports 30 e-mails received daily from a MainFrame Computer in Lotus Notes to a .txt file.
    (some of the e-mails contain hidden attachments that cannot not be removed and are translated by notepad into a
      bunch of characters.)  
2. Once the data is in the text file ( approx 6000 lines ), the text file is linked to Access.
    Here comes the problem:
    Access only displays 89 rows, since it finds characters like "€è²3þÿÝÝúÿÝÝúÿÝÝ€4þÿÿÿUS" and assumes that EOF
     has been reached.
    I don't want the user to go to the text file and delete all lines where characters like those above are present, I want to be
   able to skip them all and see the 6000 rows in an Access linked table, so I can proceed with step 3.
3. A Pass-Through query will insert all rows into ORACLE for further manipulation.  

Thanks a lot!
LVL 23
paquicubaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve BinkCommented:
You can attempt to load the text file in Access by opening it as a binary file.  Text files are traditionally terminated by a Chr(26), or Ctrl-Z character.  Since #26 can show up anywhere inside binary data, this obviously causes some problems.

If you read the file as binary, you will have to either know a set structure of the file (this field is 10 bytes, this field is 15, etc.) or be able to dynamically determine the structure as you read it (for example, manually reading FoxPro data files).  The up-side is that you can read ANY data from the file, and it will not assume #26 is an EOF.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nico5038Commented:
Hmm, as far as I recall it are two or three low values that mark the end of a textfile (#00 #00), but anyway I guess a routine that's replacing the characters other then a-z, A-Z, 0-9 and CarriageReturn (CHR(13)) and LineFeed (CHR(10)) with e.g. a space.

Probably best to use a VB program to process this as fast as possible, but you could also try VBA.

Nic;o)
0
Steve BinkCommented:
DOS always used #26.  I used to create any data files for my Pascal apps with my text company name, followed by #26.  That way if the user tried a DOS "type" command, they saw what the file was for without all the high-bit crap all over their screen.  :)  Where have you encountered the #0#0?  I seem to have some faint recollection of that scheme, but can't remember any details.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

paquicubaAuthor Commented:
This is an example of the data:

Received:  from cliff.ovnt.com ([172.16.9.146])          by otc_comm1.ovnt.com (Lotus Domino Release 5.0.12)          with ESMTP id 2004112312170376:30619 ;          Tue, 23 Nov 2004 12:17:03 -0500
"Received:  from [172.31.253.154] (helo=eval34..com)      by cliff.ovnt.com with smtp (Exim 3.36 #1)      id 1CWeGg-0003NT-01      for Service@.com; Tue, 23 Nov 2004 12:15:54 -0500"
"Received:  from loganmail.com (loganmail.com [207.135.143.106])      by eval34..com (8.12.11/8.12.11) with SMTP id iANHFqZ5019733      for <Service@.com>; Tue, 23 Nov 2004 12:15:52 -0500"
"Received:  from hostfax3 ([172.27.12.212])      by loganmail.com (8.11.6/8.11.6) with ESMTP id iANHFs704171      for <Service@.com>; Tue, 23 Nov 2004 10:15:58 -0700"
PostedDate:  11/23/2004 12:15:58 PM
$MessageID:  <200411231715.iANHFs704171@loganmail.com>
Subject:  POSTAGE REPORT
MIME_Version:  1.0
$Mailer:  Captaris EDocumentDelivery
X_Mailer_Version:  8.6.5.99
From:  <LoganPSA@rrd.com>
SendTo:  <Service@.com>
Importance:  1
X_Proofpoint_Spam_Details:  rule=notspam policy= score=0 mlx=0 adultscore=3 adjust=0 engine=2.5.0-04111800 definitions=2.5.0-04111800
$MIMETrack:  Itemize by SMTP Server on OTC_COMM1/SRVR/(Release 5.0.12  |February 13, 2003) at 11/23/2004 12:17:03 PM,MIME-CD by Notes Client on Alex F. Rodriguez/FinAcct/(Release 5.0.8 |June 18, 2001) at 11/30/2004 09:54:49 AM,MIME-CD comp
SMTPOriginator:  LoganPSA@rrd.com
$UpdatedBy:  ,CN=OTC_COMM1/OU=SRVR/O=
RouteServers:  CN=OTC_COMM1/OU=SRVR/O=,CN=RLNTM01/OU=SRVR/O=
RouteTimes:  11/23/2004 12:17:03 PM-11/23/2004 12:17:04 PM,11/23/2004 12:15:55 PM-11/23/2004 12:15:56 PM
$Orig:
Categories:
$Revisions:
$MsgTrackFlags:  0
DeliveredDate:  11/23/2004 12:15:56 PM
$FolderRef:
$FolderRefID:  3377
$FolderRefFlags:  0








PAGE:     1                              MANIFEST
POSTAGE COMPUTATION              SPLIT:(R) 1            1ST CLASS
OVT1233W.FLT
RUN DATE:
11/23/2004
CLASS: FIRST       LTRS
RUN TIME: 10:14                      MOORE WALLACE BCS
                    ENTRY POINT ZIP: 60199
RELEASE:  7.30.27.G                                MAILER'S
CHOICE                 ENTRY POINT: CAROL STREAM, IL  60
SUPER FILE: 3.2.3 08/15/2004         TRANSPORTATION
CO.                     PERMIT IMPRINT
SINGLE PIECE RATE                  1000 SEMMES AVE.


POSTAGE SUMMARY REPORT
PERMIT NUMBER:  533
(MANIFEST)
NON-PROFIT:      N
===========================================================================
=========================================================

WEIGHT OF A SINGLE PIECE                BEGINNING SERIAL #         1
NON IDENTICAL                      ENDING SERIAL #          196
TOTAL PIECES             196
MAILING DATE:
__/__/__
===========================================================================
=========================================================
POSTAGE COMPUTATION
===========================================================================
=========================================================


0.1546 LBS --   2.473 OZS --
===========================================================================
=========================================================
TOTAL     POSTAGE
POSTAGE
PIECES        RATE
CHARGES
SINGLE-PIECE RATE (SP)                 43       0.830
$35.690
-----------
--------------
43
-----------
--------------
TOTAL POSTAGE DUE FOR MAILING
$35.690


1-FT TRAYS
2-FT TRAYS                1
TOTAL LTR. TRAYS
1
"
PAGE:     2                              MANIFEST"
POSTAGE COMPUTATION      3þÿúÿÝÝ.ÝݘJÔàûaic7KK

"      €è²3þÿÝÝúÿÝÝúÿÝÝ€4þÿÿÿUS"
0
Steve BinkCommented:
Well, that's not very user friendly, now is it?  :)  

Essentially, what you want is going to be VERY difficult to get right because the format of the emails can be different from item to item.  Not all email will have every X_tag item or $Flag items.  That, combined with the variety of characters and formats in each line itself, is going to make this virtually impossible to work 100% of the time.  Reading it as a binary file may work, but you'd have to invest much more time in making it work for even a small number of instances than I would want to recommend.

My recommendation is to have the client receiving the mail export it to a format you can manipulate easily, if at all possible.  If you are using Outlook, this is not a big problem at all...
0
paquicubaAuthor Commented:
Thanks a lot guys for your answers. I'll try in the Lotus Notes forum to see how I can eliminate the attachments when exporting the e-mails.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.