Using Microsoft Access to Migrate cc:Mail Data to Exchange Server

Gregory Dodge
Microsoft Consulting Services, Gulf States District

August 1999

Summary: This document explains how to use Microsoft Access (both 97 and 2000) and the “Microsoft Exchange and Outlook Data Access Wizard” to migrate specific data from cc:Mail to Exchange. It explains how to migrate two different types of data. The first is to extract the current SMTP addresses from the cc:Mail Internet gateway and update the cc:Mail custom recipients in Exchange, and the second is to migrate existing cc:Mail mailing lists to Distribution lists in Exchange. To assist with these examples, we have included a sample set of cc:Mail example files (with the same filenames noted below), an example database to go with them, and a blank database you can use as a template. These techniques were demonstrated at Microsoft TechEd 98 in the session entitled “cc:Mail to Exchange Rapid Migration Strategies” (session number 4-309). (6 printed pages)

Requirements

To properly use the tools and techniques described in this document you must have the following software installed:

• Exchange Server 5.0 or 5.5 with appropriate service packs applied
  
  
• Microsoft Access 97 or Access 2000 (not tested with Access 95)
  
  
• Microsoft Outlook 97 version 8.03, Microsoft Outlook 98, or Microsoft Outlook 2000
  
  
• The “Microsoft Exchange and Outlook Data Access Wizard” (required for Access 97) You can download it from www.microsoft.com/Accessdev/FreeStuff/wzmapdl.htm
  
  
• The cc:Mail Import and Export utilities, version 5.X or later

Setting Up the Database

To use the example template database, you will need to follow these steps for setup:

1. For Access 97 only, install the “Microsoft Exchange and Outlook Data Access Wizard” by running the file wzmapi80.exe. Make sure that before you run this with Access 97 already installed.
   
   
2. Open Microsoft Access
   
   
3. Open the Template Database.

Linking the Exchange Global Address List to the Database

For Access 97

1. On the Tools menu, click Add-Ins.
   
   
2. Click Link Exchange and then click Outlook Wizard.
   
   
3. Double-click Address Books.
   
   
4. Click Global Address Book and then click Next.
   
   
5. Click Finish.
   
   
6. Click OK.

For Access 2000

1. On the File menu, click Get External Data and then click Link Tables.
   
   
2. In “Files of Type" choose Outlook ().
   
   
3. Double-click Address Books.
   
   
4. Click Global Address Book and then click Next.
   
   
5. Click Finish.
   
   
6. Click OK.

Note   You can also import the Global Address List into a table instead of linking it, but it is better to link because the data will always be up-to-date.

Only these steps are specific to the version of Microsoft Access you are running, so all subsequent steps and the examples below work the same on Access 97 and Access 2000.

Importing the SMTP Addresses from cc:Mail

The data to be imported into the Access database is stored in a file named SMTP.ADR, which is located on the cc:Mail Internet gateway PO. In this example, we assume that you use the naming convention “Lastname, Firstname” in your cc:Mail directory, but if you use another naming convention you could easily tailor these steps for that convention. The steps are:

1. Make a copy of the file SMTP.ADR from your cc:Mail Internet gateway and call it SMTPADR.RAW.
   
   
2. Open the file in Notepad.

   The format of the entries in this file should be

   cc:Mail address <=>alias@company.com

3. Select Search/Replace.
   • In the “Find what” text box, type < = >.
     
     
   • In the “Replace with” text box, type ,smtp: in lowercase and click Replace All.
4. Save the file as smtpadr.csv.

Importing Data

1. Open the database you previously setup in Access.
   
   
2. On the Access toolbar, select Get External Data from the File menu, click Import, and then select the file SMTPADR.CSV you created in step 4 (above).
   
   
3. Click Delimited, and then click Next.
   
   
4. For the text qualifier, choose Comma Delimited and {none} and then click Next.
   
   
5. Choose In an existing Table and select the table named SMTPADR. Click Next.
   
   
6. Click Finish.
   
   
7. In Access, click the Queries tab.
   
   
8. Double-click Generate cc:Mail SMTP Address Update File.

   You should now see a list that looks like an Exchange CSV import/export file. Verify that the number of records equals the number of SMTP addresses you had in cc:Mail. If not, check the join query in design mode to make sure the lastname and firstname fields match up correctly.

Exporting Data

1. Select Save-As/Export from the File menu, and then choose To an external file or database. Click OK.
   
   
2. Change the file type to Text Files and make the filename SMTPUPD.CSV. Click Export.
   
   
3. Choose Delimited and then click Next.
   
   
4. Check the box for Include Field Named on First Row, Comma delimited and no text qualifier and then click Next.
   
   
5. Click Finish.

