Sample Conversion Scenarios and Scripts

Let's look at a few real-life use cases in which we can build a functioning system from the components discussed above.

Purchase Order: UBL to XML to CSV

There are a lot of desktop bookkeeping systems in use that still use CSV files for importing and exporting data. I know of one, I'll call it BlueberryBush, that uses CSV formats very similar to the examples presented in Chapter 7. A few major buyers are starting to use XML formats for their purchase orders. Several such formats are emerging, but very few have much cachet as standards.

We talked in Chapter 4 about the business document schemas being developed by OASIS's UBL technical committee. Let's suppose that a big grocery chain adopts the UBL Order format. The grocer gives its suppliers an option to log into a Web site and download the orders in the UBL Order format. Let's say that Big Daddy's Gourmet Cocoa is running BlueberryBush and receives these UBL orders. Figure 11.2 shows the processing flow. Note that in this figure and others later in the chapter I use a convention of referring to the XML representation of a legacy format by prefixing it with the legacy format name . For example, the XML representation of a CSV format, used as input to the XMLToCSV utility, is named "CSV XML."

Figure 11.2. UBL Order to XML to CSV Order

We have several options for what to do on our receiving system based on its capabilities. Let's assume, since BlueberryBush is a Win32 application, that Big Daddy's PC systems support consultant, Sheila, knows her way around pretty well. She sets up and installs MSXML, the CSV conversion utilities, and a Perl interpreter. She tells Big Daddy to download the orders into a specific directory, then run this little job from a desktop icon. Here's the DOS bat file that will do the conversion.

bat File to Convert UBL to CSV (UBLOrderToCSV.bat)

 rem UBLOrderToCSV.bat rem Transform UBL Orders in Received to Transformed perl UBLOrderToCSV.pl rem Convert CSV version of XML to CSV Orders XMLToCSV C:\Work\Transformed C:\Work\Invoices.csv     C:\Blaster\XMLToCSVInvoice.xml -v 

To run through all the UBL orders and turn them into the XML format that the XMLToCSV utility expects, she codes a small Perl script.

Perl Script to Transform UBL Orders with XSLT (UBLOrderToCSV.pl)

 #!/usr/bin/perl opendir(INDIR, 'C:\Received')   or die "Problem opening current directory"; $file = readdir(INDIR); while ($file) {   if ($file =~ m/.xml$/)   {     print "Transforming ", $file, "\n";     $comm = "msxsl C:\Received\" . $file .         " C:\Blaster\UBLToCSV.xsl -o C:\Transformed\" .     $file;     system $comm;   }   $file = readdir(INDIR); } closedir INDIR; 

The Perl script opens the C:\Received directory, and for each file it finds that has an xml extension it builds a command line for msxsl. The command line takes the input XML document in C:\Received and transforms it with the UBLToCSV.xsl stylesheet into a file of the same name in the C:\Transformed directory. The script then invokes the command line with the system function. We can certainly get more diligent about error recovery and housekeeping in these two scripts, but the basic operations would remain the same.

Invoice: Flat File to XML to EDI

Now let's expand on the conversions in Chapter 8. BlueberryBush has been bought by MegaSoftware and retired . Big Daddy applies his special BlueberryBush user discount to upgrade to MASH2004. This program uses the flat files we discussed in Chapter 8. Big Daddy has started to sell to some major grocery chains, which want him to send their invoices using X12 EDI, version 004010. However, he still doesn't need to do a lot of EDI, and he doesn't want to spend thousands of dollars for a capable desktop EDI system. He also doesn't want to spend his time on a WebEDI service entering invoices manually. Sheila sets up the FlatToXML and XMLToX12 utilities and a stylesheet to perform the conversions. Figure 11.3 shows the process.

Figure 11.3. Flat File Invoice to XML to X12 Invoice

The bat file and Perl scripts are very similar to the CSV conversion.

bat File to Convert Flat File to X12 (InvoiceToX12.bat)

 rem InvoiceToX12.bat rem Convert flat invoice to XML FlatToXML C:\Work\Invoices C:\Work\XMLInvoices     C:\Blaster\FlatToXMLInvoice.xml -v rem Transform the XML representations from flat to X12 perl InvoiceToX12.pl rem Convert XML to X12 invoices XMLToX12 C:\Work\X12XMLInvoices C:\Out\Invoices.x12     C:\Blaster\XMLToX12Invoice.xml -v 

Perl Script to Transform from Flat File XML to X12 XML (InvoiceToX12.pl)

 #!/usr/bin/perl opendir(INDIR, '.')   or die "Problem opening current directory"; $file = readdir(INDIR); while ($file) {   if ($file =~ m/pl/)   {     print "Transforming ", $file, "\n";     $comm = "msxsl C:\Work\XMLInvoices\" . $file .         " C:\Blaster\FlatToX12Invoice.xsl         -o C:\Work\X12XMLInvoices\" . $file;     system $comm;   }   $file = readdir(INDIR); } closedir INDIR; 

This little system performs pretty much the same function as a good desktop EDI system at a small fraction of the cost.

Campaign Contribution Reporting: CSV to XML to Flat File

Let's now look at a scenario that's a bit different than the first two above. I'm on the board of a nonprofit group based in Seattle known as the Electronic Reporting Design Institute (www.erdi.org), which seeks to facilitate government- mandated reporting of contributions to political campaigns . That data is currently collected and reported to state and federal governments in a variety of formats including paper, CSV files exported from spreadsheets, and various flat file formats. The data is collated and stored by various means, but for this discussion let's assume that several states import the data into their systems by flat files (at least when they can get it electronically ). The ERDI is working on a standard XML document to report campaign contributions. But, even if and when this schema gets created, it will probably take awhile for the campaign organizations and political parties to acquire software that would create these documents in a native format. How do we solve this problem?

Here's one solution using the utilities from this book. It involves two pieces. Figure 11.4 shows the first piece.

Figure 11.4. Campaign Organization Reporting

First, reporting organizations are given a standard CSV format to use when exporting data from a spreadsheet or other application. They are also given the CSVToXML utility with an appropriate file description document, schema for the resulting file, and an XSLT stylesheet to transform the result into the standard ERDI format. (I'm aware that new versions of certain spreadsheet products have the ability to create XML. But do you think these campaign organizations are running them?) The users enter the data into their spreadsheet or other application and then export it to a CSV file. They run the XSLT transformation on the CSV file, then send the document to the state. Figure 11.5 shows what the state does with it after that.

Figure 11.5. State Processing

When the state receives the document, the users have an XSLT stylesheet to transform the ERDI format into the XML representation of the existing flat file format, run the XMLToFlat utility on the result, and load the flat file into the reporting system. Figure 11.5 assumes that the state system is on a UNIX box running Xalan and the Java version of the utilities.

Figure 11.6 shows the whole system, representing both the reporting organization and the state.

Figure 11.6. Complete Campaign Finance Reporting Solution

All of this could be accomplished with very simple Windows bat files and UNIX scripts very similar to what we've seen so far. This would be a very simple, low-cost, and readily available solution for everyone involved.