?
Solved

Wrong EOF when linking table

Posted on 2004-11-30
6
Medium Priority
?
482 Views
Last Modified: 2008-01-09
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!
0
Comment
Question by:paquicuba
  • 3
  • 2
6 Comments
 
LVL 51

Accepted Solution

by:
Steve Bink earned 1200 total points
ID: 12707982
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
 
LVL 54

Assisted Solution

by:nico5038
nico5038 earned 800 total points
ID: 12708123
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12708249
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 23

Author Comment

by:paquicuba
ID: 12708587
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12709429
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
 
LVL 23

Author Comment

by:paquicuba
ID: 12718654
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question