Convert Fixed-Width Files to CSV (Mainframe & Legacy Exports)

Fixed-Width Is Position, Not Delimiters

A fixed-width (fixed-length / positional) file reserves character columns for each field. There are no commas between values — alignment is the contract. Banks, insurers, government agencies, and ERP batch jobs in the US, UK, Canada, and Australia still emit these files daily. Analysts need CSV to open them in Excel; engineers need CSV to load warehouses.

Guessing widths by eye creates silent shifts: one off-by-one error misassigns every field to the right of the break.

Recognize a Fixed-Width File

Clues you are not looking at CSV/TSV.

• Values line up in a monospace font across rows.
• Spaces (or zeros) pad short fields.
• No consistent comma/tab/pipe counts per line.
• A companion copybook, layout PDF, or “positions 1–10 = ACCOUNT” spec exists.
• File may use .txt, .dat, or no extension — ignore the name; trust the layout.

1001Alice Smith         00012500NY
1002Bob Jones           00008350CA
1003Carol Nguyen        00024000TX

account,name,amount,state
1001,Alice Smith,00012500,NY
1002,Bob Jones,00008350,CA
1003,Carol Nguyen,00024000,TX

Step-by-Step: Fixed-Width → CSV

Do this before any Excel cleanup.

• Obtain the field layout: name, start position (or order), width, and trim rules.
• Open Fixed Width to CSV on Convert CSV Online.
• Enter widths in column order (e.g., 4,20,8,2) matching the spec.
• Preview the first 20 rows — verify a known value lands in the correct column.
• Trim padding if your destination wants clean names (keep raw if auditors need exact bytes).
• Download CSV; open in Online CSV Editor for type fixes (amounts, dates).
• Load to Excel, SQL, or your importer.

import csv

widths = [4, 20, 8, 2]
headers = ["account", "name", "amount", "state"]

def split_fixed(line: str) -> list[str]:
    row, i = [], 0
    for w in widths:
        row.append(line[i : i + w])
        i += w
    return row

with open("input.dat", encoding="utf-8") as inp, \
     open("output.csv", "w", newline="", encoding="utf-8") as out:
    writer = csv.writer(out)
    writer.writerow(headers)
    for line in inp:
        line = line.rstrip("\n\r")
        writer.writerow([c.strip() for c in split_fixed(line)])

Start Positions vs Width Lists

Specs are written two ways. Translate carefully.

• Confirm whether positions are 1-based (almost always in business docs).
• Confirm whether end positions are inclusive.
• Watch overlapping ranges — a documentation bug, not a CSV bug.

Padding, Trim, and Leading Zeros

Positional files love padding; spreadsheets hate it inconsistently.

• Right-space-padded names: trim for CRM imports; keep if round-tripping to fixed-width later.
• Left-zero-padded account and routing-style fields: keep as text in CSV or Excel will drop zeros.
• Numeric amounts may be zero-padded cents without a decimal (00012500 = 125.00) — apply scale after split.
• Packed/binary COBOL fields are not plain fixed-width text — this guide covers character-oriented files only.

Dates and Locales in Legacy Feeds

US feeds often use YYYYMMDD or MMDDYYYY without separators; UK feeds may use DDMMYYYY.

• Parse with an explicit format string after conversion — do not let Excel guess.
• Document the source country in the runbook (US payroll vs UK government extract).
• German partners may also deliver positional files with DDMMYYYY — same rule: explicit parse.

raw_date,iso_date
20260503,2026-05-03
03052026,2026-05-03

Round-Trip: CSV → Fixed-Width

Uploads to legacy portals often need the reverse direction.

• Clean and validate in CSV first.
• Use CSV to Fixed Width with the same width dictionary.
• Pad or truncate per the vendor’s rules — silent truncation is a production incident.
• Validate line length: every data row should equal the sum of widths (plus record terminators).

Common Mistakes

Expensive off-by-ones.

• Opening .dat in Excel as CSV and “helping” it with Text to Columns on commas that are inside fields.
• Using proportional fonts to visually mark cut points.
• Forgetting Windows CRLF vs Unix LF when measuring line length.
• Treating a header banner line as data (skip or separate layout for header records).
• Mixing EBCDIC binary transfers with ASCII width maps.

Best Practices

Layout first, conversion second.

• Store the width dictionary in version control next to sample files.
• Assert row length == sum(widths) in CI for fixture files.
• Preview known sentinel values after every layout change.
• Keep original fixed-width alongside CSV for audit.
• Prefer browser conversion for one-off analyst work; script for recurring jobs.

Why Use Convert CSV Online?

Convert CSV Online is free, browser-based, and requires no account for everyday conversions. Fixed Width to CSV applies your width map and previews columns before download; CSV to Fixed Width rebuilds positional files for portal uploads. Pair with the Online CSV Editor for cleanup. Works on Windows, macOS, and Linux browsers for US, UK, Canadian, and Australian ops teams.

Conclusion

Fixed-width to CSV is a layout problem before it is a spreadsheet problem. Lock the widths, preview relentlessly, preserve leading zeros, and only then analyze — whether the feed came from a US mainframe or a UK batch vendor.