June 10 2016

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.

About the Author

Bio

Jennie has over 25 years of information technology experience, the majority of time spent in application integration. She has broad experience in integration architecture design and implementation utilizing several integration toolsets. As an architect, she has experience developing SOA/API Governance Framework and Reference Architectures.

Join the Conversation

Jack C
July 18, 2016

Jennie, thank you for the deep research made. It's really very useful with all the step-by-step guidelines. MuleSoft made it easier, for sure.

Jack Connors - research paper writer and editor in chief

Bala
November 17, 2016

Hi Jeenie,

 

Thank you very much the detailed explanation. My requirement is the otherway round (convert Fixed Width File to JSON format). I have set the Fixed Width Metadata in dataweave and trying to convert to JSON but I am getting an error in the step 1 itself. it throws below error. I have verified in /src/main/resources and "FixedWidthSchema.ffd" file there but still system gives the error.

java.lang.IllegalStateException: Schema FixedWidthSchema.ffd not found on file system or classpath

I have tried setting schemaPath in the dataweave like below but still getting the same error. Any idea what might be wrong?

%dw 1.0
%input payload text/plain schemaPath = "FixedWidthSchema.ffd"
%output application/json

---
payload.RecordType

 

 

Murali
December 28, 2016

Hi Jennie DeRosa,

I tried with given example through dataweave, but it is giving the below error

Unexpected end of line1 (read 1 of expected 10 characters in field) (com.mulesoft.flatfile.lexical.LexicalException).

Can u please help me at this.

 

Thanks,

Murali

 

Enter your first name. It will only be used to display with your comment.
Enter your email. This will be used to validate you as a real user but will NOT be displayed with the comment.
By submitting this form, you accept the Mollom privacy policy.