CData Arc - Mapping CSV with parent-child rows to flat CSV format

[MattM]

Hi all,

I’m trying to create a mapping to translate a CSV format with parent-child rows into a flat CSV format.

My source file is structured like this:

Row Type	Order number	Customer name	Total Cost
Header	12345	John Smith	$15.00
Row Type	Item code	Item name	Cost
Line	40123	5mm cable	$5.00
Line	40124	10mm cable	$10.00
Row Type	Order number	Customer name	Total Cost
Header	12346	Jane Lane	$10.00
Row Type	Item code	Item name	Cost
Line	40124	10mm cable	$10.00

Note that the actual file does not have headings, so looks like this:

Header	12345	John Smith	$15.00
Line	40123	5mm cable	$5.00
Line	40124	10mm cable	$10.00
Header	12346	Jane Lane	$10.00
Line	40124	10mm cable	$10.00

I am wanting to convert this to a flat CSV structure that combines data from the parent header and child line rows. So the above would be translated to:

Order number	Customer name	Item code	Item name	Cost
12345	John Smith	40123	5mm cable	$5.00
12345	John Smith	40124	10mm cable	$10.00
12346	Jane Lane	40124	10mm cable	$10.00

I would greatly appreciate any suggestions on how to do this, as I’m at a loss using the default CSV connectors and the documentation I’ve read suggests that it can’t be done that way.

Thanks,

Matthew

[Ankit_Singh]

Hi @MattM

Thanks for reaching out to us. This is a classic example of a multi-line flat file transformation. Although this transformation is not possible with a CSV connector, we can achieve this with a Flat File connector.

Assuming the data you have can be represented in a CSV format like this:

Header,12345,John Smith,$15.00Line,40123,5mm cable,$5.00Line,40124,10mm cable,$10.00Header,12346,Jane Lane,$10.00Line,40124,10mm cable,$10.00

Here is what you can do to transform in the format you wish:

Step 1: Create a FlatFile connector in your workspace. You can find it in Core Connectors.

Step 2: Select File Type as ‘Character Delimited’ and Delimiter as ‘,’.

Step 3: Now we set Control Field. This section governs how the connector should read the incoming file. Select ‘Enable multi-line mode’ as your file different types of lines governed by control field - Header, Line at Index ‘0’.

Step 4: Next up, set Line Types, one for Header and one for Line control field. Add respective fields for each Control field.

You can leave rest of the settings as it is.

Step 5: Upload your sample file to Input tab and let the connector do its magic.

Click on the filename to preview the file. Here is how it should look like:

Input:

CSV Input to FlatFile Connector

Output:

XML Output of FlatFile Connector

Step 6: Now that we have an XML output of this, we use an XMLMap, in tandem to transform it into a flattened structure. Create an XMLMap connector in your workspace and connect it to Flat File connector:

Step 7: We reverse engineer now. Look at the output we want and create an XML template for destination accordingly. Here is what it’d look like, based on what you described:

<Items>	<LineNumber>		<OrderNumber>12345</OrderNumber>		<CustomerName>John Smith</CustomerName>		<TotalCost>$15.00</TotalCost>		<ItemCode>40123</ItemCode>		<ItemName>5mm cable</ItemName>		<ItemCost>$5.00</ItemCost>	</LineNumber></Items>

Step 8: In the XMLMap connector, the Souce file mapping template should be picked up from the FlatFile1 connector. Upload the Destination File template from Step 7.

Step 9: Do the mapping as necessary. However, we ought to be smart about it. There are hierarchical elements in Source and a flat structure in destination.

To ensure, each line item in source creates a new record in destination, create a foreach relationship on Item level:

Step 10: Requeue the input file from FlatFile connector and check the output at XMLMap connector. You should now have a flattened XML that looks something like this:

Output XML of XMLMap

Step 11: Add a CSV connector to convert these into a flat CSV. Make sure that ‘Column Headers present’ checkbox is checked to make sure output CSV has first line as columns.

Step 12: Run the entire flow and voila! You have a flat CSV file out of multi-line flat file created.
​​

Let me know if this helps or if you have any questions!

[MattM]

Hi Ankit,

Thanks for the detailed response!  I used the Flat File connector as you suggested, which allowed me to perform the multi-level mapping, however I have the same issue with the converted CSV as in your example output file.

Notice the OrderNumber and CustomerNumber in the third line item are populated with values from the first Header Line (“12345” and “John Smith”) where we want them to use the values from the second Header Line (“12346” and “Jane Lane”).

So I tried checking the “Nest new line types” option in the Flat File connector:

…but then encountered the error below when processing a new file:

Is there another configuration setting we’re missing?

Thanks,

Matthew

[Ankit_Singh]

Hey @MattM

I see the challenge and the oversight at my part.

What is happening here is, in XMLMap, we have done Foreach mapping at LineItem level, hence it picks up the first header values and maps it. This is primarily happening because Header and Line are sibling elements in the source XML in XMLMap. We can resolve this by making a parent-child relationship between Header and Line in XML.

If you check this setting in Flat File connector - Nest new line items, and then process the flat file, the output should look something like this:

<Items>	<Header>		<OrderNumber>12345</OrderNumber>		<CustomerName>John Smith</CustomerName>		<TotalCost>$15.00</TotalCost>		<Line>			<ItemCode>40123</ItemCode>			<ItemName>5mm cable</ItemName>			<ItemCost>$5.00</ItemCost>		</Line>		<Line>			<ItemCode>40124</ItemCode>			<ItemName>10mm cable</ItemName>			<ItemCost>$10.00</ItemCost>		</Line>	</Header>	<Header>		<OrderNumber>12346</OrderNumber>		<CustomerName>Jane Lane</CustomerName>		<TotalCost>$10.00</TotalCost>		<Line>			<ItemCode>40124</ItemCode>			<ItemName>10mm cable</ItemName>			<ItemCost>$10.00</ItemCost>		</Line>	</Header></Items>

Notice, how the Line elements are nested in the Header element.

Next up, you will have to modify your Source file in XMLMap to this:

<Items>	<Header>		<OrderNumber>12345</OrderNumber>		<CustomerName>John Smith</CustomerName>		<TotalCost>$15.00</TotalCost>		<Line>			<ItemCode>40123</ItemCode>			<ItemName>5mm cable</ItemName>			<ItemCost>$5.00</ItemCost>		</Line>	</Header></Items>

So your application UI looks something like this:

Notice how the elements are nested.

Now, follow the same mapping and as we did in Step 9 and it should give you the right CSV output:

Let me know if this helps!​​​​​​​