This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(9.6, 99%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETG%3C/text%3E%3C/svg%3E)
I would like to convert complex JSON into csv or fix length format.
There are multiple invoices in json file . One invoice may contain one or more lineitems.
Below is the json format and desired output. Each invoice number will be in header and all lineitems will be in DLT section. After that i will have footer as well. HDF, DLT, FTR will repeat for each invoice give in json .
{
"invoices": [
{
"INVOICE_NO": "B001R1",
"Invoice": [
{
"InvoiceNum": "B001R1",
"LesseeCode": "0G98"
}
],
"Lineitems": [
{
"InvoiceNum": "B001R1",
"LesseeCodeDetail": "0G98",
"VehicleNo": "0001B"
}
]
},
{
"INVOICE_NO": "B2r1S1",
"Invoice": [
{
"InvoiceNum": "B2r1S1",
"LesseeCode": "0G98"
}
],
"Lineitems": [
{
"InvoiceNum": "B2r1S1",
"LesseeCodeDetail": "0G98",
"VehicleNo": "12901S"
}
]
},
{
"INVOICE_NO": "B2R8S2",
"Invoice": [
{
"InvoiceNum": "B2R8S2",
"LesseeCode": "0G98"
}
],
"Lineitems": [
{
"InvoiceNum": "B2R8S2",
"LesseeCodeDetail": "0G98",
"VehicleNo": "15012S"
}
]
},
{
"INVOICE_NO": "B243861",
"Invoice": [
{
"InvoiceNum": "B243861",
"LesseeCode": "0G98"
}
],
"Lineitems": [
{
"InvoiceNum": "B243861",
"LesseeCodeDetail": "0G98",
"VehicleNo": "1S1006"
},
{
"InvoiceNum": "B243861",
"LesseeCodeDetail": "0G98",
"VehicleNo": "1S1006"
}
]
}
]
}
and here is the
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(195.2, 50%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EVR%3C/text%3E%3C/svg%3E)
Hi Tanya Garg To convert your complex JSON into a CSV or fixed-length format using Azure Data Factory (ADF) in a Parent (HDR) → Child (DLT) → Footer (FTR) layout, you can build a Mapping Data Flow using the following approach:
You want to convert a set of invoices from JSON into flat output blocks where:
HDR line.DLT line.FTR line.Example output:
HDR INV B001R1 0G98 DR ZZZZ
DLT 0001 B001R1 0G98 DR ZZZZ 0000273.01 1S1007
DLT 0002 B001R1 0G98 DR ZZZZ 0000194.47 1S1007
FTR 0002 B001R1 0G98 DR 4A 0000677.54 MI4818000
ADF Implementation Steps
Source JSON File
Array of Documents enabled.invoices array.Flatten Invoices
Invoice and Lineitems arrays.Invoice and Lineitems by InvoiceNum if needed.Generate HDR, DLT, FTR Rows: Use a Derived Column to create the row types:
'HDR INV ' + InvoiceNum + ' ' + LesseeCode + ' DR ZZZZ'
'DLT ' + RowNumber + ' ' + InvoiceNum + ' ' + LesseeCodeDetail + ' DR ZZZZ 0000273.01 1S1007'
'FTR ' + DLT_Count + ' ' + InvoiceNum + ' ' + LesseeCode + ' DR 4A 0000677.54 MI4818000'
Union All Rows: Use a Union transformation to stack HDR, DLTs, and FTR rows per invoice.
Sink: Write the output to a DelimitedText (CSV) or fixed-width sink format in Blob or ADLS.
Notes
Use Surrogate Key or window functions to generate DLT row numbers.
Format string columns for fixed-length fields using padding/trimming.
Add Sort if you need the rows in HDR → DLT → FTR order.
I hope this information helps. Please do let us know if you have any further queries.
Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Can you please reply to my comment
It did not work as expected. At present , i only used HDR and DLT to make it simple.
attached is the screen shot of data flow.
and here is the output. Its actually creating it as 2 different columns . and its repeating same header multiple times. Where as Header should appear once for each invoice number and then detailed Line item. Please advise.
Tanya Garg Based on your latest screenshots and feedback, it looks like the HDR rows are repeating for each line item, which is causing your data flow to output the wrong format. Here's a refined approach to help you solve the problem:
Separate HDR and DLT Logic: Ensure HDR rows are created separately from DLT (line items). HDR should be based only on invoice details and not joined with line items.
Flattening and Derived Columns:
Union Transformation: Use a Union to merge HDR and DLT rows after they are created independently.
Sink Configuration: Write output to one column (e.g., finalRow), ensuring all data is in a single field in the CSV/fixed-width output.
By separating HDR and DLT creation and using the Derived Column transformations, you should get the correct output without HDR repetition.
Hope this helps. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Tanya Garg Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.