Super Export and Import Tools

Use Exchange Administrator's export and import features to move data in a flash

[Editor's Note: Some material in this column was adapted from Chapter 5 of Managing Microsoft Exchange Server, ISBN 1565925459. The information appears here courtesy of the publisher, O'Reilly & Associates.]

Tucked away under the Microsoft Exchange Administrator Tools menu lies a secret that most Microsoft Exchange Server administrators don't know about: powerful export and import features. When you learn how to properly use these features, you can export the entire Global Address List (GAL), change thousands of SMTP addresses at one time, and import large numbers of custom recipients—all without breaking a sweat. To perform such feats, you need to master only three steps: exporting, modifying, and importing data. But first, you need to know about the data format that Exchange Administrator uses during the export and import processes.

File Format
To export and import data, Exchange Administrator uses Comma Separated Values (CSV) files. Each line in a .csv file specifies data for an exported object and contains one or more property fields. Commas, without surrounding spaces, separate adjacent fields. Each field can be empty, can contain one value, or can contain multiple values (although the Exchange directory permits most fields only to be empty or to contain one value).

Exchange Server .csv files contain a header, which tells Exchange Administrator—and you—the meaning of each field. For example, the header line

Obj-Class,First Name,Last Name,Display Name,Alias Name,Directory Name,Primary Windows NT Account,Home-Server,E-mail address,E-mail Addresses,Members,Obj-Container,Hide from AB

specifies that each line of the file's contents will have 13 property fields, starting with Obj-Class, ending with Hide from AB, and including the mailbox alias (i.e., Alias Name), the mailbox's home server (i.e., Home-Server), and mailbox-attached email addresses (i.e., E-mail Addresses). You must place the entire header on the first line of the .csv file.

The content lines of an Exchange Server .csv file contain the entry values that correspond to the header fields. For example, in combination with the example header, the content line

Mailbox,Paul,Robichaux,Paul Robichaux,PaulR,PaulR,RAPaul,HSV1,,MS:RA/HSV/robichaux%SMTP:[email protected]%X400:c=US;a= ;p=Robichaux ?Asso;o=US;s=Robichaux;g=Paul;,,/ou=US/cn=Recipients,

specifies that the first entry is for a mailbox assigned to Paul Robichaux. This content line displays two important characteristics. First and most important, when a field (e.g., E-mail Addresses) contains multiple values, the percent sign character (%) separates those values. (If a property value contains a comma, double quotes must surround the value.) Second, fields (e.g., Members) without corresponding values are completely empty, with no spaces, tabs, or other characters between the separating commas. To make .csv files perform correctly, you must properly format the header line and each content line.

Exporting Data
To modify data for many objects simultaneously, you can export a .csv file, tweak it, then import it back into the directory. (For an example of this process, see the sidebar, "A Perfect Example," page 148.) When you export a .csv file, Exchange Administrator generates a header, including default fields such as the mailbox name and alias. Alternatively, you can specify a header that includes the fields you want to export. (You can use the header.exe tool from the Microsoft BackOffice Resource Kit—BORK—to easily generate a header containing specific fields.) When you want to modify a specific set of attributes, create the header file first. Then, follow these steps:

Modifying Data
After you export data, you can edit the resulting .csv file to add and remove fields or objects or to modify existing entry values for the exported objects. For example, suppose you want to set uniform storage limits for a server's mailboxes. You've exported the server's mailboxes and now have a .csv file that contains data for all the mailboxes. You can open the .csv file in Microsoft Excel, add a column for each field that you want to add (e.g., Issue warning storage limit, Outgoing message size limit), type each field's header label in the first row, then use Excel's Fill feature to quickly fill in the content values. (You can use any tool you choose to edit the .csv file, but Excel is popular because it understands how to read and write .csv files. Excel also has good macro programming tools.) You can also massage, either manually or with the help of a script, the existing fields' contents. You can take data from another source, such as another organization's Exchange server, use a script to automatically reformat the data, and import the data to create a set of custom recipients or mailboxes.

Before you import a modified export file, you must perform one important task: You must add the Mode property field to tell Exchange Administrator how to process each entry when you import the file. Exchange Administrator doesn't include the Mode property when it exports a file, so you'll need to add the property to the .csv file. Otherwise, Exchange Administrator won't permit you to remove objects; you'll be able only to create new objects or modify existing ones. The Mode property must follow the Obj-Class property in the header; otherwise, Exchange Administrator will refuse to import the .csv file.

The Mode property's default value is modify. This value tells Exchange Administrator to create any object that has an import entry but that isn't already in the directory and to modify existing entries according to the import record's property values. You can also use the create value to create new objects or the delete value to remove existing objects. For example, the following header and content lines add a new mailbox (i.e., a mailbox for Jim Hood) and remove an existing mailbox (i.e., a mailbox for Chuck Farnsworth).

Obj-Class,Mode,First Name,Last Name,Display Name,Alias Name,Directory Name,Primary Windows NT Account,Home-Server,E-mail address,E-mailAddresses,Members,Obj-Container,Hide from ABMailbox,create,Jim,Hood,Jim Hood,JimH,JimH,RAJimH,HSV1,,MS:RA/HSV/hood%SMTP:[email protected]%X400:c=US;a= ;p=Robichaux ? Asso;o=US;s=Hood;g=Jim;,,/ou=US/cn=Recipients,Mailbox,delete,Chuck,Farnsworth,Chuck Farnsworth,ChuckF,ChuckF,,,,,,,

Importing Data
After you've edited your exported .csv file (or created a new .csv file, if you're importing new data), you're ready to import the file. Don't forget to add a Mode property column if you want to delete objects. Then, follow these steps:

Wrapping Up
Now that you know some import and export tricks, you're ready to put your power to good use. You can write import and export scripts to perform repetitive tasks such as reformatting data from a legacy database. (ActiveState's Perl is particularly useful for these tasks because the program has strong pattern-matching and text-processing capabilities.) You can also schedule directory import and export tasks—see Exchange Administrator's online Help. Next time, I'll show you how to use your new abilities to easily complete otherwise difficult projects. Until then, remember that practice makes perfect!