CSV | XML | JSON in MuleSoft

CSV stands for Comma-Separated Values and it is a popular data format in systems of information nowadays. This kind of structured data is presented in a tabular format with columns and rows in which every single line of the file follows a predefined format. All the fields in a row, defined in the file, are plain text and separated by commas. The main challenge of using CSV content is having values in a row that may contain comma characters which will make the file as if the line was not following the preset format. This kind of errors can be solved in two ways:

• Escaping the comma character

• Enclosing values in single or double quotes

One more challenge with this format is that the information in it is not normalized, which makes data repetition across the file very common and results in bigger file sizes.

Working With CSV Format in MuleSoft

Sample Files

Create sample files of the expected input and output messages. Remember to save these files to disk so MuleSoft can reference them to create scaffoldings of input and output messages in the Transform Message component.

In this case, create a file named person.csv. We will be using the below CSV format as an example.

id,name,age,gender
1,luis,21,m
2,john,23,m
3,laura,21,f
4,beth,25,f
5,matt,32,m

Likewise, create the file person.json. We will use this file as the example for our JSON message:

{
  "person": [
    {
      "id": "1",
      "name": "luis",
      "age": "21",
      "genre": "m"
    }
  ]
}

Finally, create the file person.xml with the below content:

<?xml version="1.0"?>
<people>
 <person>
 <id>1</id>
 <name>luis</name>
 <age>21</age>
 <gender>m</gender>
 </person>
 <person>
 <id>2</id>
 <name>john</name>
 <age>23</age>
 <gender>m</gender>
 </person>
 <person>
 <id>3</id>
 <name>laura</name>
 <age>21</age>
 <gender>f</gender>
 </person>
 <person>
 <id>4</id>
 <name>beth</name>
 <age>25</age>
 <gender>f</gender>
 </person>
 <person>
 <id>5</id>
 <name>matt</name>
 <age>32</age>
 <gender>m</gender>
 </person>
</people>

CSV to JSON

We will start by creating a new MuleSoft project in Anypoint Studio. We will name it "test-csv."

Drag and drop a “Transform Message” component. We will be using this component to write our DataWeave code that will transform from CSV to JSON. Rename the flow "CSVtoJSON."

Click on the “Transform Message” component. By doing this, the bottom pane divides into four sections:

a. Input
c. Transformation
b. Output
d. DataWeave code

Click on the “Define metadata” link in the Input section of the Transform Message component. This will open a “Select Metadata" window which will help us to define example files to automatically generate scaffoldings of the input and output files.

Click the Add button on the emerging window and type “personaCSV,” and then click Create Type. This creates a global object definition in the project that can be reused to set metadata for other transformations

In the type dropdown list, select the option “CSV”. Click the button “Load from example” in order to load the example file we defined in the previous section. By clicking OK, we can now see that the fields in the example file were added to the personaCSV object definition.

In the output section of the “Transform Message” component. click on “Define metadata.” Add another object definition called "personaJSON" with the example JSON file defined in the previous section.

Once the source and target definitions have been created, using the drag and drop functionality, map all the fields from source CSV format to target JSON format. We can see how the DataWeave code is generated as we map objects from source to target

DataWeave code:

% dw 1.0
 % output application / json
 -- - {
  person: payload map((payload01, indexOfPayload01) -> {
   id: payload01.id,
   name: payload01.name,
   age: payload01.age,
   genre: payload01.gender
  })
 }

Optionally, add File Connectors to test this functionality and run the project to observe how source CSV files can be transformed into target JSON files

CSV to JSON

% dw 1.0
 % output application / json
 -- - {
  person: payload map((payload01, indexOfPayload01) -> {
   id: payload01.id,
   name: payload01.name,
   age: payload01.age,
   genre: payload01.gender
  })
 }

CSV to XML

The steps above can be repeated to transform CSV to XML format. Note that the DataWeave code is different this time, since XML requires a root element (people, in this example).

% dw 1.0
 % output application / xml
 -- - {
  people: {
   (payload map((payload01, indexOfPayload01) -> {
    person: {
     id: payload01.id as: number,
     name: payload01.name,
     age: payload01.age as: number,
     gender: payload01.gender
    }
   }))
  }
 }

JSON to CSV

The steps above can be repeated to transform JSON to CSV format. Note that the DataWeave code is different this time, since CSV does not require a root element.

DataWeave code:

% dw 1.0
 % output application / csv
 -- -
 payload.person map((person, indexOfPerson) -> {
  id: person.id,
  name: person.name,
  age: person.age,
  gender: person.genre
 })

XML to CSV

The steps above can be repeated to transform XML to CSV format. Note that the DataWeave code is different this time, since XML has a root element (people) and CSV does not require a root element.

DataWeave code:

% dw 1.0
 % output application / csv
 -- -
 payload.people.*person map((person, indexOfPerson) -> {
  id: person.id as: string,
  name: person.name,
  age: person.age as: string,
  gender: person.gender
 })