Recently @tonyadam was asking about for a way to import Twitter search results into Excel via RSS or CSV. I couldn’t find a suitable method on my slightly out of date Excel 2003, but sent Tony a link explaining how Excel 2007 could get him closer to what he wants. Turns out he was on the Mac anyways, so we needed an alternative way.
Importing into Excel usual involves using the CSV format. So all we needed was an RSS to CSV format converter, but surprisingly couldn’t find anything relevant on the web. In general there isn’t much interest in XML to CSV conversion because it would only make sense for the simplest of XML files — basically, one table with rows & columns. Anything more complicated won’t fit within the confines of CSV.
To solve this problem, I created an RSS to CSV converter script, using a bit of YQL for the feed fetching and parsing.
We can test it using Tony’s original request — Twitter search results — as follows:
- Login to twitter
- Search for something (my sample: nascar)
- On the right-hand side, copy the “RSS feed for this query link”
- Go to RSS to CSV converter
- Paste in Twitter RSS link, changing “.atom” to “.rss” (my sample: http://search.twitter.com/search.rss?q=nascar)
- Click Submit
- Save CSV file when prompted, or import into Excel
Update 2011-09-11: Twitter’s UI changes have removed the old links for search result RSS feeds, so the above steps won’t quite work. See this article from Learning LibTech for an alternative way to find the feeds directly. Also, the source code for this script is now on Github.
One problem I discovered (again) is that Excel is not happy with Unicode characters in CSV format. There are some alternatives described, so I may enhance this script to properly handle UTF-8 if someone has a use-case for it.