Updated 2026-10-19

CSV Delimiter Change: Rewrite the File Without Breaking Your Data

A CSV delimiter change is either a reading setting or a rewrite, and the two are not interchangeable. If you only need one program to see columns instead of a single line, change the import setting and leave the bytes alone. If the file has to arrive comma-delimited at the other end — an upload form, a warehouse loader, a partner's import script — you have to parse every record with the old delimiter and write it back out with the new one. Doing that with find-and-replace is what silently adds columns, because the old delimiter is a perfectly ordinary character inside unquoted values.

Quick answer

Change the setting when you only need to read the file. Change the file itself only when something downstream requires a specific delimiter — and when you do, run it through a parser that quotes any value containing the new delimiter, then verify that every row still has the same field count as the header.

Three layers that all get called "delimiter"

Most delimiter confusion comes from mixing up these three, so it is worth naming them before touching anything:

If the file already opens as one column in Excel, you are at layer one and want the import fix, not a rewrite — that is covered in how to change a CSV delimiter in Excel and in CSV opening as one column. The rest of this page is about layer three.

Why find-and-replace corrupts the file

Here is a two-column semicolon file. The city value contains a comma and is not quoted, which is legal: in a semicolon file the comma is just a character.

city;note
New York, NY;"paid; in full"

Replace every semicolon with a comma and the text looks almost right — but the first data row now has three fields while the header has two. Nothing warns you. The extra column appears in the spreadsheet as a new blank header, and the value "NY" has quietly become its own cell:

city,note
New York, NY,"paid, in full"

The correct rewrite parses both fields first, then re-emits them with quoting decided by the new delimiter. The value that contains a comma gets wrapped in double quotes, exactly as RFC 4180 section 2 requires for fields containing the separator:

city,note
"New York, NY",paid; in full

Note what else changed: the note no longer needs quotes, because a semicolon is now an ordinary character. Correct quoting is a property of the output dialect, not something you carry over from the input. That is why the operation has to be parse-then-write.

The safe rewrite in Python

Ten lines, standard library only, and it handles quoted fields, embedded line breaks and doubled quotes because the csv module does the parsing:

import csv

with open("in.csv", newline="") as src, open("out.csv", "w", newline="") as dst:
    reader = csv.reader(src, delimiter=";")
    writer = csv.writer(dst, delimiter=",", quoting=csv.QUOTE_MINIMAL)
    for row in reader:
        writer.writerow(row)

Two details are not optional. First, newline=""on both files: Python's documentation states that without it, newlines embedded inside quoted fields are not interpreted correctly and an extra carriage return is added on platforms that write CRLF. Second, the writer's default line terminator is CRLF, which matches RFC 4180's record separator; the reader accepts either CR or LF, so a Unix-exported source file is read fine and comes out with Windows line endings. If a downstream system is strict about LF, pass lineterminator="\n" deliberately rather than by accident.

QUOTE_MINIMAL is the right policy for most files: it quotes only the fields that contain the delimiter, the quote character, or a line break. Use QUOTE_ALL when a downstream loader is badly written and only copes with fully quoted input.

If the source delimiter is unknown, let the module guess from a sample instead of hard-coding it. csv.Sniffer().sniff() reads a chunk and returns the dialect; the documentation notes that when several delimiters fit equally well it prefers the ones in its documented preference order, so confirm the result on your own file rather than trusting it blindly.

import csv

with open("in.csv", newline="") as f:
    sample = f.read(65536)
    dialect = csv.Sniffer().sniff(sample)
    print("delimiter:", repr(dialect.delimiter))

Doing it in Excel, Sheets and LibreOffice

Excel

Import with the delimiter, then export with the delimiter you want. Data > From Text/CSV, pick the file, choose Semicolon (or Tab, or Pipe) in the preview, then Load. Save with File > Save As and choose CSV (Comma delimited) or CSV UTF-8.

