WAB Export to CSV

WAB Export to CSV

I am seeking help in making the export of Windows Address Book (WAB) data to Comma Separated Value (CSV) files, and then combining these into a single human readable database, easier and faster than the method described below.

WAB allows export of its contact database to a CSV file, which can be imported into a spreadsheet, such as Quattro Pro, and from there copied into Word, where it will convert to a table format which is easy to edit (eg remove unwanted or empty fields, ie columns).

WAB export to CSV does not work as you would expect it to. For instance it does not export any of the contact data that you generated and placed in sub-folders that you created. With much experimentation and testing, I have been able to generate such a spreadsheet and Word backup database, but the process is long, complicated and time consuming.

The WAB I use is the address book that comes with Outlook Express in Windows XP. WAB stores all your contact data in 39 fields in a proprietary format database in a file having a .wab extension.

In Outlook, I understand it is called the Outlook Address Book. In Windows Vista, I understand it is called Windows Contacts or the Contact Address Book (see http://msdn2.microsoft.com/en-us/library/ms735778.aspx) .

WAB Folders and Sub-Folders

In earlier versions of Outlook Express, you can distinguish between multiple users by creating user profiles. In Outlook Express 5 and later, a new feature called identities performs this function. An article describing how to create, use, and manage identities in Outlook Express, may be found at http://support.microsoft.com/kb/209169

The two folders that automatically exist in the Address Book are the Main Identity's Contacts (MIC) folder and the Shared Contacts folder. Shared contacts are those shared with other Windows Identities. For home use by a single person, there will be only one Identity, the Main Identity, so that such people need only be concerned with the MIC folder and its sub-folders. The Main Identity is you.

Within the MIC folder are a number of sub-folders which you create manually (right-click | New | New Folder), and which contain contact data that you enter manually (right-click | New | New Cantact). Sub-folders provide social and other groupings, such as

Family

Friends

Business

Government

I have nearly 60 such sub-folders in my WAB. This large number is partly due to the fact that WAB does not allow sub-folders within sub-folders, ie sub-sub-folders. Outlook Contact List, does allow sub-sub-folders, and these are grouped together in the Outlook Address Book. This is why I plan to upgrade to Outlook, and why I am backing up my WAB.

Breaking up your contact list entries into such groups make them easier to find. You would never find them amongst the hundreds of entries in the MIC folder. Contact records in these sub-folders do not automatcally appear in the MIC folder.

The spreadsheet or Word backup database should preferably have the records (contacts) also broken up into such blocks, with the same sub-folder names as block headings, in order to find entries more easily in the backup database.

Automatic Data Entry

The MIC folder is created automatically, as are its contents. It contains the target addresses of all the reply emails you send out if

is enabled, which it is by default. On my computer it is enabled, and this folder contains some 800 email addresses.

Since the records in the MIC folder are derived from emails, only two of these fields can be filled, those containing the sendee’s name and email address. These must be derived from the To: field, which is the second line in the reply email header. This might typically read:

To: "John Doe" <john.doe@magma.ca>

Exporting such an MIC folder entry to CSV shows that the First and Last Name fields are empty, and that “John Doe” gets put in the (Display) Name field and <john.doe@magma.ca> gets put in the Email Address field. CSV files produced by exporting many MIC folder auto-entries, therefore are sorted by first name.

If you manually make a new contact entry into a sub- or MIC folder, and place “John Doe” in the Display field, you will find that WAB parses this entry, and puts a copy of “Doe” in the Last Name field, and a copy of “John” in the First Name field.

Manual Data Entry

To make a manual contact entry into WAB:

Select folder or sub-folder | Right-Click | New | New Contact

This will take you into the WAB Properties dialog which allows you to enter data into up to 39 different fields, under tabs such as

Name, Home, Business, Personal, Other (Notes), NetMeeting, Digital IDs

When you have finished filling the fields of interest, click the OK button to complete the entry operation. For further details, see http://support.microsoft.com/kb/307730 .

Windows Address Book Export Options

The WAB Export data option

File | Export . . .

allows you to

1. Export the Address Book (WAB) data file "identityname.wab" to another location.

2. Create a "Business Card" (vCard) of a single WAB entry. The resulting file has a .vcf extension.

3. Create a "Comma Separated Value" (CSV) list of the contents of the MIC folder to a .csv file.

The CSV option generates a text file with a .csv extension, containing a selected number of fields (up to 29 of the original 39) separated by commas, for the contact records in the MIC folder. It does not export any of the records in the sub-folders, even if these are highlighted. In order to export the contents of a sub-folder, you have to copy, or temporarily move (drag), the records from the sub-folder to the MIC folder, generate a CSV file of the MIC folder contents, and then drag these records back to their original sub-folder.

In order to make a CSV file of only those records you dragged there, it is necessary to first delete the entire original contents of the MIC folder. In addition, if you do not first delete the original (email-reply) records, then the original records plus the records you dragged there will get sorted, and the sub-folder records will be lost amongst the 800 or so original MIC records, making it impossible to find them to drag them back to their original sub-folders.

In my case, I was not really interested in these original contents (replies to emails), but only in the contents of the sub-folders which contained contact records that I had generated.

However, just in case one day one does need to look at these reply email addresses, it is wise to make a backup of the entire original ".wab" file before deleting the MIC contents, and for good measure, to make a CSV file of the original MIC contents (with a filename like "emails.csv" or "Original MIC.csv") before deleting the original MIC folder contents.

To Make a .wab file Backup

There are three easy ways to find and make a copy of the WAB ".wab" file:

1. File | Export | Address Book (WAB)

This opens the “Select Address Book File to Export to” dialog.

Browse “Save in:” field for folder where you want to save the ".wab" file backup.

In the “File name:” field, type backup filename,then click the Save button.

An Information box titled “Address Book - Main Identity” notifies you that

“Your Address Book has been successfully exported to <path\filename.wab>”

Click OK button.

2. Start | Run | “Open:” field | type "%AppData%\Microsoft\Address Book" | click OK button

This will open Windows Explorer in the Address Book folder, from which you can copy the ".wab" file to a safe location. [%AppData% is a system variable that contains the path to the Applications Data folder].

Start | Run | “Open:” field | paste the WAB file path (but delete the filename) | click OK button.

This will open Windows Explorer in the Address Book folder, from which you can copy the ".wab" file to a safe location. [If you include the .wab filename in the path, Run will just open Address Book].

To Make a CSV backup of your MIC folder

To generate a CSV file, first select (highlight) the records in the MIC folder that you wish to export. Then

File | Export | Other Address Book | select the file type “Text File (Comma Separated Values)” | click Export button

This will run the CSV Export wizard. On the first wizard page:

Click the Browse button. This will take you to the standard Windows "Save As" dialog.

Select the appropriate folder in the "Save In" field;

Enter your CSV filename in the "File Name" field; and click the Save button.

This will take you back to the wizard, where you click the Next button.

On the second wizard page is a list of the fields that can be exported.

Enable all the fields of your contact data that you wish to export.

Click the Finish button, and your CSV file will be generated and written to your specified file.

The CSV file consists of

the field names in the first line (these act as column headers when imported into a spread sheet)

the data records in the following lines, one record per line, each record on a single line.

Each field of each record is separated by a comma, with no additional spaces.

To Make a CSV backup of one of your sub-folders

Even though a sub-folder was selected when making a test CSV file, none of the records from the manually generated sub-folders where included in the output CSV file. This CSV file appears to contain only the automatically generated records in the MIC folder. Since these only contain the sendee’s name and email address (they were generated from email replies), these fields are all that appear in the CSV Export list.

Since CSV files can only be made from the MIC folder, to make a CSV file of one of your sub-folders, it is necessary to put the contents of the sub-folder into the MIC folder before doing the "Export to CSV" process. If you want a CSV file of only the sub-folder contents, it is necessary to first delete the entire contents of the MIC folder. Sub-folders have to be exported one at a time from the MIC folder. If you copy/paste, or drag, the contents of more than one sub-folder into the MIC folder, then they will be sorted alphabetically, making it next to impossible to distinguish the contents of one sub-folder from another, both in the MIC folder and in the exported CSV file. This makes it hard to restore these mixed up records to their original sub-folders.

With the contents of the MIC folder deleted, exporting to CSV produces no .csv file.

When copying from a sub-folder to the MIC folder, if you drag the entries from the sub-folder to the MIC folder, this will Move the entries and you will lose the data in the sub-folder. After exporting to CSV, you will have to Move (drag) the entries back to their original sub-folder.

Right-drag does not work in WAB, so you cannot copy the entries this way. Instead, to avoid the move-back procedure, you can Copy the sub-folder entries to the MIC folder, export to CSV, and then delete these entries in the MIC folder. To do the copy/paste:

When exporting the sub-folder contents to CSV, it is convenient to use the sub-folder name as the CSV filename.

Repeat this move/copy, export, move-back/or delete process for all your sub-folders. In my case, I end up with about 60 CSV files, one for each of my sub-folders. This can take a long time. It took me between 1 and 2 hours to export the contents of all my sub-folders. The most time consuming part is on the second wizard page, re-enabling all the fields that you want exported.

To Make a Spreadsheet Database of all your sub-folders

Open your favourite spreadsheet. I use Quattro Pro v12. To open all the CSV files at once:

1. Go to the Open dialog (folder icon, Ctrl-O, or File | Open).

2. Browse the "Look In" field till it shows the folder in which you have saved all the CSV backups of your sub-folders.

3. In the "File Type" field, select "All Files (*.*)".

4. In the field showing all the CSV files, Select All (Ctrl-A).

5. Click the Open button, and wait while it loads all the files.

6. In the Windows drop-down menu, you will see all the 60 or so CSV files that have been opened.

7. Open one more blank new spreadsheet, into which you are going to copy the contents of all these (60 or so) databases.

Before you copy each sub-folder database into the combined spreadsheet, enter a heading (bold) using the sub-folder name (the CSV filename). The last CSV field (Notes) will be in column AC (there are a maximum of 29 CSV fields). When copying each database, select database columns A to AC, down to however many rows it contains.

When the contents of all the CSV files have been copied to the combined spreadsheet, Save the result.

Name Fields

The Name fields in the New Contact entry dialog are

First Name, Middle Name, Last Name, Display Name, Nickname

By default, DisplayName is automatically generated as FirstName + MiddleName + LastName, but it can be edited.

Each record can be displayed in the MIC folder or sub-folder sorted alphabetically by name, email address, or phone number etc. The display name in the sub-folder Name column is truncated to 28 characters, but is shown in full under the Summary tab. There are four ways that WAB can display the names in the sub-folder Name column:

1. Ascending order by FirstName MiddleName LastName,

2. Descending order by FirstName MiddleName LastName,

3. Ascending order by LastName, FirstName MiddleName

4. Descending order by LastName, FirstName MiddleName

The name order and sort direction can be toggled, by repeatedly clicking the Name bar at the top of the list. The records will be sorted according to the name order you set, eg by FirstName for #1, by LastName for #3, etc.

When you double-click on the folder or sub-folder entry for “John Doe”, the “John Doe Properties” dialog comes up. This dialog has an additional Summary tab, containing the contents of some of the main fields. These entries can be copied but not edited. The data in the fields under the other tabs can be edited. The name displayed under the Summary tab is that in the Display Name field.

If the MIC folder is then exported to CSV, these records are also sorted alphabetically but by

FirstName, LastName, Middle Name, [Display] Name ie “FirstName MiddleName LastName”,,,,,,,,,,,,,,,

Misappropriation of Name Fields

I experimented with using the First Name field for the sub-folder name, so that all the sub-folders could be dragged into the MIC folder at the same time, where they would be sorted by sub-folder name. This would prevent the records from different sub-folders from getting mixed up, and expedite their return to their original folders after exporting to CSV. A single CSV export would then produce a database containing the contacts from all the sub-folders.

If all the sub-folder records have their Name fields misappropriated as follows

Sub-folder name in the First Name field

Organization name in the Middle Name field, and

“FirstName LastName” in the Last Name field

and if the MIC folder and sub-folder contents have their Name column toggled to display the Name fields in the order:

FirstName MiddleName LastName,

then such records will appear as and be sorted alphabetically in the sub-folder by

Sub-folder name, Organization name, “FirstName LastName”

and likewise if these records are dragged to the MIC folder, these records will also be sorted in this order. Records from several or all of the sub-folders, dragged into the MIC folder, will then not get mixed up, and all the records from each sub-folder will be grouped together.

Because of the truncation of the DisplayName in the sub-folder Name column, it is best to use short 3 character abbreviations [eg CBC, NCC etc] for the Organization name, so that the “FirstName LastName” do not get truncated or eliminated.

The CSV file so generated would have its first few output fields (titled FirstName, LastName, Middle Name, [Display] Name) containing:

SubfolderName,

FirstName LastName,

OrganizationName,

SubfolderName FirstName LastName OrganizationName (again)

which, because of the repetition, is not so good, but when imported into a spread sheet can easily be corrected by deleting column 4 (the repetition), resulting in just the column sequence

Sub-folderName,

FirstName LastName

OrganizationName.

However, the work involved in editing the Name fields in all the records in all the sub-folders, would be prohibitive. The advantage of this method is that only one CSV export is necessary, and there will be no combining multiple spreadsheets (CSV files) into one overall spreadsheet.

Last update 2007-07-24.