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.

Whitepapers

• 5 Biggest Blunders when Building Spreadsheet Applications in Java
• Automating Excel Reports: Five Approaches
• Java: Four Server-based Approaches
• 6 Simple Steps to Disaster Recovery Planning
• Boost Productivity While Cutting Costs with Next-generation Collaboration
• Deploying Virtualized NetWare on Linux Whitepaper

View more SOFTWARE whitepapers

Webcasts

• Creating a high performance workplace with wireless networking
• Harnessing the power of communications to increase workplace performance
• Making data centers the IT strategic focus
• Protecting assets and employees with IP Video Surveillance
• Stay out of the headlines: Detecting and preventing network intrusions
• Managing the End User Experience-A Real World Example of Success

View more SOFTWARE webcasts

Special Reports

• Executive Guide: Virtualization Reality Check
• WAN Optimization: The Ultimate No Brainer
• Keeping Spam at Bay
• Application Performance Management Executive Guide
• Executive Guide: Building a Service Oriented Architecture
• IT Buyers Guide To: Collaboration

View more SOFTWARE special reports