How to convert a pipe-delimited file to CSV

A pipe-delimited file, saved as .psv, .txt or .csv, separates columns with the vertical bar | instead of a comma or tab. Database and mainframe exports use it because their text often contains commas, which would otherwise need quoting in almost every field:

id|customer_name|address|balance
101|Smith, John|14 Oak St, Apt 2B|450.00
102|Doe, Jane|The "Gables" Main Rd|1200.50

Spreadsheet apps expect commas, so double-clicking the file usually leaves each whole line in column A, and many systems don't know which app opens a .psv file at all.

1. Drag the file onto rows.page, or click Paste data, paste the text and click Open. A dragged .psv file opens fine, but the file picker doesn't list .psv, so rename it to .txt if you'd rather pick it.
2. Check the grid: DuckDB's sniffer detects comma, semicolon, tab and pipe delimiters and the header row by itself.
3. Click Export and choose CSV.

DuckDB-WASM reads the file inside your browser tab and nothing is uploaded unless you click Share. The CSV is comma-delimited UTF-8 without a BOM, with a header row, and every field containing a comma or a quote is wrapped in double quotes. Text cells starting with =, +, -, @, a tab or a carriage return get a leading apostrophe so spreadsheets don't run them as formulas.

A file over 1.5 GB loads partially, with the note "Loaded the first N rows: the file is X, more than the browser can hold." There is no manual delimiter setting if the sniffer guesses wrong on a malformed file, and no encoding or BOM option on export.

1. Open a blank workbook and go to Data > From Text/CSV, then pick your file.
2. In the preview, set Delimiter to Custom and type |.
3. To keep leading zeros and long ID numbers intact, turn off data type detection in the last dropdown so every column stays Text.
4. Check that the columns split cleanly, then click Load.
5. Click File > Save As and choose CSV UTF-8 (Comma delimited).

Excel quotes fields that contain commas when it writes the CSV. With type detection on, it turns digit strings into numbers, which drops leading zeros and keeps only 15 significant digits.

For files over 1.5 GB or batch jobs, run the DuckDB CLI, the same engine rows.page runs in your browser:

COPY (SELECT * FROM read_csv('in.txt', delim = '|')) TO 'out.csv' (HEADER);

delim = '|' stops read_csv from guessing the separator, and (HEADER) writes the column names on the first line. DuckDB quotes output values that contain a comma, a quote or a newline. It also rewrites numbers from their detected type, so 450.00 becomes 450.0; add all_varchar = true to keep every value as written.

Replacing every | with , in a text editor or with sed only works if no value contains a comma, a quote or a pipe. Take row 101 from the example:

101|Smith, John|14 Oak St, Apt 2B|450.00

After the replace, it reads:

101,Smith, John,14 Oak St, Apt 2B,450.00

The line now has 5 commas, which any CSV parser reads as 6 columns instead of 4. A real CSV writer splits on | first, sees the comma in Smith, John and writes "Smith, John" in double quotes so the comma stays inside the cell.

Before loading the CSV into another system, open it in rows.page and compare it with the original:

• The Columns panel shows the number of columns and each column's type, share of empty values, and min and max for numbers and dates.
• The distinct count on your key column ("N values", or "~N values" when estimated) shows whether broken line breaks split records into extra rows.
• The filter -id:* finds rows with an empty id, and balance:>0 checks that a number column parsed as numbers.
• This SQL query summarizes every column at once:

FROM (SUMMARIZE data)

If you filtered out bad rows or hid columns along the way, click Export > CSV again. A filtered view exports every matching row, and a SQL result up to 1,000,000 rows.