Can I import addresses in excel format into address book?

[mileslong]

i have a ton of business contacts info on an excel spreadsheet and I want to import them into address book but the only options it gives me is for vcard, text file and LDIF, whatever that is. i really dont want to have to retype them all by hand, do i have any options??

[Bern]

If you're importing an export of contacts from Outlook on a PC in comma-separated values (CSV) format which are saved to an excel spreadsheet into OS X Address Book the only way I know how is by following these steps:-

- Get Mozilla/Netscape:
Download from www.mozilla.org.

- Mozilla Address Book:
Go to Mozilla's Address Book (in the Window menu item or Command-5). It will be empty. Create a new record card and place something in each of the fields. I found it helpful to replicate each field name in the field itself -- i.e. in the "Email:" field, enter "email1@email.com.au," "Workphone" in the work phone number field, etc. This helps later when you have to get the columns in your CSV file in the right order.

- Export CSV file from Mozilla Address Book:
In the Tools menu item, there is an Export command. Export the Address Book with your dummy record as a CSV file (so you can open it in Excel).

- Rearrange your original CSV file:
Open both the exported Mozilla Address Book file and your Contacts CSV file with your contacts in Excel (it would be advisable to use a copy of your Contacts CSV file, in case something goes wrong!). This part is a bit labourious, but you simply get your Contacts CSV file in the same column order as the Mozilla Address Book file with the dummy record. You can manually cut-and-paste the columns, or use Excel functions to move the data across into the correct column order.

A better way to do this is to insert into your Contacts CSV file as many columns as there are fields in the exported Mozilla Address Book file to the left of your Contact data, and then use functions to copy information from the original columns to the newly inserted columns. These new columns can then be pasted into a blank Excel file and saved.

Some of the fields won't match exactly - for example, Mozilla only has two "Street" fields for an address, whereas Outlook has three. In this case, you can use the concatenate function to combine the second and third "Street" fields in the rearranged column order. For example, =if(Street3Cell = "", Street2Cell, Street2Cell & " " & Street3Cell). Unfortunately, you will need to check this yourself.

I also found it good to use an "if" function to avoid copying a "0" value into a column that is blank in the original file. For example, you can input =if(Cell1 = "","",Cell1), which will copy the value if it isn't empty.

- Import your rearranged CSV file into Mozilla address book:
Save the rearranged Contacts CSV file as a new CSV-format file. Go back to Mozilla's Address Book and import the file (in the wizard, import "Address Books" as a text file).

- Export the just imported addresses as an LDIF file:
Select the newly imported address book and export it from Mozilla, this time as an LDIF file.

- Import the LDIF file to Address Book:
Go to OS X's Address Book and import the LDIF file (File: Import) and you are done.

Other people may have other suggestions, but this is the way I know how to do it.

[blodwyn]

Originally Posted by mileslong

i have a ton of business contacts info on an excel spreadsheet and I want to import them into address book but the only options it gives me is for vcard, text file and LDIF, whatever that is. i really dont want to have to retype them all by hand, do i have any options??

Sure, save the Excel spreadsheet as a CSV file and then import the CSV as a text file into Address Book. Before you do, make sure you have row A of the spreadsheet containing the headers for the rows underneath. E.G. Column A could contain last names, so cell A1 would have the text "Last Name" in it. This makes it easier to import into Address Book as you can map the columns into the right Address Book fields.

[Le Big Mac]

Originally Posted by mileslong

i have a ton of business contacts info on an excel spreadsheet and I want to import them into address book but the only options it gives me is for vcard, text file and LDIF, whatever that is. i really dont want to have to retype them all by hand, do i have any options??

Do you have entourage 2004? You could also import into that, and then do a sync to address book.

[Charlie Brown]

I am trying to import my contacts as a CVS file from Excel into Address Book. One of the main problems I am encountering is that my contact info is bilingual - in English and in Chinese. The Chinese characters are importing as plain squares and not in their original readable format. Has anyone got any solutions to this problem? It would be greatly appreciated!

[SpiceGrrl]

God, that sounds awful with the Chinese characters. Hope you got it worked out.

Meanwhile, months later, I am reading this thread because in a similar situation (but minus the Chinese). I have several hundred contacts in an Excel doc and I would like to import that data into Address Book. The data did NOT come from Outlook into Excel. I never even used Outlook for contacts; Excel was the file format from the beginning.

Now I want to import the Excel info into Address Book--selectively. I don't want ALL fields because some of the fields are made-up by me (like did we send them a Xmas card in 2010) and won't translate into Address Book.

Sorry if I am being dense but can someone please give a little more detail on the steps to accomplish this?

BTW I do have Numbers so if it would make it easier to convert the Excel doc into a Numbers doc first, I can probably do that.

[takeshi74]

Remove whatever columns you don't want to import and (as stated above) save as CSV and import the CSV.

[SpiceGrrl]

Thanks! Um, will the info go to the right places in Address Book? In other words, how does it know that this string of characters is a street address whereas that one is a home phone? I have given the columns standard titles (First, Last, Street Address, City, State, Zip, Home Phone, Work Phone, Email) but somehow I am feeling insecure that Address Book will recognize them and put the stuff in the right places. Can you reassure me or give me any tips about how to make sure it goes smoothly?

[GGJstudios]

Originally Posted by SpiceGrrl

Now I want to import the Excel info into Address Book--selectively. I don't want ALL fields because some of the fields are made-up by me (like did we send them a Xmas card in 2010) and won't translate into Address Book.

Make sure your Excel file has column headers identifying each field. Save your Excel file as a tab-delimited text file. Then import that file into Address Book and make the proper selections for which Excel fields you want in which Address Book fields. You can select to ignore Excel fields that you don't want to import.

[SpiceGrrl]

OK, sounds good, I will try that! Have not heard of a "tab-delimited text file" before so I hope that will be clear when I get to it. But I am glad to know that I can tell Address Book which fields to put where. That is great news. Thank you.

[GGJstudios]

Originally Posted by SpiceGrrl

OK, sounds good, I will try that! Have not heard of a "tab-delimited text file" before so I hope that will be clear when I get to it. But I am glad to know that I can tell Address Book which fields to put where. That is great news. Thank you.

Saving in Excel:

Importing into Address Book:

[SpiceGrrl]

Awwwesommmme! I love pictures. So helpful. Many thanks!

[beirdo]

Thanks for saving me a lot of time! Worked perfectly.

[matNZ]

Hello, I have been driven demented by my Mac mail. II am a list manager for a community email list. I want to transfer a group email list (includes about 300 email addresses) to my Mac mail box. I do not want to have to do this manually. I have had to resort to copying the email list into an excel document then coping to a bcc line in an email. It does not always work and doesn't solve the problem long term. How can I transfer the list intact and have it come up in my Address Book as a group email list?

Any suggestions? Thanks.