copy

Sunday, 17 November 2013



Quick Weekend Tips


About the Quick Weekend Tips (QWT) post series



QWT is a series of posts that are different from the “typical” posts that are being published in this blog. As the name implies the posts are published during the weekends (Saturday or Sunday) and their subject is quick/short tips about various applications (Excel, Power Point, AutoCAD etc.). These quick tips are accompanied with step by step instructions, as well as with a lot of pictures.



QWT 3: Export Your Gmail Contacts In Excel



In this post you will learn how to export all your Gmail contacts in a comma-separated values (CSV) file and afterwards, how to open this CSV file in Excel and make it readable using the “Text to Columns” method. For the later, an alternative option is given: to use the “Gmail Contacts Importer”, a custom workbook that I developed using VBA.



Export Gmail contacts in a CSV file



The procedure involves four discrete steps:

Step 1: Open your Gmail account and:
  1. Click the Gmail dropdown list which is located under the Google search box at the upper left corner of your browser. 
  2. From the dropdown list select Contacts.
Gmail Export To CSV 1

Step 2: In the contacts page that will open:
  1. Click the More dropdown list.
  2. Press the Export option.
Gmail Export To CSV 2

Step 3: In the Export contacts window:
  1. In the “Which contacts do you want to export?” question select the All contacts radio button.
  2. In the “Which export format?” question choose the Google CSV format radio button.
  3. Press the Export button.
Gmail Export To CSV 3

Step 4: Finally, in the last window:
  1. Choose the Save File radio button.
  2. Press the OK button.
Gmail Export To CSV 4

All your Gmail contacts will be saved into the CSV file, named “google.csv”. However, if you simply open the CSV file in Excel it will be quite difficult to read it. In the next section you will learn how to make the CSV file contents readable.



Text to column method



This method can be completed in 4 or 5 steps depending on your choice:

Step 1: Open the “google.csv” with Excel and select all the A column.

Text To Column Method 1

Step 2: In the Ribbon:
  1. Select the Data tab.
  2. Click on the Text to Column button.
Text To Column Method 2

Step 3: In the step 1 of the wizard that will pop up:
  1. Choose Delimited file type.
  2. Press the Next button.
Text To Column Method 3

Step 4: In the second step of the wizard:
  1. Choose the Comma delimited only. There is no need for other delimiters.
  2. Press the Finish button in order the conversion to take place OR...
  3. If you want the converted text values to be placed into a different location click the Next button (optional).
Text To Column Method 4

Step 5 (optional): In the final step of the wizard:
  1. Choose General data format.
  2. Enter the cell destination. In that cell the converted values will start to be placed.
  3. Press the Finish button in order the conversion to take place.
Text To Column Method 5

After finishing the above - 4 or 5 step - procedure your contact list will look like this:

Imported Gmail Contacts

You can now save the CSV file as XLSX file in order to use your mailing list like a typical Excel workbook:
  1. Go to File → Save As button.
  2. Choose the location you want to save the file.
  3. Enter a name (i.e. Contacts List).
  4. Choose the Excel Workbook format.
  5. Press the Save button.
Save CSV File As XLSX File



Import CSV file using the Gmail Contacts Importer



The short video below shows the 3 simple steps required in order to import your Gmail contacts (CSV file) in Excel:
  1. Open the Gmail Contacts Importer workbook, enable macros and press the Select CSV button.
  2. Find the location of the CSV file in your hard disk and press Open button.
  3. Press the Import Contacts button and the contacts will be imported in the sheet Contacts.

You can download the Gmail Contacts Importer in the download section below. The Gmail Contacts Importer was based on an older code that I developed.



Downloads



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.

Did you like this post? If yes, then share it with your friends. Thank you!



Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info