Getting the data
To make a comma-separated value (CSV) file to work with, I used DB2’s EXPORT command for the sample database of employee records that ships with the product. The command I used was:
That generated the file test.csv , which you can download.
Parsing the comma-separated value (CSV) file
After I had the data file, I started looking around for some code to parse comma-separated values. I didn’t find exactly what I wanted, but I did stumble across the Java class StreamTokenizer. This class lets you design a rudimentary parser fairly easily. You select the delimiter between tokens, and it parses the file, converts data to strings and integers, and does some other nice things. You can view the code I wrote or download) it.
This article features colorized code listings, something we’re experimenting with here at dW. To generate our color-coded listings, I’m using a couple of open-source tools. First, I load the document (Java, HTML, XML, whatever) into Emacs. Emacs defines colors for keywords, comments, function names, and other programming language constructs — about a dozen in all. After Emacs has loaded and colored a file, I use the HTMLize package, an open-source utility written in the ever-popular Emacs Lisp language. HTMLize takes a listing exactly as it appears in Emacs, then converts it to HTML. The result is a fully color-coded file that highlights keywords, comments, function names, and so on.
Let us know what you think about these new and improved code listings.
If you’d like to do this kind of thing yourself, see Resources for the appropriate links.
To use this file, I typed:
That command opens and parses the CSV file and then converts it to XML. The XML for one employee looks like Listing 1. (You can also download output.xml.)
When I started working on this, I thought the first line of the CSV file would contain the column names from DB2. I was going to use those names as the XML tag names. I didn’t immediately find a way to get DB2 to export data in this format, so I just made up the column names, using the imaginative naming scheme you see in Listing 1.
Converting the generated XML
Now that I had this XML-tagged data, I needed to write an XSLT style sheet that would convert the XML above into a more useful (and understandable) tag set. You can view the style sheet listing in a separate window, and you can also download it and view it locally.
To convert the generated XML document using the rules in the style sheet and then write the output to the file called employees.xml, I typed:
If you want to run the transform code, you’ll need the Xerces XML parser and version 1.0.0 of the Xalan style sheet processor (see Resources), both of which are free and available at xml.apache.org. The resulting XML looks like Listing 2. (You can also download employees.xml and employees.dtd.)
|Employee||Sex||Serial Number||Department||Job Title||Date of Hire||Years of Education|
|Phone||Date of Birth||Salary||Bonus||Commission||Total Compensation|
|GEYER, JOHN B.||M||000050||E01||MANAGER||08/17/1949||16|
|GOUNOT, JASON R.||M||000340||E21||FIELDREP||05/05/1947||16|
|HAAS, CHRISTINE I.||F||000010||A00||PRES||01/01/1965||18|
I hope this helps. I’ve certainly learned some things along the way, and I hope you have too.
If you want to transform the XML documents, you’ll need the Xerces XML parser and the Xalan style sheet processor, both of which are free and available at xml.apache.org.