Part Two of this three-part article covers how to import your Outlook contact list—the CSV file—into Excel, then how to refine, maintain, and manage that database so, in Part Three, you can merge it with your custom Word documents for distribution. This is the easiest, most efficient method for such a job, especially if your distribution tasks are repetitive and innumerable. Also, because Excel is so easy to use, and you can view your databases on a single screen, this method is often preferable. See Part One for instructions on how to export Outlook contacts to Excel for data management.
When you need a real database
Before we start, let’s talk about when not to use Excel for mail merge data management. Even though Excel can accommodate 1,048,576 records, I recommend using a relational database such as Microsoft Access if your database has more than 100,000 records. That’s because 100,000 records (even if each record has only a few fields) contain so much data, the manipulation of such a huge database would be slow and cumbersome (based on how much memory your system has and how that memory is allocated). Merging that much data, at one time, with a Word document would also be slow and cumbersome.
If you don’t have access to another database program, you can still use Excel, but you’ll have to break up your database into workable sections (either separate sheets or, possibly, separate worksheet files) such as, for example, last names A through H in one worksheet file; I through P in a second worksheet file, and Q through Z in the last file. Remember, 50,000 records (rows) with four fields (columns) is actually much smaller (memory and file size) than 25,000 records (rows) with 12 fields (columns). Smaller databases (or sections of a single database) are much easier to manage and manipulate than one huge database file.
You can also create relational databases in Excel, with the master database in one file and the related databases in other files. But that’s another complex process for a future article.
Import Outlook contacts/CSV file into Excel
1. There are several ways to import your CSV file into Excel. The easiest way is to just locate the file and double-click the filename. If Excel resides on your system, the file opens in the Excel program. If, however, you have more than one version of Excel and/or more than one spreadsheet program, it’s better to open Excel first, locate the file, then click File > Open > Computer > Browse, browse to the file’s location, select the file, and click Open.
2. Excel places each contact field in its own column, and the records run vertically down the rows beginning at A2. The Outlook field names are distributed across the first row beginning with A1.
JD Sartain Figure 1: Import-Open Outlook contacts CSV file in Excel.
3. If you have maintained your Outlook contact list and kept all the fields up-to-date, your CSV file in Excel should look like the spreadsheet in Figure2a. Notice, however, that because this database is work-related, you won’t need all the fields that refer to Home such as Home Address, Home City, Home Phone, etc. You won’t need the first column/field either (Title).
4. To remove these fields and all the data in them, hold down the Ctrl key and click the column letters above the “Home…” field columns. Once all the Home field columns are selected, place your cursor on any of the column letters or on any column in the selected area, right-click, and select Delete from the dropdown menu—or select Delete > Delete Sheet Columns from the Ribbon menu. Browse across the top row of your spreadsheet to review the fields you imported. Use this same procedure to remove any/all unnecessary fields that aren’t needed for this distribution list.
JD Sartain Figure 2: Remove unnecessary or unwanted columns/fields.
5. Your field names in Excel don’t have to match the Outlook field names exactly, because you can map fields to whichever field name you’d like in Outlook before you export the data. See Part One for instructions on how to export Outlook contacts to Excel for data management. Long field names are a pain—they use too much space and take forever to type—so shorten them.
For example, change Business Postal Code to bZipCode and Business Street to bStreet. You’ll have to modify these field/column names anyway because, in some versions of Word, you cannot use spaces, quotation marks, apostrophes, or hyphens in the field/column names when merged.
Note: In Office 2013, Word replaces the spaces between field names with an underscore.
6. Next, adjust the column widths so you can see the data in each field. The quickest, most efficient way is to highlight the entire spreadsheet, then (under the Home tab) select Format > AutoFit Column Width. All the columns adjust in one second. Notice that the width of each column adjusts to accommodate the longest record in each column field.
Note: This is your “master” database, which means it contains the basic data for each of your contacts. Typically, the master database contains the customers/clients and/or the mailing list records because there is only one of each—that is, each contact is a unique record. Slave or child databases are connected to the master through this unique record; however, because names are often entered differently (based on the individual typing in the information (e.g., Robert Smith, Robert J Smith, Rob Smith, etc.), most database administrators assign each client/contact a unique ID number, which results in fewer errors.
7. Move your cursor to the Home position—that is, go to A1 (or press Ctrl+Home). Select Insert > Insert Sheet Column. Enter the field name in cell A1; IDnum. In cell A2 and A3, respectively, enter 101 and 102. Highlight both cells (A1:A2), then position your cursor on the tiny green box in the bottom-right corner of cell A2 (cursor turns into a plus sign). Hold down the left mouse button and drag the plus sign (with highlight) down to the last record in the database and release the mouse. The column fills with consecutive numbers following 101 and 102; that is, 103, 104, etc. Now the clients have ID numbers.
JD Sartain Figure3: Adjust column widths, insert a column, and add unique Contact ID numbers.
Note: In a relational database, additional fields are placed in a separate spreadsheet or file, which would require a unique field such as ID Numbers. This procedure will be covered later, in another article. In this exercise, the additional fields for your distribution list are added in this “master” spreadsheet. The distribution document for this database contains three “custom” fields: Expertise, Innovations, and Fees.
8. To add these new fields, press Ctrl+Home to move your cursor to cell A1. Next, press the End key, then press the Right Arrow key twice. This repositions the cursor to the first blank column at the end of the current database (for example, column L). Enter the field name Expertise in L1, enter Innovations in M1, and Fees in N1. Next, enter the applicable data in each column.
9. Next, sort the entire database by ZIP code so your merged letters and envelopes comply with the United States Postal Services’ rules for Bulk Mail discounts.
JD Sartain Figure 4: Add new, custom fields, data, and sort by zip code.
10. The database is ready to merge with your letters. Check again soon for the next column article with instructions regarding how to merge your Excel database with your Microsoft Word letters.