Readers suggest tools for to address the daunting problem of converting XML formatted data to CSV.
Things may come and things may go, but the need to convert data formats will go on forever. That, young Jedi, is one of the eternal verities and, lest you think the conversion nut has been cracked, just consider the volume of responses I received when I mentioned my own reformatting quest in a recent column.
My task involved the problem of converting XML data to Comma Separated Variable (CSV) format. This is just one small example of the big hairy general problem of data format conversion.
Reader Jason Rabel from Houston said, “I read your article and got a good chuckle. I’ve had similar pleasures dealing with XML. Everyone loves to export XML data, but importing that data was/is still dark voodoo. So many sites give examples of how XML works and what to do, but there are no generic/simple tools that can get the job done for a person only needing the occasional parsing and conversion to a more common format. Also, ask for a DTD and they give you a blank stare like deer looking into headlights.”
Jason advocates PHP for its “decent XML abilities” but admits that the learning curve is steep.
Reader Don Hughes (White Plains, N.Y.) revealed: “I had a similar problem of parsing an XML stream when extracting map coordinate data from a Yahoo! xml data feed. I tried a number of packages with unsatisfactory results and ended up writing my own parsing program. However, as you pointed out, that can be less than robust and is somewhat fragile as the feed changes.”
As Don uses REXX for his utility programming, he resorted to RexxXML published by Patrick McPhee, which implements standard XML parsing functions. Don admits that “being a novice with XML, there were some rough spots, but the resulting utility is about 100 lines or so. The package can read from a URL, or file, and can validate against the XML definition.”
Don’s last statement should be amended to read “against the XML definition, if they’ve bothered to create one.” Without an XML Document Type Definition for any XML that isn’t trivially structured it is really hard to establish whether it is correctly formed.
Many of you wrote in suggesting the use of Microsoft Excel to convert XML to CSV. While there is some value to the idea it isn’t generally a great solution unless you are working with very simple and small data sets. What you are likely to wind up with is a mixture of macros and Visual Basic for Applications code and, if you’re really unlucky, a few ActiveX components to provide extra data munging. Yuck. The end result is more or less guaranteed to not be robust and will probably require you to shepherd your data through the conversion process. As part of a routine business process, Excel and a bunch of macros can’t really stand up.
That said, if you haven’t tried using Excel with XML you should give it a whirl — as an ad hoc toolkit it is useful and it is very good for analytical work.
Another Microsoft–based solution was posed by a couple of readers, including Tom Franciosi (Memphis, Tenn.) who wrote: “Microsoft Biztalk could handle the translation, but alas that would likely fall into the overkill category.” Tom also confessed that overkill solutions actually work for him. He “once killed a pesky bumble bee with a 20 gauge shot gun. True story. What? Oh, of course the missus was not home. Do I seem crazy?”
Next week we’ll look at a really interesting data format translation system that is, to put it technically, groovy.
