Automator workflow of the month: Export contacts to Excel

If you rely on Address Book to keep your contacts straight, you may be the tiniest bit disappointed that Apple’s contact management application provides so few useful ways to filter and export your contact data. Fortunately, Automator can lend a very helpful hand—particularly if you seek a way to filter your contacts and then export that filtered data to an Excel spreadsheet. Here are the steps for doing this.

Make your workflow

With a copy of Microsoft Office 2008 or 2011 on your Mac (these versions of Office install Word, Excel, and PowerPoint Automator actions), launch Automator and from the new workflow sheet that appears, select Service and click Choose.

At the top of the workflow, configure the first line to read Service Receives No Input in Any Application. This ensures that the service will be available at any time.

Now, select Contacts from the Actions library and, from the Actions column to the right, drag Find Address Book Items to the workflow area. In this action, make sure that the first pop-up menu reads Find People Where. In the area below, configure the action to read All Of The Following Are True and Any Content Contains. You can leave the field following Contains empty. Click on Options at the bottom of the workflow and enable the Show This Action When the Workflow Runs option.

Next, from the same group of Contacts actions drag the Get Contact Information action to the workflow area. From the Export Format popup menu choose Spreadsheet (Tabs). Within the action select the kinds of data that you’d like to eventually appear in your spreadsheet—First Name, Last Name, Work Phone, and Work Email, and so on. Additionally, enable the Add Labels option.

Now select the Utilities library and drag the Copy To Clipboard action to the workflow area. Select the Documents library and drag Create New Excel Workbook into the workflow. Finally, from this same library drag the Paste Clipboard Content Into Excel Workbooks action into the workflow. Configure the second pop-up menu in this action to read either Paste As Default or Paste As Text. Save your workflow and name it.

Test it out

To give it a try, simply click on the workflow’s Run button. A dialog box will appear asking you to enter a search term in the Contains field. Enter the term you wish to filter by—this can be a name, an email extension such as macworld.com, an area code such as 415-, or a zip code.

Click the Continue button in this dialog box and Automator will execute the workflow. During the process, any items in Address Book that match your query will be copied to the clipboard in a tab-separated format. Excel will launch (if it’s not already running) and a new workbook will be created that contains your data, with each contact appearing in a separate row.

Because you’ve saved the workflow as a service, you can invoke it at any time you like. Just choose Services from any application menu and choose this workflow. It will execute exactly as described. If you like, launch System Preferences, select the Keyboard preference, choose the Keyboard Shortcuts tab, select Services, locate the workflow you just created (it will appear under the General heading), select the workflow, click on the resulting Add Shortcut button, and press a keyboard shortcut you’d like to use to invoke the workflow in the future.