🐍 Turn fixed width files into CSV with Python

Python November 5th, 2022 2 minute read.

CSV files are the most common medium for building Workday related integrations. Sometimes, I have the pleasure of dealing with a fixed width file, which is a whole lot less human friendly. Today I learned that pandas has this handy pandas.read_fwf for dealing with fixed width files.

This is a quick example of how to transform a list of fixed width files to CSV:

import pathlib
import pandas

BASE_DIR = pathlib.Path(__file__).resolve().parent

# A list of 1 or more files to convert
files = [
    BASE_DIR / "fixed_width_file_1.txt",
    ...
]

# Specify the widths of each column
widths = [5, ...]

# Process each file 1-by-1
for f in files:
    fwf = pandas.read_fwf(f, skiprows=1, widths=widths)
    # Any extra DataFrame manipulation

    # Specify a new filename and export it as CSV
    new_file_path = BASE_DIR / ("CSV_" + f.stem + ".csv")
    fwf.to_csv(new_file_path, index=False)

Additional notes:

widths is an optional parameter for pandas.read_fwf to specify where the column breaks should happen. Pandas can try to infer the columns, but it wasn't very accurate in my experience.
Another option is specifying a start and end for each column with colspecs. That was a lot more work for me than widths and would be a last resort.