Data & spreadsheets · CSV Cleaner
How CSV Delimiter Detection Works: Commas, Semicolons, Tabs and Pipes
· How it works
csv data-cleaning file-formats
A CSV file never says which character separates its fields, so software has to guess. This post explains how delimiter sniffing works, why it fails on tricky files, and how to make the delimiter explicit.
A file that opens as one long column — why the delimiter is not stored anywhere in a CSV
A data analyst opens a .csv export and sees one long field on every line. The extension does not tell a reader which separator was used, and the file usually carries no delimiter declaration. An application assuming commas will read a semicolon-separated header such as name;city;notes as a single column. Before changing values, ask which character actually divides the fields and whether the importer has a way to override its guess.
The four supported candidates: comma, semicolon, tab and pipe
ToolAcre considers comma, semicolon, tab and pipe as candidates. A semicolon is common when commas are already used as decimal separators, tabs avoid punctuation collisions in simple exports, and pipes sometimes separate log or database dumps. A space is not among the tool’s automatic choices: names and free-text cells often contain spaces. Do not add a candidate merely because it occurs frequently in sample text; a separator must divide rows into consistent fields.
How sniffing works — counting candidate characters per line and preferring the one that gives a consistent field count across rows
The implementation strips a leading UTF-8 byte-order mark and parses a sample of up to ten rows with each candidate. It records the widths of those rows and rejects any candidate that never yields at least two columns. A candidate scores higher when it produces more columns consistently across rows; inconsistent widths are penalised twice in the score. Crucially, parsing observes quoted fields, so a comma inside a quoted address does not count as a field boundary. This differs from naive character counting and is why a messy address column need not defeat detection.
Where sniffing fails — free-text columns full of commas, single-column files and quoted fields that hide the true separator
No guess is infallible. A very short file has little evidence, a genuinely one-column CSV has no candidate that splits into two, and malformed quoting can make several candidate parses look ragged. A free-text column filled with separators may compete with the real delimiter when quoting is broken. Mixed separators across rows are not one clean CSV dialect. The tool reports parsing warnings and exposes manual delimiter selection; a user who knows the upstream export convention can override the inferred choice rather than pretending the heuristic is a specification.
Worked example — a semicolon-separated export with commas inside addresses, showing why a naive count picks the wrong character
Try a small sample with header name;city;note and a row Ada;Paris;"Meeting at 10, near the station". Counting raw commas might wrongly favour comma because the note contains punctuation, especially over several such rows. The semicolon parser returns three fields on both rows; the comma parser does not provide the same rectangular structure. Confirm the preview shows name, city and note as separate columns before trimming, deduplicating or exporting. If one source row has an unclosed quote, inspect that row instead of silently discarding it to improve the score.
Choosing the output delimiter — matching the separator to the program and locale that will read the file next
The input separator and output separator are different decisions. You might import a European semicolon file but export RFC-style comma-separated text for a program that expects commas. A correct writer quotes fields containing the output delimiter, embedded quotes or line breaks. Decide whether your recipient needs a UTF-8 BOM before downloading; some spreadsheet applications use one to recognise encoding while many parsers want the file without it. Check the downloaded header in the destination program, not only the browser preview.
What this does not cover — files that mix delimiters between rows, and fixed-width exports that use no delimiter at all
Detection cannot repair a file that changes separator halfway through, nor can it infer columns in a fixed-width export where no delimiter exists. It does not know whether a quoted comma was meant as part of a street address or a data-entry mistake: it follows syntax, not meaning. The CSV Cleaner will surface ragged rows and preserve their values rather than guessing how to delete a customer’s data. Use its preview and warnings to diagnose a source-system problem before performing irreversible cleaning steps.
Make the delimiter explicit instead of hoping — how the ToolAcre CSV Cleaner's delimiter repair produces a file with one consistent separator
A delimiter guess is an evidence-based parse of a few rows, not a promise written inside the CSV file. CSV Cleaner implements it locally in your browser, lets you choose a different separator and writes one consistent output dialect. If an import wizard guesses differently next week, record the output convention beside the file so the next reader does not have to rediscover it.