Fixed Width to CSV Converter

A fixed-width file does not carry its layout. There is no delimiter to look for, so the boundaries have to be inferred from the shape of the text, and the signal is simpler than it sounds.

A character position counts as a gap when every line is blank at that position. In a real fixed-width report that produces a column of whitespace running the full height of the file at each field edge, which is exactly what a delimited file does not have. Two or more adjacent blank positions is a gap; one is not, because a single space appears inside plenty of ordinary values.

Take this:

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

The description column contains commas and spaces, so no delimiter sniffing would ever get this right. But the columns at positions 8 to 10, and again after "40mm", are blank on every single line, and that is the boundary. Four fields, cleanly.

What comes back has its padding trimmed:

SKU,DESCRIPTION,WAREHOUSE,ON HAND
AC-00412,"Aluminium bracket, 40mm",Rotterdam,1420
BR-01730,"Bronze bushing, 17mm bore",Hamburg,3105

The commas inside the descriptions are quoted properly, which is the other half of the job and the reason pasting a fixed-width file into a spreadsheet as comma-delimited does not work.

The first line is treated as a header by default, since almost every report has one. Turn that off and it is kept as data, with columns named column_1 upward. A blank cell in the header row gets a placeholder name rather than leaving a column with no heading, and duplicate headings are suffixed apart so no column is lost when the rows become objects downstream.

Short lines are the other thing to know about. Trailing spaces get stripped somewhere in almost every transfer, so a row whose last field is empty arrives shorter than the others. Those are padded out with empty cells and the count is reported, which keeps the table rectangular and tells you it happened in case it means something other than a blank field.

Every value is trimmed of the padding around it, so a field written as Hamburg       arrives as Hamburg. Leading spaces that were alignment padding on a right-aligned numeric field go the same way.

Blank lines are skipped rather than becoming empty rows. A report with a blank line between page breaks converts cleanly.

What is not attempted is type conversion. Everything comes out as text, so a zero-padded account number keeps its zeros and a code that looks like a date stays a code. If you want types, the app will profile the columns once the file is a table.