Analyzing Twitter with Excel, Part 3

For the last two weeks I've been considering how to analyze Twitter messages using Excel 2003. Having been thwarted by the deficiencies of Excel and Twitter (turns out that Twitter Search returns malformed HTML and Excel won't tolerate that) I have a new plan: Let's use cURL to retrieve the raw XML returned by Twitter search and then haul that data into Excel for the analysis.

CURL is a free open source tool that lets you perform data transfers specified by URLs (you can think of cURL as a sort of Swiss Army knife utility for Web data retrieval -- I give it 5 out of 5, it is that good). For some background on cURL check out my Gearhead column from a couple of years ago.
I'm going to use cURL to retrieve the Twitter Search data for a specific day. I can't do this efficiently in Excel using XML Maps because once you have defined an XML source you can't easily change it. You can change the XML Map URL through Visual Basic for Applications, but messing with VBA is how people descend into madness.

That said, I'm not sure that madness isn't a natural consequence of all software engineering and, in this case, the solution is going to be particularly demented and ugly as we're forcing weakly structured content to do what it was never meant to do.

Anyway, I'm going to employ cURL to retrieve the content from Twitter search using a batch file (I've wrapped the line below for readability -- you should combine the lines into a single line with no spaces in the text between the double quotes):

curl "http://search.twitter.com/search?q=rovio
 &since=%1&until=%1&rpp=100&page=1" --o tweets%1-1.htm

If the batch file is called, say, getrovio.bat, then the command line for this request would be of the form "getrovio 2009-04-01". This will get just the first page of matching Tweets for April 1, 2009.

Now if the term we're searching for is really popular we're going to need to run the search multiple times, but here's the problem -- Twitter will return a Web page even when there's no data so we need to analyze the content to look for the string "no results", which is what Twitter will report. So, we're going to use grep on the output file:

grep "no results for" tweets%1-1.htm

Sign up for ITworld's Daily newsletter
Follow ITworld on Twitter @IT_world

• Email
• Print
• Share
  • Slashdot
  • Digg
  • Stumble
  • Reddit
  • Newsvine