KB195981 - OL2000: How To Modify Multiple-Part Address Fields for Import

Article ID: 195981

Article Last Modified on 9/29/2003

Microsoft Outlook 2000 Standard Edition

This article was previously published under Q195981

SUMMARY

This article describes how to use Microsoft Excel to modify an exported contact list that contains multiple-part address fields so it can be successfully imported into Microsoft Outlook 2000.

MORE INFORMATION

When exporting data from other information management programs, you may notice that the home or business address field is broken up into multiple parts. If you look at the field names in the header you may notice the following:

   ADDR1     ADDR2     ADDR3

Each field may contain a part of the street address. One field may contain the apartment number, while the next field may contain the street address. If you import the file into Outlook in this format it will create separate entries for each component of the address.

You can use Excel to consolidate the data from the separate address fields into one importable field. This process will create an address that imports correctly into Outlook.

Save Your Data in the Correct Format

Your information management software must allow you to export contact information in one of the following formats:

   dBase or database file   (.dbf)
   Comma Separated Value    (.csv)
   Tab-Separated Value      (.txt)

After you have exported the data into one these formats, you can open it in Excel. When you open the data file in Excel you will be able to look at the header of the data file.

Open the File in Excel and Edit the Data File Header

In Excel, on the File menu, click Open.

In the Files Of Type list, click All Files (*.*).

Locate and note the field names that contain the address information. For example:

      ADDR1     ADDR2     ADDR3

Click to select a column (the location is not critical). On the Insert menu, click Columns to insert a new column. Click in the top cell of the new column and type a name that corresponds to a valid Outlook Field name. For example:
"Business Street", "Home Street", "Other Street"
Don't include the quotation marks.

For information about field names for importing into Outlook, please see the following article in the Microsoft Knowledge Base:
195576 : OL2000: Internal Fields Used by Outlook Folders
In the new column, click to select the cell beneath the new field name. Type the following formula using the cell addresses noted in step 3
=(Cell Address #1)&CHAR(10)&(Cell Address #2)&CHAR(10)& (Cell Address #3)&CHAR(10)
where Cell Address is the address of the cell you want to combine, for example:
=C2&CHAR(10)&D2&CHAR(10)&F2&CHAR(10)
The cell will now contain the combined address.
Click to select the new cell. Go to the last row of data and in the new cell column, hold down SHIFT and click in the last cell of the column. On the Edit menu, point to Fill, and click Down. This will copy the formula to the rest of the cells with the correct cell references.

For more information about copying formulas in Excel, click Microsoft Outlook Help on the Help menu, type "copy and double-click formulas" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
On the File menu, click Save As, in the Save As Type list, click to select "Text (Tab delimited)(*txt)" or "CSV (OS/2 or MS-DOS) (*.csv)." Type a name for the file, and then click Save.

Check the header names to make sure they correspond to the correct Outlook field name. Make any corrections needed and save the file as a text file (CSV) so it can be imported into Outlook.

Additional query words: fix ACT ECCO Symantec

Keywords: kbhowto kbinterop kbconversion KB195981