Solved

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

  • 24 November 2023
  • 3 replies
  • 113 views
M
Userlevel 1
Badge

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

icon

Best answer by Ankit Singh 27 November 2023, 07:50

View original

3 replies

Ankit Singh
Userlevel 5
Badge +1

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:

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!

That Solutions Guy
M
Userlevel 1
Badge

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
Userlevel 5
Badge +1

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

That Solutions Guy

Reply


Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=
Terms and ConditionsCookie settings