Updating the cc:Mail Custom Recipients in Exchange

1. Open Microsoft Exchange Administrator.
   
   
2. Select the recipients container that contains the cc:Mail custom recipients.
   
   
3. Select Directory Import from the Tools menu.
   
   
4. Click Import File and select the file SMTPUPD.CSV (from step 2 in "Exporting data").
   
   
5. Click Import.
   
   
6. Click OK, and check the event log for any errors.

   You should now be able to validate that the cc:Mail custom recipients have an additional SMTP address that equals their old address on cc:Mail. If you want to replace the default reply-to address instead of adding a secondary proxy address, you only need to change the field name and make the letters SMTP all uppercase.

Importing the cc:Mail Mailing Lists to Exchange Distribution Lists

The data about membership and the names of the cc:Mail mailing lists can be easily exported, parsed, imported and then used to create a CSV file to create the Exchange distribution lists and their members. In your migration, you should consider when you want to move the mailing lists to Exchange, as you can do this early or late in the migration project. This example will work easily if you are moving all the lists at once, although you could easily modify it to move particular lists at a time. The steps are:

1. Use the cc:Mail export utility to export a list of the mailing lists.

   Since mailing lists are located in the cc:Mail PO in which they were created, you may need to export the lists from all your cc:Mail Pos. Make sure to put all the exported files into one file. The command to export the mailing lists and membership are

   Version 5.X - export.exe /LIST /@cclist.exp

   Version 6.x and later - export.exe /LISTS /@cclist.exp

2. Since we need only the mailing list name and the members, we parse the file and strip out what we need. Do this by executing the command in a DOS window type ccmlist.exp | find /i "Name:" > ccmlist.csv.
   
   
3. In Notepad, open the file ccmlist.csv.
   
   
4. Select Search/Replace.
   • In the “Find What” text box, type Name: LIST.
     
     
   • Leave the “Replace With” text box blank and click Replace All.
5. Save the file as CCMLISTIMP.CSV.

In your database

1. Open the database you previously set up in Access.
   
   
2. On the Access toolbar, select Get External Data from the File menu, click Import, and then select the file CCMLISTIMP.CSV you created in step 5 (above).
   
   
3. Choose Delimited and then click Next.
   
   
4. Choose Space Delimited and double-quotes as the text qualifier, and then click Next.
   
   
5. Choose In an existing Table and select the table named ccMail Mailing Lists. Click Next.
   
   
6. Click Finish.

In Access

1. Click Queries.
   
   
2. Double-click Generate ML to DL import File.

   You should now see a list that looks like an Exchange CSV import/export file. There should be one entry for every mailing list member, so don’t worry that the mailing list names show up multiple times.

3. Select Save-As/Export from the File menu, and then choose To an external file or database. Click OK.
   
   
4. Change the file type to Test Files and make the filename CCMLISTUPD.CSV. Click Export.
   
   
5. Choose Delimited and then click Next.
   
   
6. Check the box for Include Field Named on First Row, Comma delimited and no text qualifier, and then click Next.
   
   
7. Click Finish.

In Microsoft Exchange Administrator

1. Select the recipients container where you want to create the Distribution Lists.

   It is a good practice to put the DLs in a separate container from mailboxes and custom recipients.

2. Select Directory Import from the Tools menu.
   
   
3. Click Import File and select the file you created named CCMLISTUPD.CSV (step 4 "In Access").
   
   
4. Click Import.
   
   
5. Click OK, and check the event log for any errors.
   
   
6. Delete the mailing lists from cc:Mail and force the CCMC to dirsync the new distribution lists cc:Mail proxies to cc:Mail

They should be the exact same name as they were in cc:Mail.

Other Uses

Although the examples in this document used mailing lists and SMTP addresses, the techniques presented here can be used to manipulate other types of data. For example, you could update the alias field for cc:Mail CRs to match the NT domain account, or update other fields from an HR database. It should also be easy to adapt these examples for use in an MS-Mail environment, or any situation where you need to update custom recipients in the Exchange directory. Also, with a little bit of VBScript programming, you could automate the file import and export processes so they can be done with the push of a button.

--------------------------------------------

© 1998 Microsoft Corporation. All rights reserved.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

Microsoft, the BackOffice logo, Outlook, Visual Basic, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Other product company names mentioned herein may be the trademarks of their respective owners.

Microsoft Corporation · One Microsoft Way · Redmond, WA 98052-6399 · USA