Analyzing Twitter with Excel, Part 2

So, last week I set myself a problem: Use Microsoft Excel to capture Twitter messages that mention the word "rovio", pretending that I worked for the company that makes the Rovio Wi-Fi-controlled camera and I wanted to see what people were saying about it.

I set this all up to demonstrate Excel's XML Maps feature. The concept was to define an XML Map that retrieved XML data from Twitter's search feature. This feature offers to generate for your queries a news syndication feed in Atom format (that's a format much like RSS but slightly different for no particularly good reason). This is, in theory, great as Atom is a format that Excel understands.

After finishing last week's column I had to drop the project for a couple of days while I put out other fires. When I returned to it I discovered that things weren't working as I had expected.

If you recall the feed URL as provided by Twitter was:

http://search.twitter.com/search.atom?q=+rovio+since%3A2009-03-01+until%3A2009-03-25

I modified this as Excel didn't want anything to do with it for no obvious reason (the error reporting in XML Maps is, to say the least, poor).

I found that by replacing the '+'s (which stand for spaces in a URL encoded string) with "&"s (which separate variable/value pairs in HTTP GET requests) Excel would create an XML Map. I also added other arguments to set the number of results per page (rpp) and which page of the results should be returned and this is what I wound up with:

http://search.twitter.com/search.atom?q=rovio&since%3A2009-02-01&until%3A2009-03-24&rpp=50&page=1

Unfortunately this only appeared to work when used in ExcelT. The Twitter search just ignored everything after the first '&' and returned the last results available for the given search.

What this comes down to is four issues: The Twitter search interface is poorly documented; the Atom version of the search doesn't support all of the additional arguments (such as results per page); Excel is too fussy about which URLs it is willing to interrogate; and Excel also gives you precious little information about what it doesn't like.

This is all very annoying because I've used Excel XML Maps with all sorts of XML sources before and never had problems like these. I now hate both the Twitter and the Excel developers with a passion.

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

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