Withdata Software

Fixed-Width Text Files: Columns by Position, Not by Separator

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

The layout lives in a copybook, not in the file.

(zoned or packed decimal), so 0000123N is a number, not text.

rejected file, not a warning.

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:

  1. Detect the widths from a sample — most converters can propose boundaries from repeated whitespace

patterns; treat the proposal as a starting point.

  1. 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.

  1. 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.

  1. 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

The fixed-width tools start from the same step: the widths. Once those are right, the rest is a normal conversion.

write a delimited file

and truncation done for you

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.