OL98: Problem Exporting Multi-line Contact Street Addresses

ID: Q180658

The information in this article applies to:

SYMPTOMS

When you export Microsoft Outlook Contacts in either comma or tab delimited format, any street addresses that contain more than one line are exported with carriage returns separating the multiple lines. This may results in a skewed output file containing multiple records for those contacts containing multi-line street addresses.


CAUSE

In a comma or tab delimited file, carriage returns are interpreted as record delimiters.


RESOLUTION

There are two options for correcting this problem:

Use CSV Format

Export the file in comma delimited (CSV) format; then open the file in Microsoft Excel. The file will import correctly with one record per contact and the multi-line street address in one cell.

NOTE: This option will not work if you export the file in tab-delimited (TSV) format.

Manipulate the file in Microsoft Word

Export the file in either comma-delimited or tab-delimited format, and then open the output file in Word and manipulate the data as follows:
  1. On the Edit menu, click Replace. If the Special button does not appear, click More to expand the dialog box.


  2. In the Find What box, type a double quotation mark ("). Then click Special and click Paragraph Mark.


  3. In the Replace With box, type any special character not found in your data, such as a pound sign (#).


  4. Click Replace All.


  5. In the Find What box, click Special and click Paragraph Mark.


  6. In the Replace With box, type whatever character you would like to use to separate the multiple street address lines, such as a percent sign (%).


  7. Click Replace All.


  8. In the Find What box, type the special character used in step 3.


  9. In the Replace With box, click Special and click Paragraph Mark.


  10. Click Replace All.


After completing this procedure, your file will have one record for each contact. The multi-line street address will be in one field, surrounded by quotation marks. Each street address line will be separated with the character chosen in step 6.


MORE INFORMATION

The following illustrates the problem described in the "Symptoms" section and the result of performing the procedure described in the "Resolution" section.

The example below has a 2-line street address: 


   John Doe
   1234 Mockingbird Lane (address line 1)
   Apt 589               (address line 2)
   Dallas, TX 75123
The exported file will contain a carriage return ([CR]) following the word "Lane" which results in two separate records for this one contact, as follows: 

   "John Doe","1234 Mockingbird Lane[CR]  (record 1) 

   Apt 589","Dallas, TX 75123"[CR]        (record 2)
After performing the procedure described in the "Resolution" section, the resulting output will look as follows: 

   "John Doe","1234 Mockingbird Lane%Apt 589","Dallas, TX 75123"[CR]
NOTE: This example assumes the percent sign "%" was used as the separator for the street address lines in step 6.

Additional query words: 


Keywords          : IntpImEx 
Version           : WINDOWS:
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: August 10, 1999