Convert Fixed-Width Text Files to Excel with pandas

Not every export is a CSV. Banks, payroll bureaus, insurers and systems that started life on a mainframe still produce fixed-width files, where each field occupies the same character positions on every line: the account number in columns 1–10, the name in 11–40, the amount in 41–52. There are no delimiters, numbers often carry no decimal point, and one file can mix several kinds of record. Excel's text import wizard can split such a file if someone clicks the column breaks in by hand every time; pandas.read_fwf does it from a layout written down once. This page is part of Handling Excel File Formats and Conversions.

Prerequisites

pip install pandas openpyxl

payroll_20260928.txt is a payment file from a payroll bureau. The first line is a header record (H) with the date and batch, each payment is a detail record (D), and the last line is a trailer (T) with the record count and the net total. Its layout document gives the start and end column of every field in a detail record; that layout is saved here as layout.csv.

from pathlib import Path

print(Path("payroll_20260928.txt").read_text(encoding="ascii"))

Step 1 — Describe the layout, then read

read_fwf takes colspecs, a list of (start, end) pairs in Python slice terms: zero-based, end exclusive. Layout documents almost always number columns from 1 with inclusive ends, so convert once, in code, rather than by hand for each field:

import pandas as pd

layout = pd.read_csv("layout.csv")
colspecs = [(s - 1, e) for s, e in zip(layout["start"], layout["end"])]
print(colspecs)

raw = pd.read_fwf("payroll_20260928.txt", colspecs=colspecs, names=layout["field"].tolist(),
                  dtype=str, header=None)
print(raw)

dtype=str reads every field as text. That is deliberate: account 0000123456 would otherwise become the integer 123456, losing the leading zeros that make it a valid account number (see Keep Leading Zeros When Reading Excel with pandas for the same problem in workbooks). Types are applied afterwards, field by field, from the layout.

The output also shows the catch with mixed-record files: the header and trailer lines were forced through the detail layout. The header's "account" is 20260928PA — the date run into the start of the sender name — the trailer's is a slice of its record count and total, and both have no amount. Nothing about them raises an error; they just look like two odd rows of data.

Step 2 — Split the record types

Filter on the record-type column and keep only details for the data sheet. Parse the header and trailer separately with their own positions:

details = raw[raw["record_type"] == "D"].drop(columns="record_type").reset_index(drop=True)

text = Path("payroll_20260928.txt").read_text(encoding="ascii").splitlines()
header_line = next(l for l in text if l.startswith("H"))
trailer_line = next(l for l in text if l.startswith("T"))
header = {"date": pd.to_datetime(header_line[1:9], format="%Y%m%d"),
          "sender": header_line[9:30].strip(), "batch": header_line[30:39]}
trailer = {"count": int(trailer_line[1:7]), "net_cents": int(trailer_line[7:22])}
print(header)
print(trailer)

Header and trailer records are one line each, so plain string slicing is clearer than a second read_fwf call. Keep their positions next to the detail layout in the same layout file when there are many of them.

Step 3 — Apply types: implied decimals and signs

The amount field 000000245067 means 2,450.67: the layout says it is in cents, with an implied decimal point two places from the right. The sign is a separate field, C for credit and D for debit. Convert both, and strip the padding from text fields:

details["name"] = details["name"].str.strip()
details["amount"] = details["amount"].astype(int) / 100
details["amount"] = details["amount"].where(details["sign"] == "C", -details["amount"])
details = details.drop(columns="sign")
print(details)
print(details.dtypes.to_dict())

Dividing by 100 after converting to an integer avoids floating-point surprises in the parse itself. For money that must add up to the cent in later steps, keep a cents column as integers and do the arithmetic on that, converting to currency only for display — the checks in the next step use cents for exactly that reason. Other layouts put the sign inside the number (a trailing -, or the COBOL "overpunch" convention where the last digit becomes a letter); those need a small converter, and Convert Trailing Minus and CR/DR Amounts with pandas covers the common forms.

Step 4 — Check the file against its trailer

The trailer exists so the receiver can prove nothing was lost or altered in transit. Check it before producing anything:

cents = (details["amount"] * 100).round().astype(int)
problems = []
if len(details) != trailer["count"]:
    problems.append(f"record count {len(details)} != trailer {trailer['count']}")
if cents.sum() != trailer["net_cents"]:
    problems.append(f"net total {cents.sum()} != trailer {trailer['net_cents']}")
if not details["account"].str.fullmatch(r"\d{10}").all():
    problems.append("malformed account numbers")
print(problems or "file agrees with its trailer")

A mismatch means a truncated transfer, a line with a shifted field, or a file edited by hand — all reasons to stop and alert someone rather than publish a report (see Validate an Excel Report Before Sending It). The account-number pattern check catches the most common corruption in fixed-width files: a line one character too long or too short, which shifts every field after the fault.

