Hatchomatic

Quite often, I need to turn a tab-delimited text file or Excel spreadsheet into an XML document. The easiest way to do that is with a spreadsheet that uses a formulas to build the XML into one column.

Here is such a file (actually, this is a collection of .xls, .xlsx, and Numbers ’08 and Numbers ’09 files).

What you’ll see is a seven-column spreadsheet with a header row. The seventh colum is simply a formula concatenating the other columns. That formula reads as follows:

=”<a “&$A$1&”=”"”&A2&”"” “&$B$1&”=”"”&B2&”"” “&$C$1&”=”"”&C2&”"” “&$D$1&”=”"”&D2&”"” “&$E$1&”=”"”&E2&”"” “&$F$1&”=”"”&F2&”"”/>”

What that’s saying is, the cell is equal to the literal text <a , followed by the absolute reference to cell A1, followed by the literal text =” followed by the relative reference to cell A2, and so on.

The nice thing about this is that you can quickly and easily change the headers in the spreadsheet, and the XML will change accordingly. Likewise, you can change the values. And, it’s relatively simple to add and delete attributes (columns) to grow or shrink the file as needed.

Enjoy!

Tags: absolute reference, amp, attributes, b2, c2, cell a1, cell a2, colum, d2, e2, excel xml spreadsheet, f2, header row, literal text, lt, n spreadsheet, nice thing, relative reference, xml document, xml template

This entry was posted on Wednesday, February 18th, 2009 at 11:05 am and is filed under USA TODAY. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.