Go Back   MacRumors Forums > Apple Applications > Mac Applications and Mac App Store

Reply
 
Thread Tools Search this Thread Display Modes
Old Jun 22, 2007, 03:35 PM   #1
mileslong
macrumors 6502
 
Join Date: Apr 2005
Location: Newport Beach, CA
Can I import addresses in excel format into address book?

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??
__________________
(Hayden Christensen -- to merely call him wooden is an affront to puppets everywhere)
My Company
mileslong is offline   0 Reply With Quote
Old Jun 22, 2007, 04:16 PM   #2
Bern
macrumors 68000
 
Bern's Avatar
 
Join Date: Nov 2004
Location: Australia
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.
Bern is offline   0 Reply With Quote
Old Jun 22, 2007, 04:16 PM   #3
blodwyn
macrumors 65816
 
Join Date: Jul 2004
Location: Portland, Oregon
Quote:
Originally Posted by mileslong View Post
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.
blodwyn is offline   0 Reply With Quote
Old Jun 22, 2007, 04:30 PM   #4
Le Big Mac
macrumors 68020
 
Join Date: Jan 2003
Location: Washington, DC
Quote:
Originally Posted by mileslong View Post
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.
Le Big Mac is offline   0 Reply With Quote
Old Jun 26, 2011, 11:24 PM   #5
Charlie Brown
macrumors newbie
 
Join Date: Jun 2011
Follow-up question

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!
Charlie Brown is offline   0 Reply With Quote
Old Dec 14, 2011, 12:48 PM   #6
SpiceGrrl
macrumors member
 
Join Date: Oct 2010
Location: Los Angeles
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.
SpiceGrrl is offline   0 Reply With Quote
Old Dec 14, 2011, 12:53 PM   #7
takeshi74
macrumors 601
 
Join Date: Feb 2011
Remove whatever columns you don't want to import and (as stated above) save as CSV and import the CSV.
takeshi74 is offline   0 Reply With Quote
Old Dec 14, 2011, 12:58 PM   #8
SpiceGrrl
macrumors member
 
Join Date: Oct 2010
Location: Los Angeles
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?
SpiceGrrl is offline   0 Reply With Quote
Old Dec 14, 2011, 01:08 PM   #9
GGJstudios
macrumors Westmere
 
Join Date: May 2008
Quote:
Originally Posted by SpiceGrrl View Post
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.
GGJstudios is offline   0 Reply With Quote
Old Dec 14, 2011, 01:24 PM   #10
SpiceGrrl
macrumors member
 
Join Date: Oct 2010
Location: Los Angeles
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.
SpiceGrrl is offline   0 Reply With Quote
Old Dec 14, 2011, 01:32 PM   #11
GGJstudios
macrumors Westmere
 
Join Date: May 2008
Quote:
Originally Posted by SpiceGrrl View Post
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:
Click image for larger version

Name:	ScreenCap 10.PNG
Views:	104
Size:	72.9 KB
ID:	316105
Importing into Address Book:
Click image for larger version

Name:	ScreenCap 12.PNG
Views:	161
Size:	93.3 KB
ID:	316106
GGJstudios is offline   0 Reply With Quote
Old Dec 14, 2011, 01:45 PM   #12
SpiceGrrl
macrumors member
 
Join Date: Oct 2010
Location: Los Angeles
Awwwesommmme! I love pictures. So helpful. Many thanks!
SpiceGrrl is offline   0 Reply With Quote
Old Aug 24, 2012, 10:57 AM   #13
beirdo
macrumors newbie
 
Join Date: Aug 2008
I know it's an old forum, but...

Thanks for saving me a lot of time! Worked perfectly.
beirdo is offline   0 Reply With Quote
Old Dec 15, 2012, 08:31 PM   #14
matNZ
macrumors newbie
 
Join Date: Dec 2012
Transferring a Group List from Windows to Mac

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.
matNZ is offline   0 Reply With Quote

Reply
MacRumors Forums > Apple Applications > Mac Applications and Mac App Store

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Similar Threads
thread Thread Starter Forum Replies Last Post
Extracting multiple email addresses to contacts/address book PatOLeary Mac Basics and Help 0 Oct 7, 2013 05:10 PM
Export Addresses To Excel Klew721 OS X 3 Apr 6, 2013 02:37 PM
Export, Import Yahoo Address Book chattahoochee MacBook 1 Feb 21, 2013 05:31 PM
Applescript Import Into Address Book - A Better Way? Bonusgrabber Mac Programming 0 Jul 15, 2012 11:36 AM
Lost my Address Book addresses Gorillamo Mac Applications and Mac App Store 4 Jun 14, 2012 12:20 PM

Forum Jump

All times are GMT -5. The time now is 09:17 PM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC