\[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,RA\Paul, HSV1,, MS:RA/HSV/robichaux%SMTP:paul@ robichaux.net%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:
- From the Exchange Administrator Tools menu, click Directory Export to open the Directory Export dialog box, which Figure 1 shows.
- From the MS Exchange server drop-down list, select the server you want to contact to retrieve the data. This machine doesn't need to be the server you're logged on to. Be aware that choosing a remote server will increase network traffic between the machine on which you're running Exchange Administrator and the selected server.
- From the Home server drop-down list, select the server that contains the data you want to export. By default, this is the server you're logged on to.
- Click Export File and select a file in which to store the exported data. You can type a filename to create a new file, in which case Exchange Administrator exports the default header values, or you can select an existing file, in which case Exchange Administrator appends the export records to that file.
- Click Container, and select the container or Address Book View (ABV) that you want to export. By default, Exchange Administrator exports the contents of the Recipients container. When you want to include the contents of the selected container's subcontainers, select the Include subcontainers check box.
- In the Export objects section, select check boxes for the objects you want in the output file. You can export mailboxes, custom recipients, and distribution lists (DLs) in any combination.
- Click Export.
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-mail Addresses,Members, Obj-Container,Hide from AB Mailbox,create,Jim,Hood,Jim Hood,JimH,JimH,RA\JimH,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:
- From the Exchange Administrator Tools menu, click Directory Import to open the Directory Import dialog box, which Figure 2 shows.
- If you're creating accounts, select from the Windows NT domain drop-down list the domain in which you want to create accounts.
- From the MS Exchange server drop-down list, select the server to which you want to send the imported information.
- Click Container, and select a container in which to put newly created objects. The .csv file can explicitly specify a target container for each new object; the choice you make here controls the container that will hold objects without a specified target. The two radio buttons let you specify whether you want to honor or override the .csv file's specifications.
- Click Import File, and select an import file. This file must have a valid header; otherwise, the import will fail.
- In the Account creation section, select the Create Windows NT account check box and the Delete Windows NT account check box if you want Exchange Administrator to create and delete NT accounts as it creates and deletes mailbox objects. When you enable account creation, you can also select the Generate random password check box if you want Exchange Administrator to generate random passwords for the new accounts.
- Decide how you want Exchange Administrator to handle import records that have multivalued properties. In the Multivalued Properties section, select Append to add new values to the existing values, or select Overwrite to overwrite the properties' values with the information in the import file.
- Click Import.
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!
