Import from Filpac Merge File

The utility under Import>Import From Merge File was written to enable the easy import of other databases created by the Filpac Mail Merge process. The import file used by this utility is in FoxPro 2.6 format (otherwise known as "DBase" or DBF) in the structure enumerated below. This is one of the programs that we use to convert various files sent to us by customers. It is not intended to be a "do it yourself" import program because of the difficulty in converting other files and structures into a recognizable format and structure. In other words: don't try this at home unless you know what you're doing.

Important note: If you cannot easily follow the instructions below, or if you don't know how to make and restore a backup, then you probably should not be trying this, because it makes permanent changes to your Filpac data files. Your Filpac warranty specificially does not apply to data file problems caused by using the Merge Import feature of the program. You can always send your file to us and we'll convert it for only a small charge.

Creating your own merge file for import.

1. Make a backup of your current Filpac dataset, which is the collection of files in your FR folder with the extension *.ksh. The easiest way to do this is with PKZIP or WinZip. If you need to revert to your backup, you must run Data File Reorganization after you re-enter the program.

2. Create your import file. Create it in Excel, using the template provided on our website OR make it a DBF using the exact structure below. (Here's a hint: if you make a copy of LET.KSH or LET0.WRK (in the \fr\temp subfolder) and rename it to a .DBF extension, then you have successfully created this structure.)

3. Set up each record so that the NAMES are separated into last, first and middle. The import program does not include a name-separation utility.

4. Establish a CODE and TYPE for each record. Make sure it is a code recognized by your Filpac program. For example, if you're importing a list called INV ("Invite"), make sure the List Code has already been entered as a List Code in Filpac.

5. Each record should be entered with the proper KEY. If the KEY is P for PAC, the COMPANY field, will contain the PAC name. (If the KEY is I for Individual, the PREFIX, FIRSTNAME, MNAME, LASTNAME and SUFFIX fields should be filled.

6. Properly format the ITEMDATE field. For example, July 4, 2014 should be entered as "07/04/2014".

Don't worry about lower-case because the import program will convert from caps to lower case. Don't worry about filling in the county because the import program will try to assign a county from the zip code.

The File Number is immaterial because the import program will try to find a match on the basis of name and address. If it cannot find a match, it will add a new record. Which brings us to another issue: duplicates. The import program is not perfect and will create duplicates out of similar addresses. Before running this program, you should check the Last Number Assigned in Filpac (go to Data File Maintenance->Individuals and click on New Record to find the next file number), write it down, and then go to Tools->Duplicate Processing once the import process is finished to resolve any duplicates.

If the process fails despite your best efforts, then you can always re-load your backup before trying again. Make sure that you run Data File Reorganization after restoring your backup.

The fields listed in boldface below are required.


  No. Field       Width   Contents

      1  FILENUM      7   File Number or Vendor Code
      2  KEY          1   (I)ndividual, (P)AC/Company or (M) for                   Vendor
      3  PREFIX      20   "Mr.", "Mrs.", "Dr.", etc.
      4  FIRSTNAME   30   First Name
      5  MNAME       20   Middle Name
      6  LASTNAME    20   Last Name
      7  SUFFIX      10   "Jr.", "Sr.", "III", "MD"
      8  FULLNAME    50   Combines fields 3 through 7  (ignored by the import program)
      9  TITLE       30   Position or PAC/Company file Title
     10  GREETING    46   Personal greeting or salutation
     11  COMPANY     70   PAC/Company Name.  Disregard if KEY is "I".
     12  ADDRESS1    40   First Address Line
     13  ADDRESS2    34   Second Address Line (if any)
     14  CITY        22   City
     15  STATE        2   State
     16  ZIP         10   Zip Code in 99999-9999 format
     17  AMOUNT      12   999999999.99 format
     18  TYPE         1   ** see below
     19  ITEMCODE    10   Donor, Worker, Occupation, Contact or List Code, depending on TYPE  (see below)
     20  DESC        60   Code Description
     21  ITEMDATE    10   Code Date: MM/DD/YYYY format
     22  EMPLOYER    40   Employer (Individuals only)
     23  OCC         30   Occupation (Industry Code for Orgs.)

Note: all Filpac phone numbers will be formatted in the (999)999-9999 format. No need to format phone numbers beforehand, as long as they're in something recognizable, such as 999-999-9999 or 999-9999.

     24  PHONE1      13   Home or Primary phone number  
     25  PHONE2      13   Work phone (Individuals and PACs)
     26  PHONE3      13   Cell or Other phone (Individuals and PACs)
     27  PHONE4      13   Fax
     28  EMAIL1      60   EMail address
     29  COUNTY       3   County Code
     30  GEO1        20   Municipality
     31  GEO2         2   Ward
     32  GEO3        15   Precinct
     33  TRACT       11   Census Tract and Block: 999999.9999

Note: The remaining items are ignored by the import program.

     34  UDEF1       10   USER-DEFINED  (name and length vary)
     35  UDEF2        2   USER-DEFINED      "
     36  UDEF3        2   USER-DEFINED      "
     37  UDEF4        5   USER-DEFINED      "
     38  UDEF5        5   USER-DEFINED      "
     39  UTIL         8   Related Person in most cases
     40  POS          1   1 for Primary, 2 for Secondary
     41  HWM          1   Home, Work, or Mail        
     42  DP           2   Delivery Point   
     43  CHKDIGIT     1   Delivery Point Checkdigit
     44  CRRT         4   Carrier Route
     45  LOT          4   Line of Travel   
     46  LOT_ORD      1   Line of Travel Order 
     47  Nixie        5   Nixie Codes

     STANDARD LENGTH          789

Code Types (Field #18)
C Contributor
M Master file (In this case, the ITEMDATE field will contain the last update. The DESC and ITEMCODE fields will contain the occupation and occupation code.
W Worker
V Contact
G List