How can I work data magic to find hundreds of phone numbers fast?

March 18, 2008 12:38 AM

How can I work some data magic and save time looking up hundreds of phone numbers with data from an Excel spreadsheet?

I have a few hundred names and addresses of people to whom I must now attach phone numbers. The obvious route is to open the spreadsheet in one window, and a phone directory in another, and manually type in all the relevant info and search over and over again. But this would be extremely tedious.

I've considered teaching myself how to use some macro program to streamline the process so that I could automatically cut and paste fields from the spreadsheet into the browser and search. However, I'm not sure what program I'd use and what would be the quickest way to teach myself how to use it. I was considering AutoHotKey, but I'd welcome other suggestions.

I was also wondering if there are any sites or software with open databases of phone numbers. And could I use something like Yahoo Pipes to feed the data from one place to another?

I know how creative and helpful you MeFites can be, so I'm looking forward to what you can think up.

Thanks!

posted by abkadefgee to computers & internet (6 comments total)

If you buy a White Pages on CD, any number of scripts could do this for you fairly easily and quickly.

For example, let's say you have two delimited text files exported from Excel, called addressbook.txt and phonebook.txt:

addressbook.txt
Last name | First name | Street | City | State | Zip
...

phonebook.txt
Last name | First name | Phone
...

You could have a script like the following:

#!/bin/bash
if [ -f newaddressbook.txt ]
then
 rm newaddressbook.txt
fi

old_IFS=$IFS
IFS=$'\n'

cat addressbook.txt | while read line
do
 searchString=`echo ${line} | cut -d'|' -f1,2`
 cat phonebook.txt | while read listing
 do
  targetString=`echo ${listing} | cut -d'|' -f1,2`
  if [ "$searchString" = "$targetString" ]
  then
   phonenumber=`echo ${listing} | cut -d'|' -f3`
   echo "${line} |${phonenumber}" >> newaddressbook.txt
  fi
 done
done

IFS=$old_IFS


You'll get a new file called newaddressbook.txt which joins the address with the phone number:

Last name | First name | Street | City | State | Zip | Phone
posted by Blazecock Pileon at 2:27 AM on March 18, 2008

The script above should work fine for a few hundred phone numbers. If you're dealing with many thousands, you'll probably want a better solution.

A more efficient solution is to write a program to put all the phone number entries into a hash table, using the last name and first name as the "key" and phone numbers as "values".

You then loop through the address book one line at a time, testing if the last and first names of the line from the address book refer to a phone number in the hash table.

If there's a match, get the phone number, add it to the address entry and append that on a new file. Otherwise, skip to the next address book entry.

You might be able to do a hash table with Excel but that's beyond the scope of my knowledge of that program.
posted by Blazecock Pileon at 2:39 AM on March 18, 2008

i might be misunderstanding what information you have, but if you have a list of names on one sheet, and a list of names and telephone numbers on another sheet, then what you're trying to do is select just the phone numbers you need for the first list of names. in that case, could you not use something like vlookup?


in the directory page (call the sheet "directory"), i'm assuming you can put the names in column a and phone numbers in column B. lets say there are a hundred names and numbers for the example below.
in the sheet with the names, where the names are in column A, put in column B

=vlookup(A1, directory!A1:B100,2)

and paste that down as far as you have names.

in any other case, i don't see why you couldn't use the macro software in excel. you can edit the code in Visual Basic for Applications, so if you're not familiar, you can record your actions that you want to repeat over and over again which may be enough, and then go in and edit it to exactly what you want. you can do anything with this like opening dialog boxes for users to choose the files they want, or anything you can think of.

its dead easy to learn using this method, and there's plenty of code on t'internet that is easy to understand if you google "VB how do i......". There are plenty of VBA for dummies books too.
posted by galactain at 2:49 AM on March 18, 2008

Seconding galactain, maybe. VLOOKUP will do exactly what you need if you have the same "key" (e.g., name) in both sheets. No need to go all fancy Web 2.0 with it.

If, however, all you have are the names, and you'll need to look up the phone numbers for each person, that's a bit trickier. You'd either need to (a) write a script (VBA, Perl, whatever) that looks up each name on something like yellowpages.com; or (b) find a downloadable database of all phone numbers in the U.S. (with names), and then do the lookup galactain describes.
posted by Doofus Magoo at 6:21 AM on March 18, 2008

You need a database program.

Excel is not a database, even though you're trying to use it like one. If you're already familiar with Microsoft Office apps (and have it available), you may find Access somewhat more comfortable than anything else, at this point.

Access can treat Excel spreadsheets like database "tables", so you won't lose the data you already have. You can quickly create a front-end (quasi-application) which allows you to input a piece of data (like a name) and have Access spit out a phone number, address, or whatever else you have.

A database can also draw from different sources of data (i.e. other kinds of files) as long as you "join" them together. In your case, this might be a first & last name, or some other unique identifying piece of information for each record.

There are lots of other database packages (FileMaker comes to mind) -- now that you know the name of the computer tool you really need, Google may be more of a friend than a pit of despair. ;)
posted by catkins at 9:43 AM on March 18, 2008

Hmm... seems I was not very clear. I don't have the phone numbers that I need to lookup. I need to find them through a site like yellowpages.com .

As someone that is inexperienced in writing scripts, could anyone else point me in the right direction to figure something like this out?
posted by abkadefgee at 2:29 PM on March 18, 2008