Author: Gajanan Karjalkar
What are Flat Files ?
- A flat file, also known as a text database, is a type of database that stores data in a plain text format.
- Flat files present complex hierarchical structural data in a record–based storage format. Unlike XML, flat files do not embed structural data (metadata) within the data. The data in the flat file has been “flattened” by removing the hierarchical relationship between records, leaving the records intact as a single logical record of application data.
- DataWeave uses a YAML format called FFD (for Flat File Definition) to represent
flat file schemas. The FFD format is flexible enough to support a range of use
cases, but is based around the concepts of elements, composites, segments,
- groups, and structures.
- Schemas must be written in Flat File Schema Language, and by convention use a
ffd extension.
What is a Flat File Schema?
- A flat file schema is the blueprint that contains the instructions for parsing or creating a flat file
Components in Flat file Schema
- Element — An element is a basic data item, which has an associated type and
fixed width, along with formatting options for how the data value is read and
written.
- Composite — (Optional) A group of elements. It can also include other child
composites.
- Segment — A line of data, or record, made up of any number of elements
and/or composites that might be repeated.
- Group — (Optional) Several segments grouped together. It can also include
other child groups.
- Structure — A hierarchical organization of segments, which requires that the
segments have unique identifier codes as part of their data.
Types of Flat file segments
- Single Segment
- Multi Segment
Single Segment
If you are only working with one type of record, you only need to have a segment definition for that record type in your FFD.
Example of Single Segment flat file
We will convert a simple CSV file into a flat file with help of flat file schema
Drag a Transform Message Component and define the input metadata , according to your input
Select type as Fixed Width
It will create one file with .ffd extension in the build path
EmployeeFile.ffd file
| form: FIXEDWIDTH id: ‘EmployeeFile’ name: ‘EmployeeFile’ values: – { name: ’empid’, usage: M, type: String, length: 10 } – { name: ’empname’, usage: M, type: String, length: 25 } – { name: ’empdesignation’, usage: M, type: String, length: 25 } – { name: ’empage’, usage: M, type: String, length: 3 } – { name: ’empsalary’, usage: M, type: String, length: 10 } |
In Transform Message you need to use attribute schemaPathalong with ffd file
Multi Segment
If you are working with multiple types of records in the same transformation, you
need to use a structure definition that controls how these different records are
combined.
Example of Multi Segment flat file
We will convert a simple XML file into a flat file with help of flat file schema
Drag a Transform Message Component and define the input metadata , according to your input
and the output metadata
Input Metadata
Employee.ffd file
| form: FLATFILE structures: – id: ‘BatchReq’ name: BatchRequest data: – {idRef: ‘RQH’} – groupId: ‘Batch’ count: ‘>1’ items: – {idRef: ‘BCH’} – {idRef: ‘ESN’, count: ‘>1’} – {idRef: ‘RQF’} segments: – id: ‘RQH’ name: “Request File Header Record” values: – {name: “Record Type”, type: String, length: 3, tagValue: ‘RQH’ } – {name: “File Creation Date”, type: String, length: 8 } – {name: “File Creation Time”, type: String, length: 6 } – id: ‘BCH’ name: “Batch Header Record” values: – {name: “Record Type”, type: String, length: 4, tagValue: ‘BCH’ } – {name: “CompanyName”, type: String, length: 20 } – {name: “Capacity”, type: String, length: 6 } – {name: “Technology”, type: String, length: 25 } – {name: “Location”, type: String, length: 15 } – {name: “HeadQuarter”, type: String, length: 15 } – id: ‘ESN’ name: “Employees Details” values: – {name: “Record Type”, type: String, length: 4, tagValue: ‘ESN’ } – {name: “EmployeeName”, type: String, length: 20 } – {name: “Age”, type: String, length: 3 } – {name: “Designation”, type: String, length: 20 } – {name: “JobLocation”, type: String, length: 15 } – {name: “HomeTown”, type: String, length: 15 } – id: ‘RQF’ name: “Request File Footer Record” values: – {name: “Record Type”, type: String, length: 3, tagValue: ‘RQF’ } – {name: “Total Employees”, type: String, length: 5 } |
Output Metadata
In Transform Message Component , we need to do the mapping according to the expected output
Links for reference
https://www.avioconsulting.com/blog/transforming-fixed-width-data-mulesoft
https://docs.mulesoft.com/mule-runtime/4.3/dataweave-flat-file-schemas
https://docs.mulesoft.com/mule-runtime/4.3/dataweave-cookbook-flat-file-read-and-write
https://www.prostdev.com/post/3-simple-steps-to-convert-a-flat-file-into-json-csv-xml