Microsoft's own documentation says that when you save a workbook as a .csv file the default list separator is a comma, and that you can change it through Windows Region settings — a change that affects every program on the machine, not just Excel. That is exactly why double-clicking a semicolon file produces one column: Excel applies the regional separator instead of detecting the file's. It also explains why an exported file can surprise a colleague whose Windows region differs from yours. Excel's import and export ceiling is 1,048,576 rows by 16,384 columns, so anything larger never reaches the export step intact.

Google Sheets

File > Import, upload the file, and set Separator type to Custom with the character the file uses. Then File > Download > Comma-separated valuesto get a comma-delimited copy. Sheets allows up to 20 million cells or 100 MB for a spreadsheet created in or converted to Sheets, per Google Drive's file limits, and cells over 50,000 characters are removed when converting from Excel — both are real ceilings on this route for wide text data.

LibreOffice Calc

File > Save As, choose Text CSV, tick Edit filter settings, then set the field separator and text delimiter in the Filter Options dialog. LibreOffice shows this dialog on export precisely because the delimiter is a property of the output, which is the mental model worth keeping.

The terminal, carefully

sed 's/;/,/g' is the same trap as find-and-replace in a text editor — it has no idea what a field is. It is safe only when you have already verified that no value anywhere in the file contains the target character. For anything you did not generate yourself, use a parser: the Python snippet above, or a CSV-aware CLI. If the file is tab-separated and headed for a comma-delimited loader, the conversion is the same operation described in convert TSV to CSV.

Verify before you send it

Silent column shifts are the failure mode of this whole task, and one check catches almost all of them:

import csv
from collections import Counter

with open("out.csv", newline="") as f:
    rows = list(csv.reader(f))

widths = Counter(len(r) for r in rows)
print("row count:", len(rows), "field-count distribution:", dict(widths))

One number in that distribution means the file is structurally consistent. Two or more means a value contained the new delimiter and was not quoted, or the source file already had ragged rows before you touched it. A quick profile of the output — column names, types, missing values — in the free CSV analyzer will also surface a header that shifted by one.

Two more checks worth doing on files that came from elsewhere: confirm the encoding did not change (a leading EF BB BF byte order mark can appear if you re-saved as UTF-8 — see CSV file encoding), and confirm numeric-looking IDs kept their leading zeros, since a spreadsheet round trip strips them.

When the answer is "do not change the file"

If the only consumer is a spreadsheet on your desk, change the import setting and keep the original — a rewrite is risk with no benefit. If the data is full of commas and the downstream system accepts tabs, converting to TSV removes the quoting problem entirely; the trade-offs are in CSV vs TSV. And if you are choosing a delimiter for a file you control end to end, pick one that does not occur in your values and document it, rather than relying on the reader to guess.

For a one-off conversion in the browser, the free delimiter converter does the parse-and-re-emit for you and never uploads the file. If you are still deciding which character a file actually uses, CSV delimiter options covers how to tell comma, semicolon, tab and pipe apart.

Frequently asked questions

Sources

• IETF, RFC 4180: Common Format and MIME Type for CSV Files — CRLF record separators, commas between fields, quoting of fields containing commas, quotes or line breaks, and the doubled-quote escape.
• Python 3 documentation, csv module — newline="" requirement, QUOTE_MINIMAL, default lineterminator, Sniffer preference order.
• Microsoft Support, "Import or export text (.txt or .csv) files" — default comma list separator on save, the Windows Region setting that changes it globally, 1,048,576 rows by 16,384 columns.
• Google Drive Help, "Files you can store in Google Drive" — 20 million cells or 100 MB per spreadsheet, cells over 50,000 characters removed when converting from Excel.
• Our own check, 2026-10-19, Python 3.13.5: the semicolon example rewritten by naive replace yields 3 fields on the data row against 2 in the header, while parse-then-write yields 2 fields with the comma-containing value quoted.

Related reading