How to Open a Pipe-Delimiter CSV in Excel (Pipe-Separated Files)

To open a pipe-delimiter CSV in Excel, import it with Data > From Text/CSV and set the delimiter to Other with | as the character — or use Data > Text to Columns with Other set to |. A pipe-delimited file separates values with the vertical bar character (|), usually because the data itself contains commas, semicolons, and tabs, so none of those are safe as separators. Open it with the wrong delimiter and every row collapses into one column. Tell Excel to split on the pipe, and the table appears.

Pipe-delimited files are common in database exports, ETL pipelines, log files, and enterprise systems. (For an ordinary comma-separated file, the free CSV to Excel converter converts it in one step.) The pipe is a deliberate choice: it almost never appears inside ordinary text, so a field can contain commas, quotes, and even tabs without breaking the row. The import is straightforward once you know where Excel hides the "Other" delimiter option — but pipes inside values and a couple of Excel quirks can still cause trouble, and this guide covers both.

Why Pipe Exists as a Delimiter

Every delimiter is a trade-off. Commas break when a value contains a comma ("Smith, John"). Semicolons break when a value contains a semicolon. Tabs break when a value contains a tab. The pipe | is rare in human-written text, so systems that export messy free-text fields — addresses, descriptions, log messages — often choose it precisely because it will not collide with the data.

The downside is that Excel has no one-click "pipe" preset. Comma, Tab, Semicolon, and Space each get a checkbox in the import dialogs; pipe hides under "Other", where you type the character yourself. People who miss that option conclude the file is broken. It is not — it is just one checkbox away from a clean table.

Method 1: Data > From Text/CSV (Recommended)

1. Open Excel to a blank workbook. Go to Data > From Text/CSV.
2. Select your file. Pipe-delimited files may carry a .csv, .txt, or .psv extension. If the file picker hides it, switch the filter to "All Files".
3. **Set the delimiter to Other: |.** In the preview dialog, change Delimiter to Other and type | in the character box. The preview should split into proper columns immediately. If it does not, the file may use a different separator — check the raw text (see "Diagnosing the File" below).
4. Check the encoding. Set File Origin to UTF-8 unless accents look garbled. Encoding problems show up as mojibake (é instead of é) — the guide to fixing CSV encoding errors in Excel explains how to fix them.
5. Load and save. Click Load, then save as .xlsx. Saving as .xlsx preserves your fixes; the pipe file remains the raw source.

Method 2: Text to Columns (Fix an Already-Open File)

If you already opened the file and everything landed in column A:

1. Select column A (or the range holding the collapsed data).
2. Go to Data > Text to Columns. Choose Delimited > Next.
3. Untick Tab, Comma, Semicolon, and Space. Tick Other and type | in the box next to it. Click Next.
4. Set column types: Text for code/ID columns with leading zeros, Date with the right DMY/MDY order for date columns, General for the rest. The same column-type rules apply as for semicolon-delimiter CSVs and tab-delimiter (TSV) files.
5. Click Finish. The single column splits into the full table.

Warning: Text to Columns overwrites columns to the right of the selection. If column B already holds data, insert blank columns first or work on a copy of the sheet.

Method 3: Power Query (Best for Repeating Imports)

If you receive a pipe-delimited export every week or every day, set the import up once in Power Query and refresh it instead of repeating the manual steps:

1. Go to Data > Get Data > From File > From Text/CSV.
2. Select the file. In the preview, set Delimiter to Custom and type |.
3. Use the Transform view to set column types, remove junk columns, and rename headers.
4. Click Close & Load. Next time a new export arrives, replace the source file and click Data > Refresh All.

Power Query also handles the two messiest pipe-file problems — inconsistent line endings and a stray header row — more gracefully than the plain import, because each cleaning step is recorded and replayed.

Diagnosing the File Before You Import

When the pipe split does not work, open the file in a plain text editor and look at the raw text. You should see lines like:

1001|Maria Garcia|2026-10-17|1,299.00|Red, large, cotton

The pipes stand out as separators; the comma inside "Red, large, cotton" is part of a value and must stay put. If instead you see commas doing the separating, it is an ordinary CSV. Semicolons mean a European-style CSV. Tabs mean TSV. If you see || (two pipes with nothing between), that is an empty field — legitimate, not an error; the column is blank for that row.

Also count the pipes per row: every data row should have the same number of pipes as the header row. A row with one extra pipe has a pipe inside a value (see below); a row with one fewer may have a missing field or a line break inside a value that split one logical row across two physical lines.

Delimiter Quick Reference

Edge Cases Worth Knowing

A pipe inside a value. Rare, but it happens — a description field containing | splits the row into one column too many. If only a few rows are affected, merge the split cells by hand. If it is systematic, fix it at the source: ask the exporting system to quote fields, escape the pipe, or strip pipes from text fields before export. Do not delete the extra column blindly — it holds the second half of someone's data.

Quoted fields. Some pipe exporters wrap fields in double quotes. Excel handles standard quoting, but a stray quote inside an unquoted field can derail one row. When a single row misbehaves while the rest import cleanly, inspect that row's raw text for a stray quote character.

Trailing delimiter. Some systems end every line with a pipe (...|value|), which Excel reads as an extra empty column at the end. Delete the empty trailing column after import — or, in Power Query, remove the last column as a recorded step so refreshes stay clean.

Saving back to pipe-delimited. Excel has no built-in "Save as pipe-delimited" format. To export pipe-delimited text, save as CSV and replace the commas — but only if no value contains a comma, or the replacement corrupts those values. The reliable route is Power Query or a small script that joins fields with | and quotes fields containing the separator. Keep the .xlsx as the master and export the pipe file as a copy.

Very large files. Pipe files from databases can exceed a worksheet's row limit. Load them to the Data Model via Power Query, or split the file first — the guide to splitting a CSV into multiple Excel sheets by column covers the splitting approach.

FAQ

What is a pipe-delimited file?

A plain-text table where the pipe character | separates the fields in each row — the same idea as a CSV, but with pipes instead of commas. It is common in database exports, ETL pipelines, and log files, because pipes rarely appear inside ordinary text.

Why does my pipe-delimited file open in one column?

Because Excel split on the wrong delimiter. It looked for commas, found none doing the separating, and put each whole row into a single cell. Re-import with Delimiter set to Other: |, or fix the open sheet with Data > Text to Columns > Other: |.

How do I convert a pipe-delimited file to Excel?

Import it with the pipe delimiter (Data > From Text/CSV, Delimiter: Other |), then save as .xlsx. That gives you a real workbook. Do not just rename the file to .xlsx — renaming changes the label, not the format.

How do I save Excel back as pipe-delimited?

Excel has no native pipe export. Save as CSV and convert the separator only if no value contains a comma; otherwise use Power Query or a script that joins fields with | and quotes fields containing the separator. Keep the .xlsx as your master.

What if a value itself contains a pipe character?

The row gains an extra column. Fix the affected rows by merging the split cells by hand, or fix it at the source by quoting, escaping, or stripping pipes in text fields before export. Never delete the extra column without checking what it holds.

Final Checklist

• Imported with Delimiter set to Other: | (not double-clicked and hoped).
• Preview showed proper columns before loading; every row has the same pipe count as the header.
• Encoding set so accents and special characters display correctly.
• Code/ID columns set to Text so leading zeros survive.
• File saved as .xlsx; pipe format exported separately only if another system needs it.

If all five checks pass, your pipe-delimited file is now a clean Excel table — every value in its own column, pipes doing their job as separators and nothing else.