Fixed Width Files and Converting Them to CSV Safely

A fixed width file looks like a table that someone printed without gridlines. There are no commas, no tabs and no quotes. Each field starts at a certain column and ends at another, and short values are padded with spaces. Banks, tax offices, shipping carriers and older ERP systems still produce them, and they arrive named .csv or .txt or nothing, with a PDF somewhere that says which columns mean what. Converting one to CSV is not hard, and the ways it goes wrong are specific: a missing layout, a line that is a character too short, an amount with no decimal point and a name with an accent. This article walks through all four, with a small script and the pandas equivalent. It sits in the group of formats covered in CSV dialects and variants you will run into.

The layout comes first

Nothing inside the file says where fields begin. The layout is a document, usually called a record specification or copybook, that lists each field's name, start position, length and type. For the sample, a three-line order file, it says:

and the file looks like this:

1001Acme Ltd      20261001 120.50
1002Beta Corp     20261001  89.90
1003Cy Foods      20261002  15.00

Positions in specifications are usually one-based and inclusive, so "5 to 18" is characters 5 through 18. In Python, slicing is zero-based and the end is exclusive, so the same field is line[4:18]. Mixing the two is the classic off-by-one, and it shows up as every field containing a character from its neighbor. Convert the layout table once, in a list, and check it against the first line by eye.

If no layout exists, you can sometimes recover one by looking at several lines in a monospaced font and noticing the columns where spaces always line up, but you are guessing, and the first value that fills its whole field will show you where the guess was wrong. Ask for the layout.

A script that checks as it slices

The conversion is a loop over lines with a list of offsets. What makes it safe is the length check before the slicing:

import csv, sys

LAYOUT = [("order_id", 0, 4), ("customer", 4, 18), ("placed_on", 18, 26), ("total", 26, 33)]

writer = csv.writer(sys.stdout, lineterminator="\n")
writer.writerow([name for name, _, _ in LAYOUT])
with open(sys.argv[1], encoding="utf-8") as f:
    for line_no, line in enumerate(f, start=1):
        line = line.rstrip("\n")
        if len(line) != LAYOUT[-1][2]:
            sys.exit(f"line {line_no}: {len(line)} characters, expected {LAYOUT[-1][2]}")
        writer.writerow([line[a:b].strip() for _, a, b in LAYOUT])

order_id,customer,placed_on,total
1001,Acme Ltd,20261001,120.50
1002,Beta Corp,20261001,89.90
1003,Cy Foods,20261002,15.00

The csv.writer handles quoting, so a customer name with a comma is protected on the way out. Run on a second file where the date on line two lost a digit (2026100 instead of 20261001), the script stopped with a message instead of producing a plausible wrong row:

order_id,customer,placed_on,total
1001,Acme Ltd,20261001,120.50
line 2: 32 characters, expected 33

Without the check, the slice for total would have started one character early and produced 89.9 or worse, and nothing else would have complained. A fixed width file has no redundancy, no delimiter to count, so the line length is the only integrity check you get, and you should use it. Some producers pad the last field and some trim trailing spaces from the line, so confirm with the provider whether every line is the full width; if not, pad each line to the width with line.ljust(33) before checking, and say why in a comment.

The same job with pandas

If you are in a notebook, read_fwf does the slicing and takes the same offsets as colspecs:

import pandas as pd
df = pd.read_fwf("fixed.txt", colspecs=[(0,4),(4,18),(18,26),(26,33)],
                 names=["order_id","customer","placed_on","total"], dtype=str)
print(df.to_string(index=False))

order_id  customer placed_on  total
    1001  Acme Ltd  20261001 120.50
    1002 Beta Corp  20261001  89.90
    1003  Cy Foods  20261002  15.00

The same rows. The argument that matters is dtype=str. Left to infer, pandas converts order_id to integers and, for identifiers with leading zeros, drops them (why pandas turns zip codes into numbers describes the same trap with CSV). read_fwf also strips padding for you, which is convenient and means you cannot tell a field of spaces from an empty one, so if that matters, use the script. It has an infer_nrows option that guesses column boundaries from the first lines when you omit colspecs, and I would only use that to explore a file, never to load one.

The values need decoding too

Slicing gives you strings, and fixed width formats have conventions for what the strings mean that no tool can infer.

Implied decimals. An amount of 12050 with a layout note "two implied decimals" is 120.50. The decimal point is not in the file, because every byte was spent on digits. Read the number as text, divide by 100 in a decimal type and keep the scale in your schema, not in a float. A field that is "7 digits, 2 implied decimals" can be 99,999.99 at most, which also tells you the range check to apply.

Signs. Money can arrive with a trailing sign (00012050-), a leading sign, or an overpunched last digit, where the last character is a letter that encodes both the digit and the sign (} for negative zero, J to R for negative 1 to 9 in some mainframe conventions). Read the specification for each numeric field and write a function per convention.

Dates. 20261001 is easy. 261001 is not, and neither is a Julian date (26274). Parse with an explicit format and reject anything that fails.

Flags and codes. A one-character field Y or N, or 1 and 0, or a status code with a lookup table in the specification. Validate against the allowed set.

Spaces. An all-spaces numeric field means missing, not zero. Decide, and write it down.

Characters are not bytes

A layout counts positions in characters if the producer is modern and in bytes if it is old. The difference appears the first time a name contains é. In UTF-8 that is two bytes, so a line that is 33 characters is 34 bytes, and a slicer that works on bytes (or a producer that padded by bytes) puts everything after the accent in the wrong place. In the legacy single-byte code pages, 33 characters are 33 bytes and nothing shifts. Ask what the encoding is and which unit the layout uses, test with a line containing an accented name, and open the file as text in the encoding the producer states. Mainframe files may be EBCDIC, which Python reads with the cp037 or cp500 codec, and which will look like random punctuation if you open it as UTF-8. East Asian text adds a further question, because a wide character takes one position in a text slice and two columns on a terminal, so a file that looks aligned in a terminal may not slice as you expect.

A converted file is not yet a clean one

Slicing yields strings with padding stripped, and that is all. Run the checks you would run on any CSV: the number of rows against a control total if the file has a trailer record, uniqueness of the key, a parse of every date and number (validating a CSV file). Many fixed width files end with a trailer line carrying a record count and a hash total, which the slicing loop would try to parse as an order; detect it by a record type character in the first column and handle it separately. Others begin with a header record. Treat the first and last lines as special until you have confirmed they are not.

When a fixed width file is not what it claims

Some files called fixed width are not. A file whose lines vary in length is delimited with spaces and aligned for display, and a splitter on whitespace is the right reader, except where a field contains a space, such as Beta Corp, in which case you are back to needing positions. A report printed to a file, with page headers, page numbers and column titles repeated every 60 lines, has the layout of a fixed width file and the junk of a printout (detecting and handling embedded headers repeated mid file). And a file produced by a program that pads to a width but also uses a delimiter is better read as delimited with a trim.

Once the data is in CSV, treat the layout document as part of the feed: keep it in the repository next to the script, and assert in a test that the offsets in the code match the table in the document. Pandas documents the reader in its read_fwf reference, and Python's csv module in the standard library reference.