Date: Tue, 17 Nov 1992  20:43:04 EST
From: FZC@CU.NIH.GOV
Newsgroups: comp.dcom.telecom
Subject: DBASE Program to Convert Area Codes
Reply-To: TDARCOS@MCIMAIL.COM
Organization: TELECOM Digest
X-Submissions-To: telecom@eecs.nwu.edu
X-Administrivia-To: telecom-request@eecs.nwu.edu
X-Telecom-Digest: Volume 12, Issue 859, Message 6 of 7

There are a lot of places using DBASE or its clones to handle database
work, so I thought I'd include the program I use to convert our
organization's database whenever an area code splits.  It's generic
enough that I can run it against our database and update all changed
records.  I thought other people might be able to use it.


Paul Robinson


The following is a procedure in DBASE that I wrote to convert
telephone numbers from one area code to another.  Some people on the
list may be able to use it, so here it is.  This was created for a
government agency so it is in the public domain.


     --- File CONVPHN.DBF --- Cut here ---

* DBASE III PROGRAM TO CONVERT TELEPHONE NUMBERS
* WHICH ARE CONVERTED FROM AN OLD AREA CODE NUMBER TO THE
* NEW ONE.  ALSO CHECKS THAT STATES LISTED FOR SPECIFIC
* ADDRESSES ARE VALID. INACCURATE NUMBERS ARE NOT CHANGED.
*
* REQUIRES THE PRE-CREATED "PHONETAB" DATABASE FILE WHICH
* CONTAINS ALL THE PREFIXES WHICH ARE CHANGING.  THIS IS
* A DBASE FILE CONSISTING OF THE OLD AREA CODE AND PREFIX
* AND THE NEW AREA CODE AND PREFIX REPLACING IT.  THE PREFIX
* MAY BE THE SAME OR IT MAY BE DIFFERENT.
*
* BASED ON ORIGINAL PROGRAM BY PAUL W. ROBINSON, 8/9/90, 9/11/90
* WHICH CHANGED NUMBERS IN THE NATIONAL CAPITAL AREA AND IGNORED
* NON 703 VIRGINIA NUMBERS
*
* THE NEED FOR THIS PROGRAM OCCURRED WHEN AT&T ANNOUNCED AN
* IMMEDIATE CHANGE TO CERTAIN TEXAS AND NEW JERSEY AREA CODES,
* AND THUS PHONE NUMBERS IN SOME EXCHANGES NEEDED TO BE CHANGED
* THIS WILL ALSO BE OF USE WHEN THE NEW 410 AREA CODE COMES INTO
* EFFECT IN MARYLAND.
*
* THIS REQUIRES YOU MAKE A DATA BASE LISTING THE OLD NUMBERS BY
* OLD AREA CODE AND PREFIX, AND THE NEW NUMBERS REPLACING THOSE BY
* AREA CODE AND PREFIX.  THIS CAN BE DONE WITH A SIMPLE TEXT
* FILE MERGED INTO A DATA BASE USING THE IMPORT FUNCTION.
* THIS FILE MUST BE CALLED 'PHONETAB', IT CARRIES 3 FIELDS:
* 'OLD', 'AC', 'PFX'  AND MUST BE INDEXED ON 'OLD'
* TO FILE 'AC'.
*
* IT IS ASSUMED THE FILE TO BE EDITED 'HOME' IS INDEXED USING
* 'NAME', AND CONTAINS THE FIELD OF 'STATE' AND 'PHONE' WHERE
* PHONE CONTAINS 12 DIGITS NPA-NXX-XXXX IF THE NUMBER IS OUT
* OF THE AREA CODE.
*
* PAUL W ROBINSON  E-Mail: TDARCOS@MCIMAIL.COM   6-MAY-1991
* This program is in the Public Domain.
*

CLEAR
STORE .F. TO DEBUG
SET TALK OFF
SET ECHO OFF

SET PRINT OFF
STORE         '$MDDCVATNALAKAZARCACOCTDEFLGAHIIDILINIAKS'   TO STATES
STORE  STATES+'KYLAMEMAMIMNMSMOMTNENVNHNJNMNYNCNDOHOKOR' TO STATES
STORE  STATES+'PARISCSDTXUTVTWAWVWIWYCZ' TO STATES
* NOTE: TENNESSEE (TN) MUST APPEAR EARLY BECAUSE OTHERWISE IT
*       WILL BE ERRONEOUSLY FOUND BETWEEN MONTANA (MT)
*       AND NEBRASKA (NE)

* ADD OUR OWN ABBREVIATIONS FOR SPECIAL CASES
STORE  STATES+'**Z1Z2Z3Z4Z5Z6Z7Z8Z9' TO STATES

