Janie Shuman <mailto:[log in to unmask]> wrote:
> We download a file of students from our Image database into EXCEL and
> from this file send emails.  Many of these emails are rejected for bad
> addresses.  We are trying to find a cost and time-efficient way of
> deleting the addresses from the database.  Is there a way to have
> rejected addresses feed into a file?  I was told that you can
> purchase software for this task, but that it is very expensive.
>
> At present they are printing the returned ones, using a "find"
> command in the EXCEL file, coding them to be deleted in the EXCEL
> file, and then looking them up on a screen that accesses the IMAGE
> database in order to delete them.  For various reasons, we need to
> find a better way.  I The EXCEL file allows only 1 person at a time,
> and the data entry people are not accustomed to EXCEL.  They are more
> comfortable with their usual screens which access the Image database.
> I explained that I could transfer the EXCEL file to the HP, and
> automatically delete the records from the database.  But they've
> asked for a screen on which to look them up using just the email
> address, which isn't a key.  And they'd like to be able to use only
> part of the email address if possible.  Presently we only have key
> lookups on our screens, and I'm not sure how to do this.
>
> If I could just get the rejected email addresses in a file, I'd be
> able to automate the solution entirely.
>
> Anyone have any ideas or knowledge of resources for this issue?
>
> THANKS!
>
> * To join/leave the list, search archives, change list settings, *
> * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *

Maybe this will help some... I wrote this a couple years ago with the
help of Paul at Robelle. It's designed to work on the MACS/Ecometry
databases and uses Suprtool heavily, but it might give you some ideas
for your situation.

Basically, the command file extract email addresses from the MACS
database, parses them a bit to ensure they "look" like valid email
addresses (i.e. have a reasonable format), then - if you set the
DODNSLOOKUP civar to true, will actually run nslookup on your system
and verify that the domain name of the address is valid. It creates a
list of the files with invalid addresses.

It doesn't actually try to DELIVER anything, but its an efficient way
to clean up obviously bogus addresses from your database - and it runs
right on your HP3000 - unattended.

  -Chris Bartram

Caveats:
 1) If you've got a LOT of addresses, be prepared to wait a while. Even a
   simple DNS lookup for every address can take a while...
 2) the script expects NSLOOKUP to be in UTIL.SYS... You can easily change
   that
 3) You'll need to change the "server 12.34.12.34" to the IP address of YOUR
   local DNS server (or remove the line if you have RESLVCNF.NET.SYS setup)
 4) Can't get around the suprtool stuff. Some of this you could do with
other
   tools - but not alot of it.
 5) the ::SETQUESTION `  line is to prevent VESoft's STREAMX from
erroneously
   parsing some of the data in the job stream. If you don't use STREAMX you
can
   safely remove that line.
 6) the SENDHTML command files are command files that come with NetMail/3000
to
   email an MPE file and automatically convert it to HTML. We use this to
email
   the result files at various steps; if you don't have NetMail/3000,
substitute
   any other process - or just save the files off under another name and
text the
   files in to a text editor.
 7) If you extract your email addresses WITH a corresponding image key value
(like
   student-id or whatever) you should be able to read the 'bademail' files
back in
   with another command file to delete/blank out/flag the student records
with bad
   email addresses.

--------------------------------begin
script---------------------------------
::SETQUESTION `
!JOB fixemail,user.acct
!
!setvar DODNSLOOKUP FALSE
!
!continue
!purge bademail > $NULL
!SUPR
BA macord,1,DOALL
get cust-xref
def email,xref-no[3],47
def emtype,xref-no[1],2
if emtype='EM','EU' and &
 (email=='00000@' OR email=='?~' OR email=='??~' or email=='???~' &
 OR email=='????~' OR email=='?????~')
extract cust-edp
extract email
OUTPUT bademail,ascii
DELETE
XEQ
EXIT
!IF FINFO("BADEMAIL","EOF")>0 THEN
!  sendhtml.xeq.sys ^macsl2.data.sys,,,BADEMAIL,&
!    'Junk entered in MACS Email Addresses - deleted'
!ENDIF
!continue
!purge bademail > $NULL
!comment  email == '?@,?@' or not (email == [log in to unmask] or email == [log in to unmask]))
!SUPR
BA macord,1,DOALL
get cust-xref
def email,xref-no[3],47
def emtype,xref-no[1],2
if emtype='EM' and (email><'?@&@[log in to unmask]@' or email == '?@ ?@' or &
 email == '?@,?@' or not (email == [log in to unmask] or email == [log in to unmask]))
extract "Cust#",cust-edp," email:",email
numrecs 100000
set squeeze off
OUTPUT bademail,ascii
XEQ
EXIT
!
!comment Begin phase 2 - extract all "good" email addresses from the
!comment database and actually verify the domain names via DNS lookups
!
!comment  1) find all the 'good' email addresses, build a file ready for
!comment     testing
!
!IF !DODNSLOOKUP THEN
!  SUPR
BA macord,1,DOALL
get cust-xref
def custz9,1,9,display
def email,xref-no[3],47
def emtype,xref-no[1],2
if emtype='EM','EU' and email=='?@&@[log in to unmask]@' and email >< '?@ ?@'
extract 'checkns "',email, '",' ,custz9 = cust-edp
OUTPUT mylookup,temp
EXIT
!
!  comment  2) create the checkns command file which does the NSLOOKUP
!
!
!  SUPR
in *;out checkns,temp;exit
PARM EMAIL_NAME = "[log in to unmask]" , custno="0"
SETVAR EMAIL rtrim("!EMAIL_NAME")
SETVAR ATPOS, POS("@","!EMAIL")
IF ATPOS = 0 THEN
   ECHO BAD EMAIL FORMAT ERROR -1:  missing @ sign
   SETVAR EMAIL_ERR,-1
   ESCAPE
ENDIF
SETVAR URLPOS,ATPOS+1
SETVAR domain,RTRIM(STR("!EMAIL",!URLPOS,80))

setvar hpautocont TRUE
purge NSRES > $NULL
purge NSCOMM > $NULL
purge NSINX > $NULL
purge NSOUTX > $NULL
purge NSERRX > $NULL

echo set type=any                 > NSINX
echo server 12.34.12.34          >> NSINX
echo !domain                     >> NSINX

save NSINX

echo  /SYS/UTIL/NSLOOKUP !<NSINX !>NSOUTX 2!>NSERRX  >NSCOMM
save NSCOMM

xeq sh.hpbin.sys "./NSCOMM"

grep.hpbin.sys "'Non-existent ' NSERRX" > NSRES
grep.hpbin.sys "'Unrecognized command: ' NSOUTX" >> NSRES

setvar _okdomain TRUE
if finfo("NSRES","exists") then
  if finfo("NSRES","EOF")>0 then
    setvar _okdomain FALSE
  endif
endif

if not !_okdomain then
  echo Cust#!custno email:!EMAIL
endif

purge NSRES > $NULL
purge NSCOMM > $NULL
purge NSINX > $NULL
purge NSOUTX > $NULL
purge NSERRX > $NULL
setvar hpautocont TRUE
!eod
!
!  comment   3) Invoke the mylookup file we created in the first task
!
!  file bad=bademail,old;acc=append
!  mylookup > *BAD
!ENDIF
!
!IF FINFO("BADEMAIL","EOF")>0 THEN
!  sendhtml.xeq.sys ^macsl2.data.sys,,,BADEMAIL,&
!    'Invalid Email Addresses - need to be corrected'
!ENDIF
!EOJ

* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *