Exporting contacts from the Contacts app to a spreadsheet may seem impossible until you learn one simple trick. You can also export a spreadsheet to a CSV format and import contacts into the app.
You can also watch this video at YouTube.
Watch more videos about related subjects: Contacts (11 videos), Numbers (196 videos).
You can also watch this video at YouTube.
Watch more videos about related subjects: Contacts (11 videos), Numbers (196 videos).
Video Transcript
Hi, this is Gary with MacMost.com. Today let me show you how to import and export from the Contacts app on your Mac.
MacMost is brought to you thanks to a great group of more than 800 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
Now I often get asked about how to import new contacts or export all of your contacts from the Contacts app on your Mac. I want to talk about both of them here because it's easier to demonstrate one if you see how the other works. In fact, I'm going to start with exporting. So even if what you want to do is import, it's important that you see what I'm doing with exporting so you can properly import contacts from another source. First let me point out that if you want to import or export using the standard vCard format then it's really easy. You can select one contact or many. You go to File, Export, and Export vCard. Or you can simply drag and drop. Dragging and dropping to the Finder will create a vCard file. VCF. This is a standard format that will work with other apps, not only on the Mac but on other systems as well.
But if you want to get them out into some sort of readable file, something where you can manipulate them say in Numbers, then it's a little different. If you go to File, Export your only options are the vCard format or a special contacts format that you can't really use for much else. Usually you want to get these into some sort of spreadsheet so you can actually do something with the data. It looks like it's impossible to do. But there's one trick to it and if you don't already know the trick you're going to be amazed. I'm going to go and run Numbers. Then I'm going to go and create a new Numbers spreadsheet. Just like a blank spreadsheet here. So now I've got this blank spreadsheet. It would be great to be able to get these contacts into this spreadsheet. Here's how you do it. Select the contacts you want. I'm going to Shift Click to select them all. Then you simply drag and drop and look at this. They all import into the table that you've dropped them into. The table even expands its size to accommodate and if you had no table there it would actually create a table. So just dragging and dropping from the Contacts app into Numbers works great. Here you can see I've got a Header row that has all of the field names and most of the data is there. It's not perfect. I've noticed that some data is usually missing. Like, for instance, there's at least one contact here that has a Twitter account listed and that's not exported. But for the most part you get phone numbers, email addresses, physical addresses, names, and other bits of information. Even the image is imported into Numbers as a background for a cell here. So that's it! You can now go and shorten up this table if you need to or get rid of some columns that you don't necessarily want. You can Save this as a Numbers spreadsheet or you can export it to a variety of formats like CSV or Excel. It's really that easy.
Now the reason I wanted to show you exporting before importing is that if you want to import you can go to File, Import and the format that you need is a CSV file. That stands for comma separated variables. It's a spreadsheet format that you can create in apps like Numbers. What's that supposed to look like? How do you build a spreadsheet table that will actually import properly into Contacts? Well, you don't have to wonder about that because by exporting first I now have a great example of exactly what the table needs to look like. So what I'm going to do here is I'm going to look through the columns and delete the ones that I really don't care about. So for instance maybe I want to import a bunch of people's contacts and I don't have work addresses. Just home addresses for them. So I can select these columns here and delete the selected columns. I could delete whatever else I find is unnecessary like maybe this image column here as well since I won't have that data. Now I can clear out the sample data here that I got when exporting.
Now I have a table ready to fill in. So here now I've added some data to it. Let me shorten this table up now. I've just got two names in here but you could be importing hundreds of names perhaps from a previous contacts database that you had. Perhaps from something you've gotten from a company or organization. So it's just a matter of working in the spreadsheet here to make sure the proper information is in the proper place. Get that all squared away. Then save this file so you have the original Number's spreadsheet. Then you want to export it. Go to File, Export To, and CSV, that comma separated variable format I talked about. You shouldn't include the table names and there's no advanced options to choose. Just select Next and export it as a CSV.
Now the thing about CSV files is they're just text files. So I could Control Click on it. Select Open With and let's open it in TextEdit to see what we've got. This is all it is. It's basically every row of the spreadsheet with commas separating each cell in the row. You can actually go and Edit this text file right here in TextEdit if you wanted to. Now let's bring it in. I'm going to go to File, Import. Then I'm going to select the CSV file. I'm going to use Open and I get this interesting little interface here. It's going to show me the field in the Contacts app and then what it finds in my CSV file. I can compare them back and forth to make sure they are what I want. So here it's looking at the first row which was all of the headings. So I can see here, for instance, that the last name goes to my last name column. First name goes to my first name column. I can compare all the way down the list. Now I don't want to import that first row so I want to select Ignore First Card. Then I could use this arrow here to go over one and see how the first contact looks. Last name, first name, phone number, email. That all matches up. I can look at the second one. I can go through them all if I want. If something is out of place I can simply click Cancel, go back to my Numbers spreadsheet, adjust it. Export a CSV again and keep doing that until everything is satisfactory. When I've got everything the way I want I just click Okay here and now you could see it imports those two contacts and here is each entry so it worked just fine.
Now if your goal is to actually create something you could print out or view as a PDF then you don't have to use any of that. You don't have to go to Numbers or export them or anything. Just go to File and then Print. If you don't see too many options here click Show Details. Then you get all these options. Make sure you select the Style, Lists. Mailing Labels, or Envelopes of course give you those things. But a List will give you a nice list of things from your Contacts. You can select exactly what you want. For instance I could have phone number, email address, not the photo, not the job title and just leave it like that. I could adjust the Font size. I can change the Orientation and I could Print this out or Save it as a PDF.
I consider myself above-average at manipulating text and data but never spent the time trying to understand how to import or export Contacts data. Nice tutorial on how to do this. Very helpful. Thanks so much.
I can see how to print my contacts but I cannot find a way to adjust the column width for printing. It seems as if I'm stuck with the width a default width.
Henry: Maybe bring them into Numbers like I show and customize them there.
Can I just export contacts in a group?
Jim: Go to that group, select all of those contacts, export to Numbers like I show in the video.
I tried to follow these directions as I would very much like to transfer both ways. I created blank spreadsheet in Numbers - opened contacts ( I have 788 contacts) highlighted all / dragged them to spreadsheet / a dialogue box comes up and says 'Spreadsheet is updating' but then it is always blank. I highlighted 10 contacts and went through same process and it worked. Do you have to use small groups? (I have extra characters... just want to thank you for all your shares...)
Lyn: Could be that 788 is too many. Maybe try 200 at a time? Or, maybe it is a particular entry. Try some at a time to narrow it down.
That was a really great and helpful video in many ways. I am saving a link to this for later, and also forwarding to friends and family who use Macs. Thanks for making this one!
this was a great video & timely as well. so "exported" into numbers, edited the rows & columns down to just under 400 lines. saved it as a CSV. imported into contacts & got over a total of 632 lines. the extra's are bits & pieces of other contacts.
Thanks Gary, I've been needing to do this to tidy up my nearly 4000 contacts. Like Lyn It wouldn't handle them all so I did it letter by letter.
Hey Gary, thank you for the video. I've watched several times but having troubles on one of my machines. It's only bringing over 4 fields and and out of order. For example if I have an email listed under "other email" it's just brining over first and last name of that person. No address at all. Any remedy?
Justin: Is that particular machine using an older version of macOS maybe? If it works on your other machines, just do it there instead unless you can figure out the problem.
Thanks Gary I have followed this exercise through with success but all my phone numbers now have additional before and after marks which I presume are to do with the textedit file eg ‭03 5441 8923‬ What did I do wrong?
Kath: Not sure. Do you see those in the CSV? Or maybe there are quotes around the phone numbers in the CSV?
Yes Gary there is a Quote filter attached in the CSV file. I have tried with one card to remove the filter and it works well. Many thanks
I had success with one card but how do I reformat the entire column in my numbers spreadsheet before converting to a CSV file. The columns remain on Automatic format and will not accept my alterations unless I re-enter all the numbers.
I tried to Format the column using Data Format/Cell/Create Custom Format as you showed in another video “Reformat Phone Numbers”
Kath: Not sure. It depends on how things appear and you experience. You could use text functions to grab the phone numbers from one column and only use the middle characters, stripping away the quotes. But depending on your experience and the amount of numbers, it may be easier to just do it manually.
This is useful thanks. Any thoughts on how to restrict the Mailing Labels to one address per contact? Some of my contacts have a home and a business address. I want to generate a Mailing Labels list to home addresses only.
Richard: Not sure. I've never tried that. Play around, but I'm not sure you can have that detailed of control over what is printed.
Hello Gary, I tried importing csv files with less than 100 contacts into the Contacts app but it does not do anything.I initialy tried with 575 but that didnt work. There are 6 columns of info, Last name, first name, Job, Company, Email, Country. I do get the dialog box of verifying the Fields but when I click ok to proceed nothing happens and I have to cancel out. Can you advise why this may be happening and what I can do to import?
Monis: Not sure what the problem could be. It should be pretty straight-forward.
Hi Gary. I am trying to get my contacts from Mohave to Big Sur. I have exported as .abbu and .vcf. Neither will import into Contacts nor into Numbers. What should I try? Thank you.
Victoria: From Mojave to Big Sur? So you just upgraded your Mac? They should be there without any other effort like exporting and importing. Are you sure they are not there already?
Hey, Gary, I'm running Big Sur 11.5.2 (current version), and I couldn't get even 15 selected entries to transfer. However, I was able to copy 1 contact at a time. Unfortunately, Numbers only shows last name, first name, company, and phone number. No email address, physical address, etc. I'm fascinated that it works at all, but it doesn't solve my problem. Thanks.
Michael: Instead of copying and pasting, try drag-and-drop. You can also try the export option. Perhaps the difference is where you have your contacts stored -- what system are they in? (iCloud, On My Mac, Google, Yahoo, etc.)
I have always dragged and dropped from my contacts into a numbers sheet and now it wont do anything? What am I missing? I have a bout 322 contacts to drag into numbers. I tried one at a time, nothing. all of them, nothing..I know it's going to be something so simple!
Kristi: Maybe it is one contact causing a problem. Try it with just a. few contacts, then more, and narrow it down.