Example: Creating Hierarchies from CSV and Fixed-Length Text Files
This example is available at the following path: <Documents>\Altova\MapForce2023\MapForceExamples\Tutorial\Tut-headerDetail.mfd. The example uses a CSV file (Orders.csv) which has the following format:
•Field 1: H defines a header record and D a detail record.
•Field 2: A common key for both header and detail records.
•Each Header or Detail record is on a separate line.
The contents of the Orders.csv file are shown below.
The aim of the mapping is as follows:
•Map the flat file CSV to an hierarchical XML file
•Filter the Header records, designated with an H
•Associate the respective detail records, designated with a D, with each of the header records.
For this to be achieved, the header and detail records must have one common field. In this case the common field, or key, is the second field of the CSV file, i.e. OrderNo. In the CSV file, both the first header record and the following two detail records contain the common value 111.
The Orders.csv file has been inserted twice to make the mapping more intuitive.
The Tut-headerDetail.xsd schema file has a hierarchical structure: Order is the root element, with Header as its child element, and Detail being a child element of Header.
The first Orders.csv file supplies the Header records (and all mapped fields) to the Header item in the schema target file. The filter component is used to filter out all records other than those starting with H. The Rows item supplies these filtered records to the Header item in the schema file.
The second Orders.csv file supplies the Detail records (and all mapped fields) by filtering out the Detail records that match the OrderNo key of the Header record. This is achieved by:
•Comparing the OrderNo field of the Header record with the same field of the Detail records, using the equal function (the priority context is set on the a parameter for enhanced performance).
•Using the Logical-and function to only supply those Detail records containing the same OrderNo field, as the Header record.
The Rows item supplies these filtered records to the Header and Detail items in the schema file, through the on-true parameter of the filter component.
Clicking the Output tab produces the XML file displayed below. Each Header record contains its data, and all associated Detail records that have the same Order No.
Let's now have a look at another example, which uses a slightly different CSV file and is available in the <Documents>\Altova\MapForce2023\MapForceExamples\Tutorial\ folder as Head-detail-inline.mfd. The difference is that:
• No record designator (H, or D) is available
•A common key field, the first field of the CSV file, still exists for both header and detail records (Head-key, Detail-key...). The field is mapped to OrderNo in the schema target
•Header and all respective Detail fields are all on the same line.
The mapping has been designed as follows:
•The key fields are mapped to the respective OrderNo items in the schema target.
•The Detail item in the schema target file has been duplicated, and is displayed as Detail (2). This allows you to map the second set of detail records to the correct item.
•The result of this mapping is basically the same XML file that was produced in the first example.