Webpage Digging..!!

Its gonna be another weekend yippee.. But can I make it productive? I gotta think of something and go for it. Here comes a challenge from my Boss. I was given a excel file in which a column contains thousands of official email addresses like surya@somedomain.com.It also had four empty columns namely domain name, company, address, phone number. Now the challenge is to fill up those columns with details. Sounds pretty simple huh? Certainly not!

I broke down this work into following tasks.

1. Get the domain name from the email address, which is pretty simple. Excel will do that for us. Or a dumb regex: (@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}) will do the trick hands down since the given emails are valid.

2. Now the job is to find the localized whois server where the information about the domain is present. For this I need to look up a general whois server like rs.internic.net or whois.internic.net or com.whois-servers.net, etc which will give details about the country wise local whois servers.

3. Then parse the information obtained from the local server to extract the company name, address and the phone.

4. Here comes the biggest challenge: The information in different servers is in different format. There is absolutely no common pattern. :(

5. Oh.. I forgot.. How am I gonna connect java to excel?

Let’s get to the bottom of the problem. I started off with the 5th task. Inspired by parashu’s go-sync (I am using it right now and modified slightly), I went for moyosoft. Yes, they provide a java library to communicate with Microsoft outlook and Excel. The following piece of code did the needed.

try{
Excel excel = new Excel();
try{
/* Create a new Excel workbook */
Workbook workbook = excel.openWorkbook(new File("temp"));
/* Get the active worksheet in the Excel workbook */
Worksheet worksheet = workbook.getActiveWorksheet();

for(int i=startingRow;i< endingRow ;i++){
worksheet.getCell(i, column).setValue(value[i-startingRow+1]);
}
workbook.save();
}
finally{
/* Dispose the library */
excel.dispose();
}
}
catch(ComponentObjectModelException ex){
System.out.println("COM error has occured: ");
ex.printStackTrace();
}
catch (LibraryNotFoundException ex){
System.out.println("The Java Excel Library hasn't been found.");
ex.printStackTrace();
}

Task 1 and 2 are fairly simple. I also found an apache open library for the whois client “apache.commons.net.WhoisClient”. But unfotunately It din’t fetch the details of the domain , instead it just fetched the toplevel details. Thanks to Microsoft I found a very light weight whois client which recursively goes through all the servers to get the full details about a domain. It was also way faster than apache library. (this is something unusual :o )

So , yeah.. now I have the raw text within hides the name, address, phone and other details. This may require a lot of complex regexes. In addition to this I should go for some heuristics as well. I applied some funny heuristics like

An address may start with “contact :”, “address :”, also look at the colon. Some do have a colon, some don’t and some ‘-‘ or just a space .

Most of the addresses end with an email or pincode or phone numbers .

Addresses in some servers contain fields like , Street, City, State, Country.

Company names may end in Inc. , Org. , Ltd. , etc.

Extracting phone numbers is not so tough as the other two, coz lot of countries follow some general phone number patterns.

Keeping all that in mind I wrote a series of regexes which performed reasonably well. It extracted information for around 75 % of the domains accurately. That was more than I expected. These are some of the regexes I used.
For phone numbers:

"(([\\+]1[\\.])[0-9]{10})"
"([\\+]?(91)[\\s|-]+[0-9]{2,3}[\\s|\\-]+[1-9]{1}[0-9]{5,7}\\s)"
"((\\s|:)\\(?(\\d{3})\\)?[\\- ]?(\\d{3})[\\- ]?(\\d{4})\\s)"
"((\\s|:)[+]?\\d{2}\\.\\d{9,11}\\s)"
"((\\s|:)\\d{3}\\-\\d{8}\\s)"
"((\\s|:)[\\+]?\\d{2}\\.\\d{10}\\s)"
"((\\s|:)[+][0-9]\\d{2}\\-\\d{3}\\-\\d{4}\\s)" "((\\+44\\s?7\\d{3}|\\(?07\\d{3}\\)?)\\s?\\d{3}\\s?\\d{3}\\s)"
"((\\s|:)[0-9]{2,3}[\\-]?[\\s]?[0-9]{6,7}\\s)"
"((\\s|:)0[234679]{1}[\\s]{0,1}[\\-]{0,1}[\\s]{0,1}[1-9]{1}[0-9]{5,7}\\s)"

For Company names:

"([\\s|:][[\\w]+|\\s|\\,|\\-|!|&|#|\\(|\\)]+\\s(Inc|Ltd|Org|Corp |Company|Corporation)[\\.|\\s])"

"((Organization)(\\s|:)+ ((\\w)+|\\s)+ [\\s])"

For Address:

"((contact|address|communication|street[\\d]?|city|state)[\\s]?[\\.|:|\\n][\\s]?[\\w+|\\s|\\,|\\-|:|#|&|\\.|\\n|\\(|\\)|!]+([[a-zA-Z0-9._-]+@][a-zA-Z0-9.-]+\\.[a-zA-Z]{2,3}[\\s])?)"

These regular expressions can be improved a lot. I would really appreciate if you come up with other regexes or optimize the above. I am not able to come up with more precise expressions in these two days. I am still working on them.

One good thing about the above regexes is that they are not missing the patterns for which I've written em, but they may accept the unintended patterns too :-p. To put in other words they are specific to whois info. :-D

It was also required to filter the extracted contents for unwanted words, spaces and other punctuation characters. Finally, here are the results. Hope to have a good week ahead. Catch ya.

Cheers,
Surya.