Mapping CSV data with the Kaoto DataMapper
The Kaoto DataMapper maps between XML and JSON documents. A lot of real integrations start with something else, though, and CSV is probably the most common example. This post shows how to map CSV data with the DataMapper today, using the camel-dfdl data format to bridge the gap.
If you would rather skip the walkthrough and run the result, the finished project is in kaoto-examples.
The idea
The DataMapper needs a schema for each side of the mapping, and it understands XML Schema and JSON Schema. DFDL (Data Format Description Language) is a standard for describing text and binary formats, and a DFDL schema is an XML Schema extended with dfdl: annotations that describe how the data is laid out on the wire. Because it is still an XML Schema, the DataMapper can load it, and because camel-dfdl uses Apache Daffodil to turn the structured text into XML using that same schema, the data and the schema always agree.
So the route looks like this:
CSV → unmarshal (camel-dfdl) → XML → DataMapper → XML / JSON
This is the same approach that can be used for other structured text formats, such as X12 or HL7. See the links at the end of this post.
Why not use an existing CSV DFDL schema?
There is a generic CSV schema in the DFDLSchemas project. It works, but it is deliberately generic. Every line becomes a record with a list of anonymous item elements:
<record>
<item>smith</item>
<item>robert</item>
<item>brandon</item>
<item>1988-03-24</item>
</record>
Even when your CSV has a header line, the column names are lost. This is what you get when you attach this schema to a DataMapper document: the column values are all in a repeatable item field, and the header line in a generic title.
You can still map from it. Since the columns are only distinguished by their position, you refer to them as item[1], item[2] and so on, and you have to keep track of which column each position stands for. If the layout can vary, for example when columns are optional or come in a different order, you can combine those positions with a choose / when / otherwise condition to decide what to map in each case.
That has its place. For a CSV with only a few columns it is manageable, and it can even be an advantage when the column specification is not strictly defined and you need to react to the data at mapping time. But for a large CSV with a well-defined structure it quickly becomes a nightmare: dozens of positional references, none of them self-explanatory, and every one of them breaks silently if a column is added or moved. For a well-structured CSV we want the columns to have names, which is what the rest of this post does.
Step 1: Describe your CSV with a DFDL schema
Here is our sample CSV file, orders.csv:
orderId,customer,product,quantity,unitPrice
A-1001,Acme Corp,Widget,3,9.99
A-1002,Acme Corp,Gadget,1,24.50
A-1003,Acme Corp,Gizmo,10,2.75
Creating the schema means turning the header into elements. Each column becomes a named element, and the whole file becomes a list of records:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:dfdl="http://www.ogf.org/dfdl/dfdl-1.0/">
<xs:annotation>
<xs:appinfo source="http://www.ogf.org/dfdl/">
<dfdl:format representation="text" encoding="UTF-8" lengthKind="delimited" ... />
</xs:appinfo>
</xs:annotation>
<xs:element name="orders">
<xs:complexType>
<xs:sequence dfdl:separator="%NL;" dfdl:separatorPosition="postfix">
<xs:element name="header">
<xs:complexType>
<xs:sequence dfdl:separator=",">
<xs:element name="title" type="xs:string" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="order" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence dfdl:separator=",">
<xs:element name="orderId" type="xs:string"/>
<xs:element name="customer" type="xs:string"/>
<xs:element name="product" type="xs:string"/>
<xs:element name="quantity" type="xs:int"/>
<xs:element name="unitPrice" type="xs:decimal"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
A few things are worth pointing out:
- The outer
xs:sequenceseparates the lines of the file:dfdl:separator="%NL;", with the separator after each line. - The header line is consumed by the
headerelement, so it does not end up as a data row. Its content is generic (title), and the real column names live in theorderelement. - Columns are matched to elements by position, not by name. The header names are not checked against the elements of
order. If a CSV file has its columns in a different order, the values silently end up in the wrong elements, so reorder the elements inorderto match the file. - The inner
xs:sequenceoforderseparates the columns withdfdl:separator=",". Change this if your file uses a different delimiter. - This schema does not handle quoted fields. A value that contains the separator, such as
"Acme, Inc", is split into extra fields and the row fails to parse. Use it only for CSV files whose values contain no separators. - Each column is an
xs:elementwith a name and a type. Declaringquantityasxs:intandunitPriceasxs:decimalmeans the DataMapper knows they are numbers. - The
dfdl:formatannotation on the schema sets the defaults for everything else, such as the encoding and how numbers are represented. DFDL requires many properties to be defined, so the example spells them all out. The complete schema is in the example project.
Step 2: Try it with Camel
Before involving the DataMapper, check that the schema works. This route reads the CSV and unmarshals it with camel-dfdl:
Run it in Kaoto. Click the Run: Workspace button:
The log shows the CSV as XML, with the column names as element names and the values typed according to the schema:
<orders>
<header>
<title>orderId</title><title>customer</title><title>product</title>
<title>quantity</title><title>unitPrice</title>
</header>
<order>
<orderId>A-1001</orderId>
<customer>Acme Corp</customer>
<product>Widget</product>
<quantity>3</quantity>
<unitPrice>9.99</unitPrice>
</order>
...
</orders>
Step 3: Map it with the DataMapper
Now add a DataMapper step after the unmarshal step.
For this example, imagine the CSV is a batch order from a single customer, and the target is one invoice for the whole batch, described by a plain XML Schema, invoice.xsd. It has the customer, one line per CSV row, and a grandTotal after the lines:
<invoice>
<billTo/>
<line>
<orderId/>
<description/>
<quantity/>
<unitPrice/>
<lineTotal/>
</line>
<grandTotal/>
</invoice>
- Open the DataMapper step and attach
orders.dfdl.xsdas the Source Body schema. The DataMapper accepts it like any other.xsd, and ignores thedfdl:annotations. - Attach
invoice.xsdas the Target Body schema. - Create the mappings. Because the source fields are named after the CSV columns, this is a normal visual mapping:
- Use a
for-eachonorderto produce onelineper CSV row. Inside it, maporderId,quantityandunitPricestraight across, and builddescriptionfrom the product and quantity withconcat(product, ' x ', quantity). - The CSV repeats the customer on every row, so map the
customerof the firstordertobillTo. lineTotalandgrandTotalhave no counterpart in the CSV, so compute them in the XPath editor. ForlineTotal, multiplyxs:decimal(quantity)andxs:decimal(unitPrice)within thefor-each. ForgrandTotal, add up the same product over all the orders:sum(/orders/order/(xs:decimal(quantity) * xs:decimal(unitPrice))). The values read from the XML are untyped, and XPath would otherwise multiply them as floating-point numbers, which can show rounding artifacts such as2.3999999999999995instead of2.4. Casting them toxs:decimalkeeps the amounts exact.
- Use a
The route now has the shape setBody (CSV) → Unmarshal (XML) → DataMapper:
The DataMapper generates the XSLT behind the scenes. Each order element in the CSV becomes a line of the invoice, and the grandTotal adds them up.
Running the route again gives one invoice for the whole batch:
<invoice>
<billTo>Acme Corp</billTo>
<line>
<orderId>A-1001</orderId>
<description>Widget x 3</description>
<quantity>3</quantity>
<unitPrice>9.99</unitPrice>
<lineTotal>29.97</lineTotal>
</line>
...
<grandTotal>81.97</grandTotal>
</invoice>
What’s next
Writing the DFDL schema by hand is fine for a CSV with a handful of columns, like the one in this post. Real-world CSV files often have dozens or even hundreds of columns, and typing out one element per column is tedious and easy to get wrong, and it has to be repeated for every CSV layout you want to map. We are planning to automate it, and the work is tracked in kaoto#4475:
- Upload a CSV file in the DataMapper. A header line is enough; you do not need the whole file.
- Generate the DFDL schema from it. Each column in the header becomes a named field, and the rows are represented as an array of that field set, exactly as in the schema above.
- Add the
camel-dfdlstep automatically inside the DataMapper step, so the CSV is converted to XML before the mapping and back again afterwards, without you having to configure it.
These features are planned and are not available yet. Until then, the manual approach in this post works with the DataMapper as it is today. Stay tuned!
Let’s build it together
CSV support in the DataMapper is tracked in kaoto#4475. Any kind of feedback is always more than welcome.
Join the conversation in the GitHub discussions or create an issue.
Give it a try
- The CSV DFDL DataMapper example in kaoto-examples: the finished project from this post, ready to run with Camel JBang
- Kaoto is available as a VS Code extension, where you can open the example and explore the DataMapper mapping
See also
The same camel-dfdl and DataMapper combination works for other structured text formats. These proof-of-concept projects are good places to start:
