CSV delimiters: comma, semicolon, tab or pipe, and how to change them

A delimiter is the character that separates one field from the next on each line. RFC 4180 uses commas and wraps a field in double quotes when it contains a comma, a double quote or a line break, writing a quote inside a quoted field as "".

Many .csv files use another character. Excel on Windows writes CSV with the Windows Region list separator, which is usually a semicolon where the decimal symbol is a comma, so a price like 12,50 stays one field. Other tools write tab- or pipe-separated text and still call it .csv.

• If a spreadsheet puts each whole line in column A, it split the file on the wrong character.
• Open the file in a text editor and look at the header row: the character between every column name is the delimiter.
• Watch the quotes: in "Smith, John";42;Paris the comma sits inside a quoted field, so the delimiter is the semicolon.
• Or drop the file on rows.page, which detects comma, semicolon, tab or pipe and the header row, and shows the columns split in a grid.

rows.page

1. Drop the file on rows.page, or click Paste data and paste the text. .csv and .txt files open as CSV, .tsv and .tab files as TSV.
2. Check the grid and the Columns panel to confirm the header row and the column types.
3. Open Export and pick CSV.

You get a comma-delimited UTF-8 file with a header row and no BOM, and fields that contain commas are wrapped in double quotes. Export writes the current view, so filters, sorting, hidden columns or a SQL query apply, and cells that start with =, +, -, @, a tab or a carriage return get a leading apostrophe so spreadsheets don't run them as formulas.

rows.page has no delimiter, encoding or BOM setting, so it can't write semicolon or pipe files. DuckDB-WASM reads the file in your browser, and nothing is uploaded unless you press Share.

DuckDB

For files over the 1.5 GB browser limit, or inside a script, use DuckDB:

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

Add decimal_separator = ',' inside read_csv to turn 12,50 into the number 12.5. To write semicolons instead, swap the characters: delim = ',' in read_csv and DELIMITER ';' at the end.

Open a file that uses another delimiter

Instead of double-clicking the file, go to Data > Get Data > From File > From Text/CSV, pick the file, and choose the delimiter before you load it. Excel then splits the columns the way the file was written.

Save with another delimiter

1. Go to File > Options > Advanced.
2. Untick Use system separators.
3. Set the decimal separator. With a comma as the decimal separator, Excel saves CSV files with semicolons.

This changes Excel's settings, not the Windows Region settings, so tick Use system separators again when you're done.

Replacing every ; with , in a text editor or with sed works on a table of plain integers, but it breaks any field that contains a comma. Take this semicolon row, where the name has a comma and the price uses a decimal comma:

104;Widget, small;12,50

Find-and-replace gives 104,Widget, small,12,50, which is five fields instead of three. A CSV parser reads the three fields first and quotes the ones that contain commas, so DuckDB and rows.page write 104,"Widget, small","12,50".