MuleSoft Flat File Transformation

Flat files are simple text files where each line represents one record. Banks, insurance companies, government agencies, and logistics companies exchange data in flat file formats. MuleSoft handles flat file transformation with DataWeave and the Flat File Schema format.

Types of Flat Files

Two types of flat files appear most commonly in enterprise integrations:

• Delimited files: Fields in each record are separated by a character like a comma (CSV), pipe (|), or tab. Field lengths can vary.
• Fixed-width files: Each field occupies a specific position and length in the line. No delimiter separates fields. You must know the field positions in advance.

Delimited vs Fixed-Width Comparison

Delimited (CSV): Field values separated by commas
Header:   Name,Age,City,Balance
Record 1: Alice,32,New York,1500.00
Record 2: Bob,28,Chicago,890.50
Record 3: Carol,45,Houston,3200.00

Fixed-Width: Each field at a fixed position, space-padded
Position:  1-15 = Name, 16-17 = Age, 18-27 = City, 28-34 = Balance
Record 1:  Alice          32New York  1500.00
Record 2:  Bob            28Chicago   0890.50
Record 3:  Carol          45Houston   3200.00

Reading a CSV File

The most common flat file format is CSV. MuleSoft's File connector reads a CSV file and the Transform Message component converts it to JSON for processing.

CSV to JSON Flow

[File Listener: reads employees.csv when file appears in folder]
      |
      v
[Transform Message]
  Input:  application/csv (separator=",", header=true)
  Output: application/json
  Script:
    %dw 2.0
    output application/json
    ---
    payload map (row) -> {
      "name":    row.Name,
      "age":     row.Age as Number,
      "city":    row.City,
      "balance": row.Balance as Number
    }
      |
      v
[Database: bulk insert employees]

Writing Output as CSV

To produce a CSV file from JSON data, set the output format to application/csv.

JSON to CSV DataWeave

Input (JSON payload):
[
  { "product": "Pen",  "sold": 150, "revenue": 225.00 },
  { "product": "Book", "sold":  42, "revenue": 504.00 },
  { "product": "Bag",  "sold":  18, "revenue": 450.00 }
]

DataWeave:
%dw 2.0
output application/csv separator=",", header=true, quoteValues=true
---
payload map (item) -> {
  "Product Name": item.product,
  "Units Sold":   item.sold,
  "Revenue USD":  item.revenue
}

Output (CSV file):
"Product Name","Units Sold","Revenue USD"
"Pen",150,225.0
"Book",42,504.0
"Bag",18,450.0

Fixed-Width File Schema

Fixed-width files require a schema file that describes the exact position and length of each field. MuleSoft uses the Flat File Schema format with a .ffs extension.

Fixed-Width Schema File (employees.ffs)

form: FLATFILE
structures:
- id: 'Employee'
  name: Employee Record
  data:
  - { idRef: 'EmpRecord' }
segments:
- id: 'EmpRecord'
  name: Employee
  values:
  - { name: 'EmpId',    usage: M, type: AN, length: 8  }
  - { name: 'LastName', usage: M, type: AN, length: 20 }
  - { name: 'Salary',   usage: M, type: N,  length: 10 }
  - { name: 'JoinDate', usage: M, type: AN, length: 8  }

Fixed-Width File Diagram

Character positions (each character counts):
Pos:  1        9                    29         39       47
      |        |                    |          |        |
Line: EMP-0001 Smith, John          00075000   20220315

Field:  EmpId(8) | LastName(20)         | Salary(10) | JoinDate(8)

Reading Fixed-Width Files with Flat File Connector

Add the Flat File schema to your project under src/main/resources/schemas/. Configure the Transform Message component with:

• Input type: application/flatfile
• Schema path: schemas/employees.ffs
• Structure: Employee

After reading, the flat file data becomes a Java object that DataWeave transforms to JSON.

Handling Headers and Trailers

Many bank and financial flat files have a header record on the first line and a trailer (summary) record on the last line. The data records sit in between.

Bank File Format Example

Line 1 (Header):   HDR20240115ACMECORP0000150
Line 2 (Record):   REC001ALICE SMITH     0000025000CR
Line 3 (Record):   REC002BOB JONES       0000008750DR
...
Last Line (Trailer):TRL000000150000033750

Splitting Header, Records, Trailer with DataWeave

%dw 2.0
output application/json
---
do {
  var lines = (payload as String) splitBy "\n"
  var header  = lines[0]
  var trailer = lines[-1]
  var records = lines[1 to -2]
---
{
  "fileDate":    header[3 to 10],
  "company":     header[11 to 18],
  "recordCount": header[19 to 24] as Number,
  "transactions": records map (line) -> {
    "seqNo":   line[3 to 5],
    "name":    trim(line[6 to 25]),
    "amount":  (line[26 to 35] as Number) / 100,
    "type":    line[36 to 37]
  },
  "totalAmount": trailer[6 to 17] as Number / 100
}
}

EDI Flat Files

EDI (Electronic Data Interchange) is a flat file standard used heavily in healthcare (HIPAA), retail, and supply chain. MuleSoft has dedicated EDI connectors for X12 (used in the US) and EDIFACT (used internationally). These connectors parse EDI transactions like purchase orders (850), invoices (810), and shipping notices (856) without custom parsing code.

Best Practices for Flat File Integrations

• Always define the encoding explicitly. Flat files from legacy mainframe systems often use EBCDIC or ISO-8859-1 instead of UTF-8.
• Validate record counts. If the trailer says 150 records, verify you actually processed 150 records before marking the file complete.
• Archive processed files instead of deleting them. Move them to a processed folder with the timestamp in the file name.
• Handle empty lines. Some file generators add a blank line at the end of the file. Filter it out before processing records.