# CData Arc - Mapping CSV with parent-child rows to flat CSV format **URL:** \ **Category:** CData Arc\ **Tags:** question, cdata-arc, csv-connector\ **Created:** [November 24, 2023, 5:36am UTC](https://community.cdata.com/t/cdata-arc-mapping-csv-with-parent-child-rows-to-flat-csv-format/535 "2023-11-24T05:36:14Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![MattM](https://avatars.discourse-cdn.com/v4/letter/m/c89c15/32.png) [@MattM](https://community.cdata.com/u/MattM)\ **Post date:** [November 24, 2023, 5:36am UTC](https://community.cdata.com/t/cdata-arc-mapping-csv-with-parent-child-rows-to-flat-csv-format/535/1 "2023-11-24T05:36:15Z")
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 ---
**Author:** ![Ankit\_Singh](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/ankit_singh/32/1614_2.png) [@Ankit\_Singh](https://community.cdata.com/u/Ankit_Singh)\ **Post date:** [November 27, 2023, 6:50am UTC](https://community.cdata.com/t/cdata-arc-mapping-csv-with-parent-child-rows-to-flat-csv-format/535/2 "2023-11-27T06:50:40Z")
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: > ```auto > 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 **‘,’**. ![8f659a6b-0c66-4de2-8c9a-731afe855c0d.png](https://us1.discourse-cdn.com/cdata/original/1X/d82ace9e848dc14dbd1c6856916c8614294c58c1.png) **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’_. ![d4e3235c-1a93-4b57-95a1-78ee0f6b8f61.png](https://us1.discourse-cdn.com/cdata/original/2X/3/353b97322fcc7751d253485c73c192c4eb7190ba.png) **Step 4:**  Next up, set **Line Types** , one for **Header** and one for Line control field. Add respective fields for each Control field. ![2389c692-a34e-4cff-aec6-e2c34e5f6c1d.png](https://us1.discourse-cdn.com/cdata/original/2X/1/124c20d5740227867276360799d0707eb56578cc.png) ![5a26af6e-586b-410a-be59-467cf86e0afb.png](https://us1.discourse-cdn.com/cdata/original/1X/253d6f13a013dea5bb52a661ba5be8ee117a01d6.png) 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. 😎 ![fbd5ab9c-03ea-4a67-94d0-5daa81e9c9fe.png](https://us1.discourse-cdn.com/cdata/original/1X/f149431da07d5c0780a2a055f286a7e9c6501fa2.png) Click on the filename to preview the file. Here is how it should look like: **Input:** ![ce5c1a47-c3ae-428d-acb0-69b526c4c5b0.png](https://us1.discourse-cdn.com/cdata/original/2X/0/0c083207b1f7494610543ad041b5c9dcabe17fba.png) CSV Input to FlatFile Connector **Output:** ![aad246f4-cdbe-4f50-8bc1-4b0282202823.png](https://us1.discourse-cdn.com/cdata/original/1X/7239e91e0bec59fdf8746ae16bd3fa59d9f430c1.png) 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:** ![a6ca87c5-b143-4356-bafd-9a90d81aa35d.png](https://us1.discourse-cdn.com/cdata/original/1X/fb9e54c423f9c2fde553232a0c99592e702c5579.png) **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: > ```xml > 12345 John Smith $15.00 40123 5mm cable $5.00 > > ``` **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. ![a1beb152-e321-44bf-a0d8-115b21fa9d06.png](https://us1.discourse-cdn.com/cdata/original/2X/c/c682f7f24d1479337dd7c3a26e9fe135d4841c22.png) **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: ![eb5902fd-1da8-42e5-9c7a-4f8bad967584.gif](https://us1.discourse-cdn.com/cdata/original/1X/777c9439b7b027e1e9509525881cabe8409e1023.gif) **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: ![be2feeaf-aa68-4c2b-9552-f605f596bff2.png](https://us1.discourse-cdn.com/cdata/original/1X/1becd1dacfb7e9633b7b7f070537820753e2b574.png) 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. ![4aad92db-6744-43fe-897b-7f2dac408d9e.png](https://us1.discourse-cdn.com/cdata/original/2X/c/cd678010d9aceaff787f4d0fc69d3d6e2acc4349.png) **Step 12** : Run the entire flow and voila! You have a flat CSV file out of multi-line flat file created. ​​ ![4ff81217-236f-4b94-9854-782da157c752.png](https://us1.discourse-cdn.com/cdata/original/1X/c3a4c7d609c2b0739eb96d103ca4eba7ae7daa32.png) Let me know if this helps or if you have any questions! ---
**Author:** ![MattM](https://avatars.discourse-cdn.com/v4/letter/m/c89c15/32.png) [@MattM](https://community.cdata.com/u/MattM)\ **Post date:** [November 28, 2023, 11:37pm UTC](https://community.cdata.com/t/cdata-arc-mapping-csv-with-parent-child-rows-to-flat-csv-format/535/3 "2023-11-28T23:37:54Z")
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”). ![7d9db128-cfd3-43b2-a963-2ab1ac621055.png](https://us1.discourse-cdn.com/cdata/original/2X/f/f90bebe85a1aa1341b7af6548e5230b5d08c97b8.png) So I tried checking the “Nest new line types” option in the Flat File connector: ![49cc12ad-d0d4-4097-baa3-e85372be2fb7.png](https://us1.discourse-cdn.com/cdata/original/1X/4445d6922539733792e8224650bee4b171b45b34.png) …but then encountered the error below when processing a new file: ![1ef9bb96-0752-431f-9022-b59550cf3b69.png](https://us1.discourse-cdn.com/cdata/original/1X/c99a07106236dd31364d42bece84b6458ae99caa.png) Is there another configuration setting we’re missing? Thanks, Matthew ---
**Author:** ![Ankit\_Singh](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/ankit_singh/32/1614_2.png) [@Ankit\_Singh](https://community.cdata.com/u/Ankit_Singh)\ **Post date:** [November 30, 2023, 9:36am UTC](https://community.cdata.com/t/cdata-arc-mapping-csv-with-parent-child-rows-to-flat-csv-format/535/4 "2023-11-30T09:36:13Z")
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: ```xml
12345 John Smith $15.00 40123 5mm cable $5.00 40124 10mm cable $10.00
12346 Jane Lane $10.00 40124 10mm cable $10.00
``` 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: ```xml
12345 John Smith $15.00 40123 5mm cable $5.00
``` So your application UI looks something like this: ![663545ca-30fc-4e84-ac59-e7bd041dcf6e.png](https://us1.discourse-cdn.com/cdata/original/2X/1/1c29c23e16c85550792e3524f2963602e51fd788.png) 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: ![075b206f-7826-436e-a77f-0e5df011ec3e.png](https://us1.discourse-cdn.com/cdata/original/2X/4/45263d0001e16e48e3023f2573a98b414f4c692a.png) Let me know if this helps!​​​​​​​