A fixed-width file has no delimiter. There is no comma, no tab, no header row — the columns are defined by character positions, and the only thing that says where one ends and the next begins is a specification somewhere else. That is why the format outlived the machines that invented it, and why it still breaks spreadsheets today: every tool that expects “separator then value” reads the whole line as one column.
Where these files still come from
- Mainframe and COBOL exports — a COBOL
PIC X(20)field is twenty bytes, always, padded with spaces.
The layout lives in a copybook, not in the file.
- Bank and payment files — fixed record lengths with numeric fields whose sign is stored in the last digit
(zoned or packed decimal), so 0000123N is a number, not text.
- Government and EDI exchanges — the receiving system validates by position; a shifted character is a
rejected file, not a warning.
- Old reporting tools — printed reports “saved as text”, where column alignment came from the printer.
What a layout looks like
A customer record, 60 characters wide, as it appears in the file:
“ 000000123ACME EXPORTS ACCT-44 20241130 0000000152500 “
and the specification behind it:
| Position | Width | Field | Type |
|---|---|---|---|
| 1–9 | 9 | customer id | numeric, zero-padded |
| 10–29 | 20 | name | text, space-padded right |
| 30–41 | 12 | account | text |
| 42–49 | 8 | date | YYYYMMDD |
| 50–60 | 11 | amount in cents | numeric, zero-padded |
Nothing in the file says any of that. The widths above are the whole schema — which is why the same file without its specification is a wall of 60-character lines.
The three things that actually hurt
1. Without a copybook, the boundaries are a guess
You have a file of 200-byte records and no specification. The practical route is to look at a slice of raw lines in a monospaced editor with a character ruler: values that repeat in the same character range are the same column. Two clues make it reliable — a column that is blank in every row is padding, and a column whose values are all digits at a constant width is numeric with leading zeros, not text.
2. Leading zeros and padded spaces are data
000000123 is not 123. In a fixed-width file the width is the type: a nine-character numeric field that holds 123 is written 000000123, and the zeros survive only if the reader keeps them. The same applies at the other end — a 20-character name field is right-padded with spaces, and if those spaces are not trimmed, every later comparison (WHERE name = 'ACME') stops matching.
3. A record is not necessarily a line
Most fixed-width exports put one record per line. Some do not: mainframe transfers often concatenate records with no line break at all, so the file’s length is a multiple of the record length and reading it line by line produces one enormous “line”. Check first: if the file has newlines, the line length tells you the record length; if it has none, divide the file size by the record length and see whether the remainder is zero.
Encoding is a separate question
Fixed-width files are usually single-byte: cp1252 for Western European text, cp037 (EBCDIC) for older mainframe transfers. There is no BOM and no declaration; the only way to know is the specification or the source system. A file read with the wrong code page produces coherent-looking garbage — accented characters become ‚ or ?, and the column positions stay correct, which makes it look like a data problem rather than an encoding one.
Converting it into something a spreadsheet can read
The job is the same in every direction: declare the column widths, then write a delimited file or a workbook. In practice:
- Detect the widths from a sample — most converters can propose boundaries from repeated whitespace
patterns; treat the proposal as a starting point.
- Edit the boundaries by hand until the preview shows the columns you expect. This is the step where a
single character of drift turns a numeric column into two text columns.
- Choose the target — CSV when something else will read it, XLSX when a person will. Keep leading zeros by
writing those columns as text, not as numbers.
- Verify one known record against the specification, not just the row count: a file can have the right
number of rows and every value in the wrong column.
The same widths apply to the reverse direction: writing a fixed-width file from a spreadsheet means right-padding text, zero-padding numbers, and truncating anything longer than the field — silently, because the format has no way to complain.
When the file is 2 GB
Fixed-width files are often big, because that is what they were designed for. Splitting is safe only if the split respects the record length — cutting at an arbitrary byte offset produces a first file that ends mid-record. Convert first (a delimited file is line-based, and splitting by rows is then trivial), or split by record count rather than by size.
What to check before trusting the output
- One record copied from the source and compared field by field with the converted row.
- The column count in every row — a truncated final record is the most common defect in these files.
- Leading zeros and trailing spaces on a known value (
000000123,ACME). - The total row count against the specification, if one exists.
The fixed-width tools start from the same step: the widths. Once those are right, the rest is a normal conversion.
- Convert fixed-width text to CSV — set the widths, preview the columns,
write a delimited file
- Write a fixed-width file from CSV — the reverse direction, with padding
and truncation done for you
- All fixed-width tools — import into a database, split a huge file, merge several
If the file is not fixed-width after all — if the columns are separated by runs of spaces that shift by a character here and there — that is a different problem, and a TXT to CSV conversion is where to start.