Pipe Delimited Files From Legacy Systems Without Surprises

Pipe delimited files are the quiet workhorse of older systems. A bank sends settlement files, an insurer exchanges policy records, a government agency publishes a register, and the file uses | between the fields because pipes almost never appear in names, addresses and amounts. That reasoning mostly works, and the cases where it does not are the ones that waste a week. There is usually no quoting, no escaping and no header you can trust, and the file was designed to be read by a program that knows the layout. This article covers how to read one properly, the three traps that account for most failures, and how to tell when a pipe file is not what it claims. The format is one of the variants mapped in CSV dialects and variants you will run into.

Why a pipe

A delimiter has to be a character that does not occur in the data. Commas are everywhere in text: addresses, company names, numbers with grouping. Tabs are invisible and editors convert them. A pipe has none of those problems, and it looks like a column border on screen, which is part of its charm. The designers of these formats made a deliberate choice that quoting was unnecessary, and that choice simplifies the writer, which is a few lines in a COBOL program, and pushes all the risk to the reader.

That risk is the first thing to understand. In RFC 4180 CSV, a field can contain any character if it is quoted. In a pipe file, a field can contain any character except a pipe, a line break and the escape character if there is one, and the file does not tell you which rules applied. Always ask for the specification: whether pipes inside values are replaced, whether there is an escape (\| is common), whether the file has a header and whether it ends with a trailer.

The sample

Here is a small file with every trap in it. The header has a trailing pipe, every data line does too, one customer name contains a pipe and the last line is a trailer carrying a record count:

order_id|customer|total|
1001|Acme Ltd|120.50|
1002|Beta | Corp|89.90|
TRAILER|2||

Read with every column as text and errors ignored, DuckDB reported four columns where a person sees three:

DESCRIBE SELECT * FROM read_csv('pipe.txt', delim='|', header=true, all_varchar=true, ignore_errors=true);
SELECT * FROM read_csv('pipe.txt', delim='|', header=true, all_varchar=true, ignore_errors=true);

order_id, customer, total, column3
('1001', 'Acme Ltd', '120.50', None)
('1002', 'Beta ', ' Corp', '89.90')
('TRAILER', '2', None, None)

Three problems are visible. The trailing pipe produced a fourth column, which the reader named column3 because the header has nothing after the last pipe. Order 1002 has Beta | Corp split into two fields, so the customer is Beta , the total column holds Corp and the real total slid into the phantom column. And the trailer row is parsed as a data row with TRAILER in the key column. Each of these is quiet. Loaded into typed columns, the second would fail on a type error, and in an all-text staging table it would load wrong.

Trap one: the trailing delimiter

A line that ends with | is common in files written by programs that emit each field followed by a delimiter, instead of putting delimiters between fields. By the strict rule, the line has a final empty field. Readers differ: some drop it, some keep it as an empty column and some raise an error for a header with fewer names than fields.

Decide once what the spec means and handle it explicitly. If every line, header included, ends with a pipe, drop the last empty column after reading. In DuckDB, select the columns you want by name and ignore the extra one; in Python, row[:-1] after checking that the last element is empty. Add the check, because a producer that stops writing the trailing pipe next quarter changes the field count, and you want an error and not a silent shift. A cheap guard is to compare the field count of every line with the header's, and fail on any mismatch (checking row and column counts in a CSV).

Trap two: a pipe inside a value

With no quoting, a value that contains the delimiter makes the line one field too long. The sample's Beta | Corp is a visible case; in real data, free-text fields such as notes, product descriptions and company names are the places it hides, and most lines are fine, which is why it survives testing.

Find them by counting. A line with more fields than the header is a candidate:

awk -F'|' 'NR > 1 { n[NF]++ } END { for (k in n) print k, n[k] }' pipe.txt

The output of that on a real file is a histogram of field counts, and any bucket other than the expected one is a list of lines to read. If the pipe is in a known column, you can sometimes repair the line: with a layout where the middle field is text and the last two are fixed-format numbers, rejoin the extra pieces into the text field. This is fragile, and I do it only when the producer cannot be fixed, writes the repair as a separate, tested step and logs every repaired line. The better outcome is to get the producer to escape or replace pipes, and to say so in the spec.

Trap three: headers and trailers

Legacy formats often carry a header record (a file title, a date, a sender ID) and a trailer record (a record count and a control total). They share the pipe delimiter and do not share the schema. The sample's TRAILER|2|| claims two records, and two data lines precede it, which is the check: parse the trailer separately, verify that the count matches the number of data rows and compare any control total with the sum of the amounts. A mismatch means a truncated or doubled file, and it is the cheapest integrity check these formats offer.

Recognize special records by a marker in the first field, not by position, since files can be concatenated. Strip them before type conversion, and keep them: the trailer belongs in the ledger of the import (automating CSV imports into a database). Some formats also have several record types in one file, an H header, many D details and a T trailer, each with its own layout. That is a multi-schema file and needs a reader per type, selected by the first field.

Code pages and line endings

Old systems often write EBCDIC, the IBM mainframe character set, or a legacy Windows page. Open an EBCDIC file as UTF-8 and the result is nonsense, with the pipe itself showing as a different character, because | is a different byte in EBCDIC. Python decodes it with encoding="cp037" (US) or cp500 (international), and the sender can tell you which. A Windows code page file looks fine until the first accented name; read it with cp1252 and convert to UTF-8 once (converting a CSV from Latin 1 or Windows 1252 to UTF8).

Line endings can also be fixed-width records with no line break at all, a stream of 80-byte or 133-byte records that you cut yourself, which is a fixed width file with a delimiter inside. And a CRLF file read on Linux leaves a \r on the last field of every line, which breaks joins on it (line endings in CSV files).

Load as text, convert later

The safe order of work is the one used in the other posts in this series. Read the whole file as text with a fixed delimiter. Check the field count on every line. Separate header and trailer records. Verify the trailer's count and totals. Then convert types column by column, with explicit formats and a place to put the failures. DuckDB's all_varchar=true, pandas' dtype=str and a plain csv.reader(f, delimiter="|") all give you that first step, and the second step is yours.

When the pipe is not really a delimiter

Occasionally a pipe file is something else. If the lines do not have a consistent number of pipes, and the pipes appear to align in columns, the file may be a fixed width report with pipes drawn as borders, in which case the border pipes are part of the layout and not field separators (fixed width files and converting them to CSV). If some lines have 3 pipes and some 12, the file has several record types. And if a pipe appears in nearly every field, the producer is quoting with pipes or using them in a nested structure, and no generic reader will do. Read the specification, or ask for a sample of every record type, before writing code. The DuckDB options used above are described in its CSV overview, and the Python codecs module lists the legacy code pages in its standard encodings table.