Text to CSV Converter

Three kinds of file, one complaint

People arriving at a page like this are holding one of three things. Most tools answer only one of them, which is why the search takes so long.

• A fixed width report. A mainframe extract, a COBOL copybook dump, an AS/400 spool file, a bank statement laid out the way that bank has laid them out since 1994. No separator anywhere; each field begins at a character position and is padded with spaces to a set length. The people who need this are usually migrating something old under a deadline.
• A delimited file that happens to end in .txt. Tabs, pipes or semicolons inside, and an extension that Windows attached when somebody saved out of Notepad. The content is already a table. Only the label is wrong, and only sometimes.
• A table held together by spaces. Console output, a copied terminal table, a summary block at the bottom of a log. One record per line, columns lined up by eye, punctuation nowhere in sight.

The advice you meet first is to rename the file, and it works in exactly one case: a file that already has commas between fields, where the extension was the only thing wrong. Rename anything else and you get a CSV with one immense column, because not a character of the content has changed. Splitting the line into fields is the job. Renaming skips the job.

How the layout gets worked out

Auto asks two questions in order and stops at the first clear answer.

The first is whether one character is doing the work. Comma, semicolon, tab and pipe are each counted across the first 200 non-blank lines, ignoring any that sit inside double quotes, and a candidate has to appear at least once per line and yield the same field count on nine lines in ten. Nine rather than ten, because a single mangled line at the bottom of a 50,000 line export should not sink an obvious guess. When two characters both qualify, the more consistent wins, and a tie goes to whichever produces more columns.

The second is about alignment. A character position counts as blank only when it is blank on every sampled line, so a run of them is a column of whitespace standing the full height of the block. Two in a row ends one field and starts the next. Two is the narrowest gap real reports use, and insisting on two is what stops a value with a single space inside it, a surname or a city, from being cut in half. Positions past the end of a short line count as blank, so ragged lines cost nothing.

If neither question lands, runs of spaces are tried as a separator, and failing that you get one column per line with a note saying so rather than a silent shrug. Every field is trimmed of its padding on the way out.

The Layout control overrides all of that. Delimiter takes the character route and lets you name the character, including two or more spaces. Fixed width takes the alignment route. The choice is not cosmetic: give a block where one line has a blank middle value to fixed width and the gap stays in the middle column, while runs of spaces slides the city left into the name column and pads the end. Slicing by position knows where a column belongs; counting separators only knows how many it found.

Things that catch people out

• Only the first 200 lines are measured. That keeps a 100 MB file responsive, and it means a second report appended to the bottom with a different layout goes unnoticed. The boundaries found at the top get applied all the way down. Split the file first if it holds two shapes.
• Two spaces inside a value split it. Under fixed width a value like North East is safe, because slicing happens at fixed positions. Under the two-or-more-spaces setting any double space inside a value becomes a column break, and the row shifts left from there on.
• Quotes are literal in fixed width mode. On the delimited route quotes do their normal job and a line break inside a quoted field is kept. On the alignment route a quote is just another character in a column, so it survives into the cell and gets escaped in the CSV, which looks alarming and is correct.
• Short lines are padded, and counted. A line stopping before the last column is filled out with empty cells and the result says how many did that. A large number there usually means the boundaries are wrong, not the data.
• Empty header cells get placeholder names. A blank in the header row becomes column_2, column_5 and so on, with a note counting them, since a nameless column is awkward for whatever reads the file next.
• Excel will undo the leading zeros. The download really does contain 000145, and double clicking it gets you 145. Bring the file in through Data, then From Text/CSV, and mark that column as text before the import runs.