Taking a huge list of names, addresses, phone numbers and email addresses and turning it into a Google map can be very useful and surprisingly easy.
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. Shear Pin Load Cell
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:
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:
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.
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:
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:
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:
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.
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.
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.
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).
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.
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.
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.
Sandra Henry-Stocker has been administering Unix systems for more than 30 years. She describes herself as "USL" (Unix as a second language) but remembers enough English to write books and buy groceries. She lives in the mountains in Virginia where, when not working with or writing about Unix, she's chasing the bears away from her bird feeders.
Load Cell 100kg The opinions expressed in this blog are those of Sandra Henry-Stocker and do not necessarily represent those of IDG Communications, Inc., its parent, subsidiary or affiliated companies.