APPLICATION NOTE #159 SORTING MAIL MERGE DATA FILES Rev. 01 Since you rarely enter records into a mail merge data file in a logical order (such as alphabetically), you may find it helpful or necessary to sort entries. This application note describes the procedures for rearranging entries either alphabetically by last name or numerically by zip code. INTRODUCTION To sort your mail merge data file into alphabetical or numerical order, you must perform the following steps: 1. Create a mail merge data file as described in the XyWrite III Plus Reference Guide. 2. Modify the data file so that each record is on a single line. 3. Insert a tab character before the word on which you want to base the sort (e.g., the last name or the zip code ). 4. Use the DEFAULT command to change the SK (Sort Key) setting. This application note gives you the correct SK setting for each procedure as described. Refer to Note #4 for more information on using the SORT command and the SK setting. 5. Sort the data file. 6. Return the data file to its original format so that you can use it with the Mail Merge feature. The following sections describe this procedure in detail. Note #1 Default Separators. The procedures in this application note assume that you used the default mail merge characters to separate fields and records (one and two carriage returns, respectively) in your data file. If you used some other characters, adjust these procedures accordingly. MODIFYING THE DATA FILE When you create your mail merge data file, you end up with records that are several lines long. Since XyWrite's sorting feature sorts each line of a file or a defined block, you need to change the records so that each one is on a single line. To turn multiple-line records into single lines, you must change the single return characters between fields to a unique character that is not part of any record. Next, you must change the double return characters between records to a single return. You do this with a series of change commands. 1. Make a copy of your mail merge data file and display the copy on the screen. 2. Change all instances of double return characters to some unique character so that it will not be affected when you change the single return characters. This example procedure uses the @ character, but you can use any character, as long as it does not appear anywhere else in your data file. Type: F5ci / Press: Ctrl (twice) Type: /@/ Result: The characters on the command line look like this: ci /{ASC-27}{ASC-27}/@/ Press: 3. Change every remaining return character to a unique character to mark the places between fields. This example procedure uses the # character, but you can use any character, as long as it does not appear anywhere else in your data file. Type: F5ci / Press: Ctrl Type: /#/ Result: The characters on the command line look like this: ci /{ASC-27}/#/ Press: 4. Change the @ character (or whatever character you used in Step 2) back to a single return character to separate records. Type: F5ci /@/ Press: Ctrl Type: / Result: The characters on the command line look like this: ci /@/{ASC-27}/ Press: This is an example of a data file before the fields were prepared for sorting: This is an example of a data file after the fields were prepared for sorting: Susan Faber#100 Broadway#Boston, MA 01270 Gail Pentel#27 Main St#Melrose, MA 02176 Rosemary Dixon#275 Tremont Street#Nutting Lake, MA 01730 Chris Cross#31 Ellsworth St#Bedford, MA 01730 Ruth Parker#44 Manning Road#Billerica, MA 01821 SORTING THE DATA FILE ALPHABETICALLY Use the following procedure to sort your data file alphabetically by last name. 1. Prepare the data file for sorting as described in "Modifying the Data File." 2. Place a tab character ({ASC-10}) before each last name in the file. The simplest way to do this is to put a tab character after each space. (This results in many extra tab characters, but they will not interfere with sorting.) Type: F5ci / Press: Space (once) Type: / Press: Space (once) Press: Tab Type: / Result: The characters on the command line look like this: ci / / {ASC-10}/ Press: This is an example of a data file ready to be sorted alphabetically by last name. Susan Faber#100 Broadway#Boston, MA 02170 Gail Pentel#27 Main St#Melrose, MA 02176 Rosemary Dixon#275 Tremont Street#Nutting Lake, MA 01730 Chris Cross#31 Ellsworth St#Bedford, MA 01730 Ruth Parker#44 Manning Road#Billerica, MA 01821 Note #2 Record Format. The next step assumes that the names in your data file are entered in the format firstname surname without titles or middle initials. If you have used titles or middle initials, then you must use a different SK (Sort Key) setting than the one shown. First, be sure that you have used a consistent format in all your records; if one record contains a title (or initial), then all the entries must contain it. Next, change the last value in the SK command so that it reflects the number of tab characters between the beginning of the line and the surname. For example, if the records are set up as: Ms. Chris C. Cross#31 Ellsworth Street#Bedford,MA 01730 then the correct SK setting would be DEFAULT SK=8,80,3 (there is a tab in each of the three spaces that appear between the beginning of the line and the surname "Cross"). 3. Use the following default setting to sort the name after the first tab character: Type: F5default sk=8,80,1 4. Use the SORT command either to sort the stored data file or to sort entries within a defined block. For more information, refer to Chapter 3 of the XyWrite III Plus Reference Guide. To return your sorted file to its original format, refer to the section, "Restoring the Original Format," at the end of this application note. SORTING THE DATA FILE BY ZIP CODE MAILSORT.PGM is a XyWrite program you can use to prepare your data file for sorting by either the five-digit or the nine-digit zip code. When you run the program, it places a tab character before each zip code. Note #3 Zip Code Field. The following procedure assumes that the zip code is the last field in each record of your data file. 1. Copy the MAILSORT.PGM either onto your XyWrite working disk or onto the XYWRITE directory of your hard disk. 2. Call the data file to the screen and prepare it for sorting as described in "Modifying the Data File." 3. Make sure the cursor is at the top of the file (or at the point where you want to begin the sort). 4. Run MAILSORT.PGM. Type: F5run mailsort.pgm Result: The program starts to work its way through the file, moving to the end of a line, searching backward for a space (the one that precedes the zip code), moving right one space, and adding a tab character. The program stops when it reaches the last zip code. The example data file now appears as follows: Chris Cross#31 Ellsworth St#Bedford, MA 01730 Rosemary Dixon#275 Tremont Street#Nutting Lake, MA 01730 Ruth Parker#44 Manning Road#Billerica, MA 01821 Susan Faber#100 Broadway#Boston, MA 02170 Gail Pentel#27 Main St#Melrose, MA 02176 5. Use the following default setting to sort by the zip code after the first tab character: Type: F5default sk=8,80,1 6. Use the SORT command either to sort the stored data file or to sort entries within a defined block. For more information, refer to Chapter 3 of the XyWrite III Plus Reference Guide. To return your sorted file to its original format, refer to the section, "Restoring the Original Format." This is an example of a data file sorted numerically by zip code and restored to its original format. Chris Cross 31 Ellsworth St Bedford, MA 01730 Rosemary Dixon 275 Tremont Street Nutting Lake, MA 01730 Ruth Parker 44 Manning Road Billerica, MA 01821 Gail Pentel 27 Main St Melrose, MA 02176 Susan Faber 100 Broadway Boston, MA 02170 Program Content MAILSORT.PGM is reproduced below. Remember, if you plan to recreate this program yourself, you must follow the rules for creating a XyWrite program file. For more information, refer to Chapter 5 of the XyWrite III Plus Reference Guide. <>BC se / / XC <>>><> DUPLICATE FIELDS Sometimes records in your data file will have duplicate fields (for example, two or more people with the same last name or several addresses with the same zip code). When this happens, XyWrite continues to analyze the characters to the right until it reaches either a character that does not match or the end of the line. Sorting by Last Name If you are sorting by last name, XyWrite prioritizes the sort based on the characters that follow the last name Ä usually the address. You can sort groups of duplicate last names by the first name as follows: 1. Change the default sort setting to ignore tabs: Type: F5default sk=1,80,1 2. Define the first group of duplicate names and sort it. Type: F5sort 3. Continue through the file defining groups of duplicate names and sorting them. Sorting by Zip Code If you are sorting by zip code, no characters follow the zip code on the line, so XyWrite does no further prioritizing. You can sort groups of duplicate zip codes by the addressees' last names as follows: 1. Change all the spaces between words to tabs. Type: F5ci / /{ASC-10}/ 2. Make certain the default sort key setting will sort on the first tab. Type: F5default sk=8,80,1 3. Define the first group of duplicate zip codes and sort it. Type: F5sort 4. Continue through the file defining groups of duplicate zip codes and sorting them. RESTORING THE ORIGINAL FORMAT Use the following procedure to return your sorted file to its original format: 1. Make sure the cursor is at the top of the file (or at the point where you began the sort). 2. Change all single return characters to double return characters. Type: F5ci / Press: Ctrl (once) Type: / Press: Ctrl (twice) Type: / Result: The characters on the command line look like this: ci /{ASC-27}/{ASC-27}{ASC-27}/ Press: 3. Change all # characters to single return characters. Type: F5ci /#/ Press: Ctrl (once) Type: / Result: The characters on the command line look like this: ci /#/{ASC-27}/ Press: 4. Remove the tab characters. Type: F5ci / Press: Tab Type: // Result: The characters on the command line look like this: ci /{ASC-10}// Press: Note #4 Using the SORT Command and the Sort Key (SK) Setting. The SORT command uses the first 80 characters of each entry to sort. In order to sort data file entries by criteria other than the first 80 characters, the procedures in this application note require you to insert one or more tab characters in each record of your data file. You can then adjust the SK (Sort Key) setting to skip tabs instead of characters. The SK setting has the following form: default sk=n1,n1,n3 The default value for n1 is 1, which sorts numerical entries in decimal order (01, 2, 3, 10). A value of 2 sorts entries in reverse order (Z to A and 9 to 0). When n1 is 8, n3 defines the number of tabs (rather than the number of characters) to skip. The second value of the Sort Key (n2) defines the number of characters that XyWrite uses when sorting a list. The default size is 80 which means that the first 80 characters of each entry are sorted. If you have a very large file, you can reduce the amount of memory required to sort it by defining a smaller number of characters (for example, default sk=8,60,1). The third value of the Sort Key (n3) defines the number of characters or tab columns that should be skipped before the sort starts. (The default is 0.) This application note gives you the correct SK setting for each procedure as described. Refer to Application Note 122 for a complete explanation of XyWrite's SK (Sort Key) setting.