Can CsvPath create XML reports?

As well as being awesome for CSV, Excel, and JSONL validation, CsvPath Framework has always had cool printout tricks. And for quite a while it has also supported some XML and XPath operations. CsvPath is even a transformation language, similar (though not very!) to XSLT.

But when the boss wants to ship a machine-readable XML giving the validation status of newly arrived data, does CsvPath have your back?

Of course, it does. Or I'd be typing notes on a different topic. :)

A Scenario

Shipping data arrives daily as an Excel or CSV file. It must be validated and prepped for downstream use. If the delivery is good, we want to send a report to job monitoring tool in XML saying all is well. If all is not well, we'll create an errors report for the BizOps team and send it to their inbox. The accepted data lines will go in a Parquet file called shipping.parquet.

That's a lot, but we can do this quickly at a high level. We'll use FlightPath Data to minimize the effort required. If you want more detail how anything works, hit the comments and ask.

To simplify things more, let's assume FlightPath Server is doing the runs based on a webhook from the MFT server, an easy assumption. FlightPath Data and FlightPath Server are free and open, so why wouldn't we go there?

Here's the plot: an MFT (SFTPGo? S3? Whatever!) receives a file. It calls FlightPath Server (in dev) at http://192.168.1.73:8000/runs with a JSON payload saying where the new file is.

We need:

• Validation csvpath(s)
• An XML formating csvpath
• An error reporting csvpath

And we need to load these csvpaths into a named-paths group that we can apply to data files as they are registered. The named-paths group will validate the CSV files and at the same time write two reports and publish them to a certain location.

Easy!

To start, I create a new project in FlightPath Data by clicking the drop-down at the top-left and selecting Create new project. I'll call it XML. We'll use the examples FlightPath creates in all new projects. Specifically, our data file will be examples/schemas/shipping.csv and our validations and report writing csvpaths will be in a new directory: examples/xml.

The Validation

For our validation script, I'll right-click on shipping.csv and select Generate csvpath. This opens the AI schema generation tab. I have Gemini Flash configured at the moment. Gemini is good at CsvPath, especially in FlightPath. I'll let you read the docs for how to configure your favorite AI, it's simple stuff.

I asked for "a basic schema" and I got:

   ~ Validate headers for correct names and order ~
   $[1][ header_names_match("order_id|addressee|unit|street| city|state|zip|payee|unit|street|city|state|zip") ]

   ---- CSVPATH ----

   ~ Basic schema for data lines ~
   $[1*][ 
     integer.notnone(#0) 
     string.notnone(#1) 
     string.notnone(#3) 
     string.notnone(#4) 
     string.notnone(#5) 
     string.notnone(#6) 
     string.notnone(#7)
   ]

   ---- CSVPATH ----

   ~ Verify order_id uniqueness ~
   $[*][ not(has_dups(#0)) ]

Could that be simplified a bit? Yes. FlightPath's AI is often concise, but not always. Still, it's a fine place to start.

The XML

For the XML I created a csvpath statement that doesn't do much beyond illustrating the concept. This is the heart of the scenario. There are a few things we're going to cover.

~
   id: hello xml world
   test-data: examples/schemas/shipping.csv
   transfer-mode: xml.txt > myoutput 
   print-mode:separate
~
$[*][
    @myoutput = "test/my.xml" 

    firstline.nocontrib() -> print("<test>", "xml")
    firstline.nocontrib() -> skip()

    print("  <orderId>$.headers.order_id</orderId>", "xml") 

    last.nocontrib() -> print("  <valid>$.csvpath.valid</valid>", "xml")
    last.nocontrib() -> print("</test>", "xml")
]

This little gem of an XML-generating critter is going to spit out:

It doesn't show off CsvPath Validation Language's awesome transformation capabilities, but it does show a bit of the power of the print() function, in combination with modes.

Print Mode

Our requirement is to print two reports. One is an XML describing the state of the data. (In lieu of an actual report we're just spitting out a few XML entities. The actual reporting of validity or not is an exercise for the reader). The other is an error report.

Print mode is one of the bunch of modes you can set using a metadata directive in a leading comment at the top of a csvpath statement. It is always in the form print-mode: followed by one of default, no-default, separate, no-default-separate. "default" refers to if anything is printed to standard out, or not. "separate" means send different printstreams to different files.

A printstream is just the sum of print() function calls with a name as the second argument. In our example, we use print("<test>", "xml") to print the opening of our top XML element to the "xml" printstream. Since we have print-mode:separate we see that print statement show up in an XML file called my.xml.

Now we need a third csvpath statement to write the error report. We can send the BizOps team the standard errors.json that CsvPath Framework generates, but let's say they want plain English.

To do that we add print() statements to a "report" printstream. Then we add transfer-mode: directives, with a + for append, to send the report printouts to a report.txt file. Here's what the summary section of the report looks like:

Notice these items:

1. The print-mode: and transfer-mode: directives at the top in the leading comment
2. A + at the end of the variable that provides the path the report will be transferred to. That signals the transferred data will be appended.
3. We use last.nocontrib() to indicate the last line, with no contribution to line matching; however, since we don't care about matching for this example, we could have dispensed with the nocontrib qualifier. I left it there because it is a good instinct.
4. The printed lines are left-justified. Since we're using markdown formatting we should be sure to start at the left margin.

Finally, to put it all together I need to deploy this set of csvpath statement files as a named-paths group. The way to do that with the most control and flexibility + the least clicking (or, alternatively, least lines of Python) is to use a definition.json file.

A definition.json file that you use to setup a named-paths group can be simple. And the csvpath references they contain are ordered, which is important in csvpath statements. I used:

{
  "xml": [
    "examples/xml/schema.csvpath",
    "examples/xml/xml.csvpath",
    "examples/xml/report.csvpath"
  ]
}

In FlightPath I just right-click my xml.json file, select Load csvpaths, and click the 2-button form dialog to submit.

When I right-click my new named-paths group and select New run my reports are generated. Since I put them in the project root > transfers > test directory, that's where I look, and there they are.

We saw the XML output earlier. The text report looks like this. (Again, very simple, just giving you the idea).

Your full results are back in the Archive. The Archive is your immutable versioned source-of-truth data repository. All the run files are present in the date-stamped run directory, including the cleaned and valid CSVs. Everything you need for programmatic workflows and forensics. You can transfer these files, call webhooks, run scripts, etc. And, if you are using FlightPath Server, they are available from a REST API.

That's All, Folks

We've done a few things here.

1. We validated a file
2. Created two reports, text and XML
3. Transferred the reports to where our BizOps team knows to look
4. Created a set of structured, machine readable results available to downstream data consumers
5. Created an easily automateable process that, in this case, we only need an MFT server webhook to trigger

Not bad for a few minutes' work.