3 Simple Steps to Convert a FlatFile into Json / CSV / XML

Hello Muleys,

Well this article is all about how simple it is to convert a Flatfile into Json or CSV or an XML using all time powerful weapon DataWeave 2.0.

Please do not forget to read Things to Remember at the end of article so that you wont commit any mistake while coding.

• Anypoint Studio 7.4 verison
• Mule 4.2.2 Version

Before Stepping in , lets be familiar with few terms .

FlatFile : A flat file is data in a plain text format. Usually we get this kind of Data from either Mainframe systems or SFTP or Files etc.

CopyBook: A copybook is a selection of code that defines data structures. If a particular data structure is used in many programs, then instead of writing the same data structure again, we can use copybooks. In general terms, to define the fields Data Types/Length and their structure, we define them in a copy book which in turn helps us in generating ffd file using Transform Message.

Let's follow Step-By-Step:

Step 1: Creating Copy Book

First of all have the flatfile Data in Handy . as previously said, its a plain text string and the rows are separated by new line.

Eg:

1234SRAVAN    27-09-1992TELANGANA HYDERABAD 11111
5678LINGAM    07-02-1996USA       CHICAGO   77777

The above flatfile data contains 98 characters with spaces (will discuss why spaces are considered later below). each row has 49 characters defined and they are divided by new line

In my example the data i am getting from SFTP or Mainframe basically contains information of :

-'BIRTHID' 'BIRTHNAME' 'BIRTHDATE', 'BIRTHSTATE','BIRTHCITY', 'BIRTHZIP',

Remember The flat file always has FIXED length !

If you have Birthname defined for length of 10 characters . And if at all your name has only 6 characters, then remaining 4 characters MUST be of empty string or 4 spaces

Below is the Copy Book that i have created to define my incoming fields:

000000 01 BIRTH-DETAILS.
000100 05 BIRTHID PIC X(4).
000200 05 BIRTHNAME PIC X(10).
000300 05 BIRTHDATE PIC X(10).
000400 05 BIRTHSTATE PIC X(10).
000500 05 BIRTHCITY PIC X(10).
000600 05 BIRTHZIP PIC X(5).

Please follow the indentation in Copy Book. type is nothing but your datatype of that particular field, length is the fixed length allocated to that field .

Save this file with extension .cpy Eg: BirthDetails.cpy

Step 2: Auto generation of ffd file using DataWeave.

This is pretty simple step with No Coding! we need basically an ffd file to convert the flatfile to Json / csv. For that you have to do below step

• Drag and Drop the transform message in an empty flow
• Go to Input Face and click on Define Meta Data.
• Click on Add and give some Type Id.
• Drop down Type and select Copy Book
• Click Import and select your Copy book that you have created in Step 1 and finally click select

After clicking on Select , an ffd file will be created automatically and placed under : src/main/resources --> schemas--> BirthDetails.ffd

Please copy the ffd from schemas folder and paste it in root of src/main/resources as shown in below screenshot:

FFD file generated:

form: COPYBOOK
id: 'BIRTH-DETAILS'
values: 
- { name: 'BIRTHID', type: String, length: 4 }
- { name: 'BIRTHNAME', type: String, length: 10 }
- { name: 'BIRTHDATE', type: String, length: 10 }
- { name: 'BIRTHSTATE', type: String, length: 10 }
- { name: 'BIRTHCITY', type: String, length: 10 }
- { name: 'BIRTHZIP', type: String, length: 5 }

Step 3: Final Transformation

Its pretty simple transformation. Only thing to remember is dont forget to give the schemaPath in set Payload.

No matter what is your source if Data, whether it be a File Read connector or Whether its Http Listener where you send plain text data or any other form, The fisrst thing that yo need to place after the source is Set Payload

Drag and Drop a Set Payload and configure as below. It needs value as #[payload]

Now goto MimeType Tab and configure recordTerminator and schemaPath as below:

Now Place a Transform Message just after Set Payload and code it as below. :

Its just a basic Mapping

If you want your output in csv format, just replace application/json with application/csv

If you want output in xml format , we need another transform message after application/json output because this doesn't have a root tag. If you try to map directly to application/xml , it will throw an error!

That's it finished :)

Things to Remember :

From below image you can see that , the name is defined as 10 characters in your ffd . But "sravan' has only 6 characters. So input name must have 10 characters. i.e, "sravan "

We have to have 4 spaces appended and that too after continuation of name. Because

for each row , we defined ffd as

1st 4 characters belong to Birth ID

Next 10 characters belong to Birth Name

Next 10 characters belong to Birth Name

Next 10 characters belong to Date Of Birth and like wise ..

Each special character is treated as 1 character. And the rows must be divided using new line

break (highlighted in yellow). If you can see input , State name for 2nd row is US(3 characters). So we have appended 7 spaces to it to make it perfect and valid flatfile.

So one of the validations mentioned above failed in input, we will get Transformation errors!

You wanna try? Try out now!

Pass the flatfile data through postman using Post Method and select type as Text and send it as input for your Application

Your final Mule app is pretty Simple:

Get back to me if you have any doubts!

Happy Learning !

Yours Sravan Lingam