CSV to Fixed Width Converter

A CSV to fixed width converter turns a spreadsheet into space-padded records with every column starting at the same character position. This one right aligns your numeric columns so the digits line up, widens a column rather than letting a long value shift everything after it, and appends the column boundaries as a spec whoever reads the file can configure a parser from. Nothing is uploaded.

Need to trim, pad or reorder columns first? Open the app

The layout is not in the file, and that is the problem

A CSV carries its own structure. Wherever the commas are, that is where the fields are, and any parser can work it out from the bytes. A fixed-width record carries nothing. It is one long string of characters, and the only thing that says field two starts at position 11 is a document, an email, or somebody's memory of a system written in 1997.

That is why fixed-width files get misread. The layout drifts out of step with the file, a column gets one character wider in a new release, and everything downstream silently shifts by one. The value in the amount column becomes the last digit of the previous field plus the first four of the amount, and it is still a number, so nothing errors.

So this converter writes the layout into the file. Pick a specification and you get a comment block appended with the exact character boundaries. Three forms, because the person reading it back is one of three people:

An offset table, for a human: field name, start, end, width and alignment, one line each. A pandas snippet, for an analyst: a colspecs list and a names list you paste straight into read_fwf. And a COBOL record layout, for the mainframe end of the pipeline: PIC clauses with one-based inclusive positions and the record length.

A worked example

Four rows of stock:

sku,description,warehouse,on_hand
AC-00412,"Aluminium bracket, 40mm",Rotterdam,1420
BR-01730,"Bronze bushing, 17mm bore",Hamburg,3105
CL-00088,"Cable clip, nylon, black",Felixstowe,18400
GR-04510,"Gear, spur, 45 teeth",Rotterdam,410

With two spaces between fields and the pandas spec:

sku       description                warehouse   on_hand
AC-00412  Aluminium bracket, 40mm    Rotterdam      1420
BR-01730  Bronze bushing, 17mm bore  Hamburg        3105
CL-00088  Cable clip, nylon, black   Felixstowe    18400
GR-04510  Gear, spur, 45 teeth       Rotterdam       410

# Read this file back with pandas:
# import pandas as pd
# colspecs = [(0, 8), (10, 35), (37, 47), (49, 56)]
# names = ["sku", "description", "warehouse", "on_hand"]
# df = pd.read_fwf("data.txt", colspecs=colspecs, names=names, skiprows=1)

The on_hand column is right aligned, so 1420 and 18400 end at the same character and you can read the column down. Everything else is left aligned. The description column is 25 characters wide because that is what its longest value needs, not because anyone picked a number.

The colspecs pairs are half-open, which is what pandas expects, so no off-by-one translation is needed when you paste them.

Overflow, and the only two honest options

What should happen when a value is wider than the space allotted to it? A naive padder does nothing, and the value just runs on. That is the worst answer available, because it shifts every field after it on that one row while leaving every other row correct. A reader working by offset gets garbage from that row onwards and no error, which is exactly the failure fixed width exists to prevent.

The default here is to widen the column to fit its longest value. Every record stays the same length as every other, the boundaries in the spec stay true, and the only cost is a slightly wider file.

The other option is truncation, and it is available with a maximum field width. Sometimes the receiving system genuinely has a 30-character name field and anything longer has to be cut. When that happens the count of truncated cells is reported, so you know how many values lost their tails and can decide whether to shorten them properly upstream instead.

A line break inside a cell becomes a space, with a count. There is nothing else to do: a fixed-width record is one line by definition, and a newline in the middle of one turns a record into two malformed ones with no continuation syntax to escape to.

Alignment, gaps and record length

Numeric columns are right aligned and text columns are left aligned, which is the convention every printed report has used since printed reports existed. It is not decoration: right-aligned digits line up on the units place, so a column of figures can be scanned down and totalled by eye, and a wrong order of magnitude is visible instantly.

Types are decided once per column, which is what keeps that promise. A column mixing 9.99 and 12.50 is text, because 12.50 as a number prints back as 12.5. So it is left aligned, all of it, rather than half one way. If your data needs something else, the whole table can be forced left, right or centre.

The header row is always left aligned, even over a numeric column. A right-aligned heading over a right-aligned column of two-digit numbers ends up floating in the middle of nothing, and a heading is a label rather than a value.

Two spaces between fields is the default, adjustable from one to four. Two is the narrowest gap that still reads as a gap when a value ends near the column edge, and it is also the minimum the boundary detection on the return leg looks for. Trailing padding on each line is trimmed: it is invisible, and on a sparse file it can double the size.

A record longer than 200 characters gets a warning. Plenty of fixed-width consumers assume 80 or 132 columns, inherited from terminal and line-printer widths, and finding that out after the transfer is the expensive way.

Reading one back

The fixed width to CSV page is the return leg. It detects the boundaries automatically, and the way it does so is worth knowing because it explains when it will fail.

A position counts as a boundary when every sampled line is blank at that character. In a real fixed-width file, that means there is a column of whitespace running the full height of the file at each field edge, which is the signal a delimited file does not give off. Two or more adjacent blank positions is a gap; one is not, since a single space appears inside plenty of values.

That fails in one specific case: when a field is completely full on every row, with no space before the next one. Then there is no blank column and the two fields read as one. For that, type the character positions in by hand, which is precisely what the offset table appended to the file gives you. Providing the spec here is what makes the round trip reliable rather than lucky.

Questions

Why does the output include a comment block of column positions?

Because a fixed-width file does not carry its own layout. Unlike a CSV, where the delimiter tells a parser where the fields are, a fixed-width record is one long string and the boundaries live in a document somewhere else, or in somebody's memory. Appending the spec to the file means whoever reads it back can configure their parser from the file itself. You can pick a plain offset table, a ready-to-paste pandas read_fwf call, or a COBOL-style record layout.

What happens when a value is wider than its column?

The column is widened to fit. Letting a value overflow would shift every field after it on that row, which silently corrupts the file for anyone reading by offset, and that is the failure mode fixed width exists to avoid. If you set a maximum field width instead, over-long values are cut and the count of truncated cells is reported, so the loss is a choice you made rather than one that happened to you.

Are numbers right aligned?

Yes, when the column types as numeric. Right-aligned digits line up on the units place, which is what makes a printed report readable down a column and lets you total it by eye. Text stays left aligned. Types are decided once for the whole column, so a column mixing 9.99 and 12.50 stays text rather than half aligned one way. You can override the whole table to left, right or centre.

What happens to a line break inside a cell?

It becomes a space, and the count is reported. A fixed-width record is one line by definition; a newline in the middle of one turns a single record into two malformed ones, and every reader downstream miscounts from there. There is no continuation syntax in fixed width to escape to.

How many spaces go between fields?

Two by default, adjustable from one to four. Two is the narrowest gap that still reads as a gap when a value happens to end near the column edge, which is why report generators have used it for decades. One space is tighter and works when every column is comfortably narrower than its heading; more than two mostly wastes line width.

How do I read the file back?

Use the fixed-width to CSV converter on this site. It detects the boundaries from the whitespace pattern, which works because a real fixed-width file has a column of blanks running down the whole file at each boundary. If the detection gets it wrong, you can type the character positions in yourself, which is exactly what the offset table in the appended spec gives you.

Is anything uploaded?

No. The file is read and written in your browser tab, with no server round trip, nothing kept between visits and no row cap. Fixed-width files usually come out of a bank, a payroll system or a lab instrument, which are exactly the files you do not want passing through somebody else's server.

Convert your CSV to fixed width

No sign-up, no upload, no row cap. Aligned columns, no silent overflow, and a layout spec in the file.

Back to the converter