How to pin a pile of addresses onto a Google map

Turning a list of names, addresses and related information into a Google map is a lot easier than you might think. The effort required depends, as you might imagine, on the information that you starting with. But if the format is fairly consistent, it’s relatively easy to massage the information into a form that can be uploaded into a format that works.

First, what you can expect

Once you’ve loaded a list of names and addresses into a Google map, you will be able view the location of each person and set up your map such that clicking on any of the map markers displays the information collected for that address.

To get to that stage, you need to do a number of things:

• Verify the consistency of the data
• Parse the address list to pull out the relevant information
• Put the extracted fields into a CSV (comma-separated value) or related format that Google maps can use
• Log into Google maps
• Create a new map
• Upload the information from the CSV file or spreadsheet onto the map
• Click on some markers and make sure it works as expected — as in the example below

Google

Starting with your data

Say you have an address list for some organization that you belong to and you want to see how geographically dispersed the members are. The first thing you should do is examine the list of addresses and determine how consistent it is. Does each entry in the address list have the same numbers of lines? Are the lines labelled in some way? Are the fields in the same order? Here are two examples:

A labeled listing

Name: Anne Marie Palmer
Address: 123 Songbird Lane
City/State: Winchester, VA 22603
Home phone: (123) 456-7890
Mobile phone: (123) 567-8901
Email: notme@gmail.com

An unlabeled listing

Anne Marie Palmer
123 Songbird Lane
Winchester, VA 22603
(123) 456-7890 (home)
(123) 567-8901 (mobile)
notme@gmail.com

Each of these entries has six lines. If other entries have only five — maybe no home or mobile number — your parsing has to be a little more complicated. You might also have some entries where no email address is included. Overall, labels are helpful, but they are not always needed. In the unlabeled example above, you might notice that four of the lines include numbers, one has an @ sign and the last has neither of these things. These observations should prove very useful.

Parsing your data

In preparation of loading the data into a Google map, you’re going to need to reformat the list you’re starting with into a format that looks like this:

Anne Marie Palmer:123 Songbird Lane:Winchester, VA 22601:(123) 456-7890 (home):(123) 567-8901 (mobile)

Working with fields containing commas makes using traditional CSV (comma-separated values) very tricky. The format above is a variation on the CSV format in that the fields are separated by colons to allow us to include commas within the fields. If you use this format, you then need to load the reformatted data into a spreadsheet and save it in a format such as xlsx. Google maps will accept data from any of these formats:

• csv
• kml
• gpx
• xlsx

I’m not familiar with all of these formats. I use xlsx which can be generated by Excel, Open Office Calc and probably other spreadsheets as well.

So, let’s look at our sample data again:

Anne Marie Palmer
123 Songbird Lane
Winchester, VA 22603
(123) 456-7890 (home)
(123) 567-8901 (mobile)
notme@gmail.com

If the information you’re working with is consistently six lines and in the same order, parsing is dead easy. Substitute your preferred language if you don’t like Perl. The code below is setting up the input and output file, reading through the address listing, assigning each of the six fields to a variable that it writes to the output file once it reaches the sixth line, and then resetting the line number and starting with the next record.

#!/usr/bin/perl -w

use Switch;
my $input_file = "./address_list";
my $output_file = "./addresses.csv";
open(INPUT,"
$output_file") || die "Can't open $output_file for writing!n";
$name="";$addr="";$city="";$home_phone="";$mobile_phone="";$email="";

print OUTPUT "Name:Address:Home Phone:Mobile Phone:Emailn";

$line=1;

while () {
  chomp;
  switch ($line) {
    case 1 { $name=$_; }
    case 2 { $addr=$_; }
    case 3 { $city=$_; }
    case 4 { $home_phone=$_; }
    case 5 { $mobile_phone=$_; }
    case 6 { $email=$_; $line=0;
             print OUTPUT "$name:$addr,$city:$home_phone:$mobile_phone:$emailn";
           }
  }
  $line++;
}
close INPUT;
close OUTPUT;

If your information is inconsistent with respect to the number of lines, you will have to work a little harder. If you can’t depend on having two phone numbers and an email address, your script will have to look at each input line more closely.

In this next script, we are not depending on line numbers but examining the content pattern in each line. For example, the inclusion of an @ sign identifies an email address while three digits inside parentheses indicate a phone number. In the sample data, the words “home” and “mobile” are used to differentiate phone number types, but you might have to handle work numbers, as well.

This next script also considers any line that ends in five digits as being the city, state, and ZIP code line, while any line that begins with digits is taken as the street address.

#!/usr/bin/perl -w

use Switch;
my $input_file = "./address_list";
my $output_file = "./addresses2.csv";
open(INPUT,"
$output_file") || die "Can't open $output_file for writing!n";
$name="";$addr="";$city="";$home_phone="";$mobile_phone="";$email="";
$line=0;

print OUTPUT "Name:Address:Home Phone:Mobile Phone:Emailn";

while () {
  chomp; $line++;
  if ($_ =~ m/([0-9]{3})/) {  # phone number (area code)
    $phone=$_;
    if ($phone =~ /home/) {
      $home_phone=$phone;
    }
    if ($phone =~ /mobile/) {
      $mobile_phone=$phone;
    }
  } elsif ($_ =~ m/@/) {        # email
    $email=$_;
  } elsif ($_ =~ m/^[0-9]/) {   # address
    $addr=$_;
  } elsif ($_ =~ m/[A-Za-z]/ && $_ =~ m/[0-9]{5}$/) {   # city, state and zip
    $city=$_;
  } else {
    if ($line != 1) {           # output preceding record & reset fields
      print OUTPUT "$name:$addr:$home_phone:$mobile_phone:$emailn";
      $name="";$addr="";$city="";$home_phone="";$mobile_phone="";$email="";
    }
    $name=$_;
  }
}
# print last record
print OUTPUT "$name:$addr:$home_phone:$mobile_phone:$emailn";
close INPUT;
close OUTPUT;

Even if you are working with many thousands of records, parsing the data and turning it into a colon-separated listing is likely to take only seconds.

Mapping your data

Once your address list has been turned into a colon-separated file, you can load it into a spreadsheet, specifying that you are using a colon as your field separation character. Save the spreadsheet in xlsx format.

Open a browser, and go to Google Maps (make sure you are signed in).

• From the menu (upper left, looks like parallel lines), select “your places”
• Click on MAPS
• At the bottom, click on “CREATE MAP”
• In the middle of the box that appears, click on “Import”
• Select the xlsx file from your computer or drag it into the box

You’ll be prompted choose the column that represents the map location (i.e., the address) from a list of your fields. This is the most critical information for Google maps, and it will ignore addresses that don’t have street addresses (e.g., those with PO boxes). Choose the address field for this. Next, choose the field that will be used as the title for each location. This should be each individual’s name. Then click on Finish, and the map should soon show all of your markers. Clicking on any one of them should display the associated information — as illustrated in the example above.

Saving and sharing your map

There are only a few things left to do at this point — name your map and decide if you want to share it. Click on “unnamed map,” and replace it with a name. Then click on “share,” and add the email addresses of people you want to share the map with. Note that you can give them edit or simply viewing rights.

The benefit of maps

Turning large address, membership or customer lists into Google maps can give you a different and very useful perspective. And once you work the kinks out of the process, it can be a surprisingly efficient.