Yearly Archives: 2009

RSS to CSV Converter

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:

  1. Login to twitter
  2. Search for something (my sample: nascar)
  3. On the right-hand side, copy the “RSS feed for this query link”
  4. Go to RSS to CSV converter
  5. Paste in Twitter RSS link, changing “.atom” to “.rss” (my sample: http://search.twitter.com/search.rss?q=nascar)
  6. Click Submit
  7. 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.

Private Browsing as a Developer Tool

Having spent the better part of today debugging a problem with cookies on a couple different servers, I stumbled on the fact that the “Private Browsing” feature of Firefox was exceptionally handy for my situation. Private browsing is usually touted as a feature for “porn browsing”, or other activities in which you’re trying to not leave a trail. The same cleanroom features make it ideal for testing and developing web applications.

In a typical case where you’d need to clear browser history, clear cookies, and so on, I would usually keep one browser set up with developer tools (bugs, twiki, etc.), and launch another browser to be the guinea pig. With private browsing, I can now use my preferred browser (Firefox) for both cases.

Local Flash Files Reveal Browsing History

While doing some housekeeping cleanup on my Windows laptop, I noticed an interesting set of files under the Adobe/Macromedia Flash section in “Application Data”. Inside a directory called #SharedObjects was a set of sub-directories named after domains I had visited. Each directory contained one or more .sol files which I discovered are Local Shared Objects, basically local data storage for Flash components.

I’m not so worried about the privacy aspects of the sites using this local storage; it’s very similar to the issues with normal browser cookies. The surprising thing was the existence of this file set, essentially revealing all of the sites you had ever visited. In my case I had 217 unique domains from November 2007 through today.

For more info, see the recent article on Wired: You Deleted Your Cookies? Think Again, or the Adobe article What are local shared objects?. In the meantime, if you care about keeping your browsing history safe, remember to clear out these files regularly.

Screenshot of flash shared objects sub-directories

Cash for Clunkers Market Distribution

Here’s a quick comparison of the market share for US auto sales compared with participation in the Car Allowance Rebate System (aka “Cash for Clunkers”).

The distribution of rebates among top manufacturers tracks pretty closely to their recent market share. But, if you look closely, you’ll see that GM, Ford, and Chrysler are all slightly below expectations, while all the imports (except VW) are above.

Conclusion: import manufacturers got a larger share of the incentive program than their general market share would anticipate.

Chart comparing market share numbers with CARS data

Data sources:

Palm Desktop to Outlook: Better Contacts Migration

Having switched phones recently from an aging Treo 650 to a BlackBerry 8830, I once again put myself in the mess of transferring my calendar and contacts from one device to the other. In the past I’ve switched between Palm Desktop and Outlook by careful use of the Palm’s HotSync conduits. With the switch to a BlackBerry, those old tricks weren’t available. Luckily I was already syncing my Calendar with Outlook, so that left the migration of Contacts for me to solve.

I tried the old trusty CSV file export/import path, but even after painstakingly assigning field names to columns, the results were still jumbled (home phone number labeled as Work, and so on). The Palm Desktop CSV import/export process still has a few bugs in it, including not being able to reliably label the exported fields.

Enter vCard. Palm Desktop does have vCard support (for single or multiple contacts), and does fill in the fields more accurately. And Outlook accepts them as an import format. The only hitch was that Outlook can only handle vCard files with a single contact. Rather than repeat that process for several hundred contacts, I tried (then bought) vCard ImportExport by Topalt.com. This simple Outlook add-in lets Outlook handle vCards with multiple contacts.

With my new vCard software in hand, here’s the process that ultimately worked best for transferring my contacts from Palm Desktop to Outlook:

  1. In Palm Desktop, export each category of contacts to its own vCard (.vcf) file (e.g., Personal category -> personal.vcf)
  2. In Outlook, use vCard ImportExport to import the first category vCard file
  3. Select all contacts just imported and assign to that category (normally they come in with no category assigned)
  4. Repeat steps 2-3 for remaining categories

The only problem I encountered was that the Notes field from the Palm Desktop side ended up in Notes and in Web Page fields in Outlook. Not sure if this is a bug with the importer, or Outlook itself. I wrote a quick Outlook macro to clean those up.