Although Excel does support XML export, the format is bloated, and the resulting XML data is still basically a spreadsheet: a collection of columns and rows. TableToTree allows you to collapse your data into logical and efficient structure, storing it hierarchically into a tree.
TableToTree is currently a GUI program. It was designed to make it easy enough for non-programmers (i.e., designers) to use. If there is sufficient interest, I can produce a command-line version of the tool for developers to use in automation tasks.
TableToTree is a free application.
TableToTree requires OS X 10.4 or higher, or Windows XP or higher. On OS X, TableToTree is a Universal application, which means it will run natively on both PowerPC and Intel Macs.
When TableToTree launches, you will see a new document window. The software can import your data from a tab-delimited text file on disk or from tab-delimited clipboard text. The first row in your file should contain the column names. TableToXML will use this first row to construct the element tags in the XML it produces. Below, we have some sample data in an Excel spreadsheet:
To import your data, simply choose “Open Tab Separated Text…” from the file menu, or Paste from the Edit menu. For the above example data, I’m going to copy the data from Excel and paste it into TableToTree. This is because—at least in the version I’m using (Excel 2004)—Excel’s .txt export reformats text fields that appear to be numbers, such as our FIPS fields, above, by removing the leading zeros.
Above, we have our data pasted into TableToTree. The column header row from the original data appears in the list at left.
Now we specify the structure of our XML file; we can create Elements and Nodes. First, the Elements:
Drag the column names from left-side list into the list on the right. Once items are in the right-side list, they can be further arranged hierarchically by nesting.
Next, the Attributes:
Right-click on each row and select the columns to be included with each element.
When TableToTree exports an XML file, it “collapses” the table down using the hierarchy specified here, grouping fields into XML elements that contain unique data “signatures.” This concept might be a bit confusing, but press on, because it will all make sense once we take a look at the exported XML file.
Choose, “Save XML Data…” from the File menu; you will be prompted to name and save the file.
Now, open up the XML file. (I recommend FireFox, because FireFox has support for viewing XML documents.)
See what happened? Our original table—with its redundant column data, is now collapsed down into an efficient tree structure—we can see the nodes we’ve nested as well as the attributes we’ve attached to each node.
As a concrete example: Our original table contained multiple row entries in the “State Name” column. TableToTree used these identical fields as a grouping strategy. In other words, each unique value that appeared in the “State Name” column became it’s own XML element. This element was in turn subdivided, and so on, until all leaf nodes in our tree are exhausted. For each element in the tree, TableToTree will use the combination of element name and attributes to partition the table rows. Handy!
If you look carefully, you’ll notice one other thing: The XML element and attribute names are slightly different than they were in our spreadsheet. This is because XML places some restrictions on what it considers a “legal” element or attribute name: no spaces, names can’t begin with a number, etc. TableToTree performs some filtering of the column headers to make sure they are valid for XML.
TableToTree has one more trick up its sleeve, something that makes repeated exports easier. Say that you’ve updated your spreadsheet with some corrections. Now, rather than go through the entire process of importing your text and redefining your XML structure, you can simply reimport your last saved XML file. TableToTree will read the file and reuse the structure for your updated data! Just choose, “Import XML template…” from the File menu and choose a previously exported XML document (or an XML file saved with the “Export XML template…” command.
DownloadTableToTree 1.1.2 OS X
(2575 downloads since 2011-02-04)
TableToTree 1.1.2 Win32
(618 downloads since 2011-02-04)