Do you ever look at Wikipedia’s huge comparison tables, like the Comparison of Wiki software? Are you ever frustrated by the sheer size of the table and the fact that you cannot quickly focus on the data which is relevant to you? There are two solutions I tried today: the TableTools Firefox extension and Google Docs. Read on for the details.
The Firefox TableTools Extension
You can download this tool here. Once installed, if you left-click in a table, you get two extra menu items labeled “Sort Table Column as” and “Other Table Operations”. Using “Other Table Operations->Show/Hide Select Filters”, you can turn on an additional row at the top of a table which provides filters for each columns. In the Comparison of Wiki software table, for instance, you can filter the “Open source column” to “Yes” and get rid of all the useless closed source crud. How neat is that? Even neater is the use of “Other Table Operations->Show/Hide Search Filters”. This brings up search filter fields above each column. These filters support regular expression searches on fields. For instance, the “Stable release date” column can be filtered with the regular expression “[A-Z]” to eliminate fields which are empty or have “?”. This leaves only wikis with a known stable release date.
This extension does not always work perfectly. For instance, if you just sort on a column without being careful you can end up with table headers in the middle of the table. It is possible to tweak the extension behavior in the preference dialog to minimize the likelihood of running into trouble. At any rate, these quirks are pretty minor and understandable. If a table is badly coded there is only so much the extension can do to work around that problem.
This extension works well when you only want to manipulate a table for your own purpose and if you don’t need to results to be permanent. What about if you want to share or store the results of your manipulations? Or what if you don’t want to install an extension? There is another solution.
I think there is a lot of hype out there about Google Docs but I am finding more and more cases in which Google Docs is a valuable tool. Manipulating web tables is one of these cases. The procedure is as follows:
1. Select in your browser the table you want to manipulate. This can be tricky for long tables. TableTools has a utility to select tables but every time I’ve used it, Google Docs complained that I cannot paste that much text. So I select my table with a click and drag.
2. Select the first cell of your Google Docs spreadsheet. If you don’t do this, it is likely that Google Docs will not paste the whole table. Then, hit Ctrl-V to paste. Check that you have pasted the whole table.
3. Perform any sorting you want. Be warned that Google Doc’s sorting capabilities are really primitive: alphabetical or reversed alphabetical and that’s it.
4. Select the content you want to filter. Most likely, this will be your whole table.
5. Go to “Insert->Gadget…” to bring the list of gadgets you can insert. Click the “Add to Spreadsheet” button of the “Table” gadget. It is described as “An interactive table with filters and grouping.”
6. Chose the gadget settings you want. I suggest manually setting the “Number of headers row” rather than leave it to Auto. I tried with Auto and Google Docs was confused. Click “Apply and Close” when you are done adjusting the settings.
7. The Gadget will be in its own separate little dialog box, floating above your sheet of data. I suggest clicking the down arrow on the top right part of the gadget’s dialog frame and selecting “Move to own sheet…”. Google Docs will move the gadget to a new sheet.
8. You are done. You can go to the new sheet and use the combo boxes to filter your data.
Note that the Table gadget is not able to filter data as powerfully as TableTools. It is possible to do more precise filtering in Google Docs by using formulas but for the novice spreadsheet user learning the proper use of formulas is more complicated than using the gadget or TableTools.
Moreover, it is possible to copy the table from a web page and paste it into OpenOffice Calc or Gnumeric rather than Google Docs. Both do a good job of parsing the table content into something intelligent and both have more powerful filtering capabilities than Google Docs. Where Google Docs shines relative to Calc or Gnumeric is its instant publishing capability.