Fixed-Width vs CSV: What Fixed-Column Files Are and How to Convert

Open a legacy payroll or bank export and you may see no commas at all — just neatly aligned columns padded with spaces:

NAME       ACCT      BALANCE
JOHN SMITH 001234    1250.00
ADA BYRON  009876     340.75

That is a fixed-width (or fixed-column) file. There is no delimiter; each field is defined by character positions — "name occupies columns 1–10, account 12–17, balance 19–25." The spec lives outside the file, in a layout document from the system that produced it.

Why they still exist

Mainframe and COBOL-era systems standardized on positional records decades before delimited formats took over. Banks, insurers, government agencies, and payroll providers still emit them because the format never breaks on data content — a balance of 1,250.00 needs no quoting gymnastics when the field simply owns its twenty-five characters.

The conversion problem

Converting fixed-width to CSV requires the position map. Without it, you must infer columns by eyeballing whitespace — workable for clean files, treacherous when values contain internal spaces (names like VAN DER BERG). A practical approach for occasional files:

1. Open the layout spec and list each field's start and end positions.
2. In a spreadsheet or script, slice every line at those offsets.
3. Write out as CSV and validate against row counts.

For delimiter-style files the browser handles it directly: detect what you actually have with CSV Diagnostic, then normalize separators with Change CSV Delimiter. If your "fixed-width" file turns out to use a rare delimiter after all, that path takes seconds instead of an afternoon.

Which format should you produce?

Downstream tools want CSV: databases (import guide), spreadsheets, and pandas all speak it natively. Keep fixed-width only when the receiving system demands it — and if you are stuck receiving one, budget time for the position map before promising anyone converted output.

A worked conversion

Given the layout "NAME columns 1–10, ACCT 12–17, BALANCE 19–25," every line converts positionally:

JOHN SMITH 001234    1250.00
0123456789^         ^     ^^^^^^^

Slice characters 1–10 (JOHN SMITH), 12–17 (001234), 19–25 (1250.00) — then emit JOHN SMITH,001234,1250.00. Two details bite in practice:

• Leading zeros are data: account 001234 must survive into CSV as text, or Excel will eat it (why that happens).
• Trailing-space padding is not data: strip it per field, but only after slicing — trimming first shifts your positions and silently corrupts everything downstream.

Validating the result

Positional conversion fails loudly (garbled rows) or quietly (shifted balances), so always verify:

1. Row count matches the source line-for-line.
2. Numeric columns sum to the control total printed on the layout sheet, if one exists.
3. Every row parses with the same column count via CSV Diagnostic.

When you meet one in the wild

Ask for two things from the producing system: the layout spec (field names plus positions) and a control report (row counts, totals). With those, fixed-width files convert deterministically; without them, you are pattern-matching whitespace and hoping. And once converted, keep the pipeline delimited end-to-end — mixing formats mid-flow reintroduces exactly the ambiguity the conversion just removed.

FAQ

Can I detect positions automatically? Partially. Repeated whitespace columns suggest boundaries, and tools can propose splits — but names containing spaces and right-justified numerics make proposals wrong often enough that checking against the real spec remains essential.

Why not just ask for CSV instead? Do ask — most modern exports offer it. Fixed-width survives mainly where the producer is old, regulated, or indifferent to your convenience.

Is fixed-width ever better? For machines, marginally: no escaping logic at all, fixed byte offsets for random access. For humans and modern pipelines, CSV wins on every practical axis.

More FAQ

Do I need quotes in CSV after converting from fixed-width? Only for fields containing commas, quotes, or newlines — standard minimal quoting applies exactly as with any other CSV.

What about dates in fixed-width files? They arrive as raw text (20260822 or 08/22/26 per the layout sheet). Standardize them into ISO format with Standardize Dates right after conversion, before any import relies on them.