SELECT 3
USE PHONETAB INDEX AC
GO TOP
SELECT 2
USE HOME INDEX  NAME, XPHONE
GO TOP
DO WHILE .NOT. EOF()

* CHECK THAT THE STATE NAME IS OKAY.

    STORE TRIM(STATE)      TO S
    STORE TRIM(HOME_PHONE) TO P
    STORE '   '            TO NPA
    STORE '   '            TO NXX
    STORE '    '           TO XXXX
    STORE '      '         TO L
    I = AT(S,STATES)/2
    IF I<>INT(I) .OR. I<1
      IF .NOT. DEBUG
         SET PRINT ON
      ENDIF
      ? '***BAD STATE NAME:'
      DISPLAY
      SET PRINT OFF
    ELSE
* STATE NAME IS OKAY, CHECK AREA CODE
     IF LEN(P)=12
       STORE LEFT(P,3) TO NPA
       STORE SUBSTR(P,5,3) TO NXX
       STORE RIGHT(P,4) TO XXXX
       STORE NPA+NXX TO L
       SELECT 3
       GO TOP
       FIND &L
       IF .NOT. EOF()
          STORE STR(AC,3)  TO NPA
          STORE STR(PFX,3) TO NXX
          STORE NPA+'-'+NXX+'-'+XXXX TO P
          SELECT 2
          IF .NOT. DEBUG
             SET PRINT ON
          ENDIF
          ?  '  CHANGING RECORD '
          DISPLAY
          REPLACE HOME_PHONE WITH P
          ?  '  TO '
          DISPLAY
          SET PRINT OFF
       ENDIF
     ENDIF
   ENDIF
   SET ECHO OFF
   SET TALK OFF
   SELECT 2
   SKIP
ENDDO
CLOSE ALL

   --- Cut here

The following shows the format of the DBF file PHONETAB.  After
creating this file, you will need to do "INDEX ON OLD TO AC" in order
to index this file to make the above procedure work.

 . display structure
Structure for database: C:phonetab.dbf
Number of data records:     376
Date of last update   : 11/16/92
Field  Field Name  Type       Width    Dec
    1  OLD         Numeric        6
    2  AC          Numeric        3
    3  PFX         Numeric        3
** Total **                      13

Now the first three and last three records of the PHONETAB.DBF file.

    OLD  AC PFX
 301205 410 205
 301208 410 208
 301221 410 221

 - Circa 370 records deleted

 301996 410 996
 301997 410 997
 301998 410 998
 301999 410 999

The following is the complete set of codes which have changed
from Area Code 301 to 410.

List of Prefixes changed from 301-xxx to 410-xxx

  205  208  221  222  224  225  226  228  232  233  234  235  237  239
  242  243  244  247  250  252  254  255  256  257  260  263  265  266
  267  268  269  272  273  275  276  278  280  281  282  284  285  287
  288  289  290  291  296  298  307  312  313  316  319  321  323  325
  326  327  328  329  332  333  335  337  338  339  342  343  346  347
  348  351  352  354  355  356  357  358  360  361  362  363  364  366
  367  368  370  374  376  377  378  379  381  382  383  385  388  389
  391  392  393  396  397  398  425  426  429  430  433  435  437  438
  440  442  444  446  448  450  451  452  455  456  457  458  461  462
  465  466  467  471  472  476  477  479  482  483  484  485  486  488
  489  494  515  521  522  523  524  525  526  527  528  529  531  532
  533  534  535  536  537  538  539  541  542  543  544  546  547  548
  549  550  551  553  554  556  557  558  560  561  562  563  566  569
  573  574  575  576  578  581  583  584  586  591  592  594  597  602
  605  612  613  623  624  625  626  628  631  632  633  634  635  636
  637  638  639  641  642  643  644  646  647  648  651  653  655  658
  659  661  664  665  666  667  668  669  671  672  673  674  675  676
  677  679  682  683  684  685  686  687  691  692  693  712  719  720
  721  723  726  727  728  730  732  734  740  741  742  744  745  747
  748  749  750  751  752  754  755  756  757  758  760  761  764  765
  766  768  771  775  778  780  781  783  784  785  787  788  789  792
  793  795  796  798  799  808  820  821  822  823  825  827  828  830
  832  833  835  836  837  838  841  848  849  850  857  859  860  861
  866  867  873  875  876  877  879  880  882  883  885  886  887  889
  892  893  896  906  920  922  923  928  931  938  939  941  943  944
  945  947  955  956  957  960  962  964  965  966  968  969  971  974
  978  979  987  988  991  992  993  995  996  997  998  999


Paul Robinson -- TDARCOS@MCIMAIL.COM
#INCLUDE <Standard\disclaimers.h>