AVIO Insights

Transforming Fixed Width Data in MuleSoft

The ever increasing number of web and mobile applications continue to drive a lot of discussion around XML vs. JSON when exchanging data. For many systems within an enterprise, this conversation has no relevance, since the exchange of data to/from these systems is limited to only flat file formats. Many of these legacy systems require formats such as comma separated values (csv), fixed length, or hierarchical structures. Mule’s DataWeave has had support for csv transformations. With the recent Mule 3.8 release, DataWeave also now supports the ability to transform to/from fixed length and hierarchical formats.

Within this article, we will focus on the fixed width transformation capability within DataWeave.

Fixed Width Definition

The first step for manipulation of any fixed width record is defining the fields along with the field lengths. Within DataWeave, regardless of whether transforming to or from the fixed width format, this definition can be done easily in Studio 6.0. Define the metadata type within a message processor, add a metadata type then select the type of ‘FIXED WIDTH’:

Metadata Type

This choice will now allow you to define the fields for the given fixed width message:

Fixed Width

Once all the fields have been added, Anypoint Studio generates a flat file definition (.ffd) schema. This schema is, by default, stored in the src/main/resources of the project. Defining the fixed width in the external schema, also provides a higher level of reuse capability..

Field Formats

Fields can be defined as a particular type, including the format type string, integer, decimal, Boolean, etc. Alignment of the fields is also a typical requirement by the legacy systems. Below is an example of how, along with the width size, right/left alignment can be specified:

String

The data contained within this fixed width file in many instances, needs to be defined as something other than as a string. Numbers need implicit decimal points, values need to be formatted, and numbers require padding with zeros. All of this, and more, can be accomplished within DataWeave.

Specifying the format type (i.e. string, integer, decimal, Boolean, etc.) of the field provides additional formatting capabilities. For example, a field defined as a decimal type has the capability to be formatted using java.text.decimalFormat, padded with zeros (i.e. 123 -> 00123), or have the decimal point implied.

The two examples below show some of the formatting options with a decimal field:

decimal

Examples of the java.text.decimalFormat pattern are below:

Pattern

Like decimal fields, integer fields can also be aligned left/right, padded with zeros, and formatted using a specified pattern:

Integer

Boolean fields have width and alignment parameters, but can also specify mappings such as True->1, False->0:

Boolean

DataWeave Mapping

Below is an example of a schema (.ffd). Notice that it contains the necessary field formatting:

form: FIXEDWIDTH

name: 'flatFile'

values:

- { name: 'Id', type: Integer, length: 10, format: { justify: ZEROES } }

- { name: 'LastName', type: String, length: 20 }

- { name: 'FirstNme', type: String, length: 20, format: { justify: RIGHT } }

- { name: 'StreetAddress', type: String, length: 25 }

- { name: 'City', type: String, length: 25 }

- { name: 'Zip', type: String, length: 10 }

- { name: 'StartDate', type: Date, length: 10, format: { justify: RIGHT } }

- { name: 'Salary', type: Decimal, length: 10, format: { pattern: '$###,###.00', locale: US } }

Because the formatting is defined within the schema, the mapping to/from the fixed width can be fairly simple. Below, is an example of a DataWeave map that used the schema from above:

DataWeave map

Using the above schema and DataWeave map, the JSON input is transformed to the following fixed width output:

Input:

{
"user": {
"name": {
"firstName": "Jane",
"lastName": "Doe"
},
"primaryEmail": "test@gmail.com",
"primaryPhone": "555-1212",
"isActive": true,
"startDate": "2016-01-01",
"salary": 89452,
"endDate": "",
"address": [
{
"streetAddress": "123 Main St",
"city": "anywhere",
"state": "wa",
"postalCode": "90909",
"type": "home"
}]
}
}

Output:

0000000012Doe                                 Jane123 Main St              anywhere                 90909       20160101$89,452.00

Conclusion

Through the use of flat file definition schemas within DataWeave, transforming your legacy data just got easier with MuleSoft 3.8.

2 minute read