Step 5 — Write the workbook

Put the header information above the table, keep the account column as text, and format the amounts:

title = f"{header['sender']} — batch {header['batch']} — {header['date']:%d %b %Y}"
with pd.ExcelWriter("payroll_20260928.xlsx", engine="xlsxwriter") as xw:
    details.to_excel(xw, sheet_name="Payments", index=False, startrow=2)
    wb, ws = xw.book, xw.sheets["Payments"]
    ws.write(0, 0, title, wb.add_format({"bold": True, "font_size": 13}))
    ws.set_column("A:A", 13, wb.add_format({"num_format": "@"}))
    ws.set_column("B:B", 24)
    ws.set_column("D:D", 13, wb.add_format({"num_format": "#,##0.00;[Red]-#,##0.00"}))
    last = len(details) + 3
    ws.write(last, 2, "Net total", wb.add_format({"bold": True}))
    ws.write_formula(last, 3, f"=SUM(D4:D{last})", wb.add_format({"bold": True, "num_format": "#,##0.00"}))
print(pd.read_excel("payroll_20260928.xlsx", skiprows=2, dtype={"account": str}).head(3))

Letting pandas infer the columns

When no layout document exists, read_fwf can guess. Its default, colspecs="infer", looks at the first infer_nrows lines (100 by default) and places a boundary wherever every line has a space in the same column:

guess = pd.read_fwf("payroll_20260928.txt", header=None, dtype=str)
print(guess.shape)
print(guess.head(3).to_string())

On this file it finds just two columns: one gap after the padded name and nothing else, because the account runs straight into the name (0000123456OKAFOR) and the department, amount and sign touch each other. Inference works for files where every field is separated by at least one space of padding, such as reports formatted for printing, and fails on files designed for machines, like this one. It is also thrown off when the first 100 lines are not representative — a long block of header records, say. Use it to explore an unknown file, then write the real positions down in a layout file and read with explicit colspecs from then on.

Common pitfalls

• Off-by-one colspecs. Layout documents count from 1 with inclusive ends; Python slices count from 0 with exclusive ends. Convert with (start - 1, end).
• Losing leading zeros. Read with dtype=str and convert numeric fields explicitly.
• Implied decimals left in. An amount field without a decimal point is almost never in whole currency units; check the layout.
• Encoding. Older systems write cp1252, latin-1 or even EBCDIC (cp500). Pass encoding= and check a name with accents.
• Tabs. A tab counts as one character, so a file with tabs is not really fixed-width; replace them first or it will not line up.

Performance notes

read_fwf does not use the fast C tokenizer that read_csv has; in a quick test with 300,000 lines it took about two and a half times as long as read_csv on the same data as a CSV — half a second — so it is still comfortably fast for files of a few hundred thousand lines. For multi-gigabyte files, pass chunksize= and process the details in pieces, accumulating the count and the cents total as you go so the trailer check still covers the whole file. Workbooks hold at most 1,048,576 rows per sheet; a file bigger than that belongs in a database or a Parquet file, with Excel reserved for the summary.

Conclusion

Keep the record layout in a small table, convert its 1-based inclusive positions to colspecs, and read with read_fwf(..., dtype=str). Split header, detail and trailer records by their type code, apply types field by field — strip padding, divide implied-decimal amounts, apply signs — and check the record count and total against the trailer before writing the workbook with text-formatted IDs and number-formatted amounts.

Frequently asked questions

How do I read a fixed-width file in pandas? Use pandas.read_fwf with colspecs, a list of (start, end) positions with zero-based starts and exclusive ends, or widths, a list of field lengths. Pass names for the columns and dtype=str to keep leading zeros.

What does colspecs="infer" do? It looks at the first rows of the file and puts a column boundary wherever every row has whitespace at the same position. It works for space-padded reports and fails when fields touch each other without a gap.

How do I handle amounts without a decimal point? Many fixed-width layouts store money in the smallest unit with an implied decimal point, so 000000245067 is 2450.67. Convert the field to an integer and divide by 100, or by the power of ten the layout specifies.

How do I read a file with header and trailer records? Read every line with the detail layout and keep only rows whose record-type field marks a detail, then slice the header and trailer lines separately. Use the trailer's count and total to check the detail rows.

Related

• Up one level: Handling Excel File Formats and Conversions — every format that ends up in a workbook.
• Convert CSV Files to Excel with Python — the delimited cousin of this job.
• Reconcile Excel Totals with Control Totals — the trailer check, generalised.
• Format ZIP Codes and IDs with Leading Zeros — keeping account numbers intact in the workbook.