How to process a Google contacts .csv export in excel

Google contacts provides an easy way to export your contacts to csv (for details on how to export have a look at How to export Google contacts to CSV).

With a bit of data manipulation this gives you a great opportunity to be able to edit your contacts directly in Excel, allowing you to rapidly make bulk changes to your contacts. This is much faster than manually editing one contact at a time from Google’s interface.

The process involves the following steps:

1. Export your contacts to Google csv
2. Import into Excel
3. Edit in Excel
4. Export to csv
5. Import to Google
6. Merge with existing contacts

Step 1: Export your contacts to Google csv

For instructions on this step please refer to the dedicated article.

 

Step 2: Import into Excel

To import into Excel, load up a new Excel spreadsheet.

Now, go to the “Data” tab and select “From Text”

Select the csv file you exported in step 1

Choose “delimited” and press Next

Choose “comma” and press Finish

On the final prompt press OK

You now have a spreadsheet with all your contact information!

 

Step 3: Edit in Excel

You’ll notice that Excel will import a whole host of columns – up to around 160 of them.

Don’t worry thought, the most important ones are quite obvious. Here’s a quick overview:

1. Name: When editing the name fields note that someone’s first name is the column “Given Name” and their Surname is “Family name”. If you edit either of these fields, make sure to update the “Name” field. The “Name field is what you actually see in google contacts and is usually automatically calculated by combining Given name and Family name. Yomi name is the phoenetic representation of a name… you can usually just ignore this!
2. Email: You can edit a contact’s emails in the Email columns. Note that each email will have two columns, one for type and one for “value” which represents the actual email address. The type column can be blank if you want
3. Phone: Much like email, each phone has two fields
4. Address: Address entries have up to 9 fields. Feel free to use whichever of these are relevant to you
5. Organisation (work/job/company): These entries have 8 fields in total. However the ones you’ll most likely be interested in are Organization 1 – Name (the company’s name) and Organization 1 – Title (the person’s job title)

 

Step 4: Export to csv

Now that you’ve made all your edits you can export back to csv format.

To do this press File and Save As and choose “CSV (comma delimited)” type

Excel is likely to show a warning. Press “Yes”

If you see a second warning, press OK again.

 

Step 5: Import into Google contacts

To import your new csv file back into Google contacts go to More and Import and choose the file.

For detailed instructions please refer to the dedicated article here.

 

Step 6: Merge with existing contacts

Having imported your freshly edited contacts you now have two copies of each contact in Gmail (the original one and the edited one). All you need to do is merge them by going to More and then “Find & Merge Duplicates”

 

Easily export contacts and pictures from your phone

Did you know that Covve can help you export contacts and pictures from your iPhone or Android phone at a tap of a button? Export contacts by Covve is the easiest way to export your contacts in an excel-compatible format.