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.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

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:

Output:

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:
 

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!

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

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!​​​​​​​