Video Games

How to make a mailing list from Google Contacts

Create a file ready for merging—or for use by a mailing service—from one or more sets of Google Contacts

Illustration: Andy Wolber/TechRepublic

The following steps help you transform names and addresses you’ve saved in Google Contacts into a standard (comma-separated value or .csv) file you may use as a data source for postal service mailings. The steps also cover how to combine two or more sets of exported Google Contacts addresses into a single usable list without duplicates. I recommend you use a desktop-class browser, such as Chrome, since many of the export, import and data cleanup steps benefit from a laptop or desktop sized screen.

SEE: Electronic communication policy (TechRepublic Premium)

1. Export contacts

What’s hot at TechRepublic

First, go to Google Contacts on the web, then select Export from the left-side menu. If the menu isn’t displayed, click or tap on the three horizontal lines in the upper left corner to access the menu (Figure A).

By default, the system will export all contacts. To select a subset, you may select the drop-down menu, then Starred contacts, Frequently Contacted or any set of contacts by Label. (Alternatively, you may individually select contacts before you start the export process. To do this, click or tap on each contact’s profile image, then select Export.) If you plan to export contacts often, I suggest you create and apply labels to organize contacts in groups, since that makes selection simpler.

Choose the Google CSV format button, then select Export. The system should download and save a file to your computer. Typically, the exported file name is contacts.csv. You might rename the file to reflect the set of contacts exported along with the date (e.g., WolberContacts-20210818.csv).

Repeat the export process for each person whose Google Contacts you want to include on your mailing list.

Figure A

Screenshot of Google Contacts, with the three-horizontal line menu circled, and an arrow pointing at Export (after Import).

Export all (or some) of your Google Contacts in .csv format on the web.

2. Import contacts into a Google Sheet

Create a new Google Sheet (e.g., type sheet.new in Chrome), then select File | Import. Navigate to one of the contact files you saved above, choose it, then click (or tap) on Select. This should display an Import file screen. 

For the first contact data file, select Replace Spreadsheet, then Import Data. For additional contact data files, select Append to current sheet to place the imported contacts in rows after the end of current data (Figure B).

SEE: How to sync your Google contacts to your iPhone (TechRepublic)

Leave the Separator type as Detect Automatically, and also leave the box next to Convert Text to Numbers, Dates, and Formulas checked. Select Import Data.

Repeat the import process for each file. You should end up with all of your imported contacts files on one Google Sheet.

Figure B

Screenshot of Import file with

Within Google Sheets, import contacts. For the first contact list, select Replace Spreadsheet as the Import Location. For additional lists, choose Append to Current Sheet.

3. Delete duplicate header rows

If you imported more than one contact file, you will have a header row for each import. Keep only the first header row (i.e., row 1). Scroll down to each additional imported header row, click on the number of the row to select it, then choose Edit | Delete row (Figure C). After this step, you should have headers in row 1 along with all your contacts in a single sheet.

Figure C

Screenshot that shows additional header row (row 45), with a red line through it to indicate the need to delete it.

If you import more than one contact list into your spreadsheet, make sure to delete the additional header rows.

4. Delete columns 

Next, delete all the non-address columns (Figure D). Obvious columns to delete will be all columns to the right of column BY (i.e., Relation 1- Type). But you also will want to delete all other non-address fields, including email, birthday, directory server, mileage, etc. The goal is to end up with a person or organization and address data on each row.

SEE: How to create an envelope in Google Docs (TechRepublic)

As you delete, remove combined columns and keep individual columns of data, since these separate columns streamline sorting later in the process. For example, remove Column A (e.g., “Andrew J. Wolber), which is a combined column that includes the contents of column B (“Andrew”), C (“J.”), and D (“Wolber).

Since it is common to have more than one address for a person (e.g., home, work or additional work locations), review your data carefully before you delete any column with Street, City, P.O. Box, Region, Postal Code, Country or Extended Address information. In some cases, you may need to cut-and-paste address information from one set of columns into another (e.g., for a home to work address or vice versa) to ensure that all address information is contained in a consistent set of columns.

Figure D

Screenshot of email and phone columns displayed, with a red X over each column to indicate the need to delete it.

Select and delete all non-address columns, such as email addresses, phone numbers and so on.

5. Delete duplicates

If you’ve combined contact lists from multiple people, sort your list to identify duplicates. I suggest you sort three different ways: By business name, last name and street address. Review the list after each sort. Consolidate or delete data as desired. (For more details, read How to alphabetize in Google Sheets.)

SEE: How to migrate contacts to a different Google account on your Android device (TechRepublic)

Additionally, Google Sheets can help you find and remove duplicate data. Select a range, then choose Data | Remove Duplicates. Sheets will indicate the number of rows removed and the number of rows that remain. (For more details, read How to find duplicates in Google Sheets.)

6. Standardize abbreviations

The way many people enter addresses in Google Contacts may not match standard U.S. Postal Service abbreviations. For example, you might replace Road, Route, Street, Suite and Avenue, respectively, with RD, RTE, ST, STE and AVE for mailing purposes. (Review common abbreviations at USPS.com, as in Figure E.) In Sheets, use Edit | Find and Replace to streamline this process.

Figure E

Screenshot of USPS page of Street Suffix Abbreviations, showing Alley abbreviated to ALY, Annex to ANX, ARCADE to ARC and AVENUE to AVE.

Use standard postal abbreviations wherever possible.

7. Export your list

Select File | Download | Comma-Separated Values (.csv, current sheet) to export your list, as shown in Figure F. This creates and downloads a file with the file name of your document combined with the name of your sheet (e.g., Mailing List – contacts.csv). The comma-separated file includes the header row with the names of the data fields. This is the file that you may use as the source data for your mailing.

If you use a mailing service, it may take additional actions on your list to standardize mailing abbreviations you may have missed, add postal code data (e.g., ZIP+4) or update addresses due to moves (e.g., with the National Change of Address database). Mailing services may charge fees for these changes. You might want to update the names and addresses in Google Contacts with any changes identified.

Figure F

Screenshot of the Download | Comma-separated values menu displayed.

Choose Download | Comma-Separated Values to export your mailing list from your spreadsheet in a standard format.

What’s your experience?

Do you use Google Contacts as a data source for mailings? Do you standardize address information to reflect mailing standards when you enter data? How methodically do you update address information in Google Contacts after a mailing? Are there additional services you suggest that make mailings easier? Let me know, either in the comments below or on Twitter (@awolber).

Also see



 Source link

Back to top button
SoundCloud To Mp3