Mulesoft transform multiple JSON/XML to CSV

Question

I'm totally new on transforming JSON to CSV in Mulesoft. Currently i need to work on to transform a nested JSON into CSV which my CSV should look like this and my JSON will be like

{
  "Invoice": [
    {
      "Invoice Number*": "",
      "Supplier Name": "",
      "Supplier Number": "",
      "Status": "",
      "Invoice Date*": ""
    }
  ],
  "Invoice Line": [
    {
      "Invoice Number*": "",
      "Supplier Name": "",
      "Supplier Number": "",
      "Line Number": "",
      "Description*": "",
      "Supplier Part Number": "",
      "Auxiliary Part Number": "",
      "Price*": "",
      "Quantity": "",
      "Bulk Price": "",
      "Bulk Price Qty": ""
    }
  ],
  "Invoice Tax Line": [
    {
      "Invoice Number*": "",
      "Invoice Line Number": "",
      "Invoice Charge Number": "",
      "Line Number": "",
      "Tax Amount": "",
      "Tax Rate": "",
      "Tax Code": "",
      "Tax Rate Type": ""
    }
  ]
}

What i know about CSV is there will be only one header. What is the best way for me to map this complex JSON into CSV which will shows different header?

help.mulesoft.com/sfc/servlet.shepherd/version/…

Shawn.Y
– Shawn.Y

2019-08-30 02:49:42 +00:00
Commented Aug 30, 2019 at 2:49 — Shawn.Y
– Shawn.Y, Commented Aug 30, 2019 at 2:49
Image for CSV view

Shawn.Y
– Shawn.Y

2019-08-30 02:49:52 +00:00
Commented Aug 30, 2019 at 2:49 — Shawn.Y
– Shawn.Y, Commented Aug 30, 2019 at 2:49

aled · Accepted Answer · 2019-08-30 11:29:06Z

4

You can do something like this:

%dw 2.0
output application/csv header=false
---
flatten (
    [
        {"Invoice Number":"Invoice Number",
            "Supplier Name": "Supplier Name",
            "Supplier Number" :"Supplier Number",
            "Status" : "Status",
            "Invoice Date*" : "Invoice Date*"
        },
        {
            "Invoice Number":"Invoice Number*",
            "Supplier Name":"Supplier Name",
            "Supplier Number":"Supplier Number",
            "Status":"Status",
            "Invoice Date*":"Invoice Date*"
        },
        payload.Invoice map {
            "Invoice Number":$."Invoice Number*",
            "Supplier Name":$."Supplier Name",
            "Supplier Number":$."Supplier Number",
            "Status":$.Status,
            "Invoice Date*":$."Invoice Date*"
        }, 
        payload."Invoice Line" map {
            "Invoice Number": $."Invoice Number*",
            "Supplier Name":$."Supplier Name",
            ...
        } 
        , 
        payload."Invoice Tax Line" map {
            "Invoice Number":$."Invoice Number*",
            ...
        } 
    ]
)

answered Aug 30, 2019 at 11:29

aled

26.5k4 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shawn.Y Over a year ago

Hi @aled, thanks for answering! Can you slightly explain what happening on this code?

aled Over a year ago

The CSV support in DataWeave is meant to have 1 optional header line and the rest of the rows with the same structure. I'm abusing it a bit by creating each line by hand. For DataWeave there is no header. Because the items are an array the map for it can generate an array of objects, so we need to use the flatten() function to output a single array, which each object representing a line of the output.

Collectives™ on Stack Overflow

Mulesoft transform multiple JSON/XML to CSV

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related