CSV Dialects and Variants You Will Run Into in Practice

Ask three people what a CSV file is and you get three answers, each correct for the tools they use. The first says commas, double quotes for fields with commas, one record per line. The second says whatever Excel writes, which in their country uses semicolons and a decimal comma. The third has a database export with tabs and backslash escapes and the letters \N where a value is missing. All three files are called CSV, all three will open in something, and no single parser setting reads all three correctly. That is the central fact about the format: it is not one format. It is a family of dialects held together by a shared look, and the work of reading CSV safely is the work of recognizing which member of the family is in front of you. This guide lists the settings that define a dialect, catalogs the dialects you will meet, shows how to identify one from a file and how to pin it in code, and says where to stop accepting variants. The foundations are in everything a CSV file does and does not promise you, and the one written standard is explained in what RFC 4180 says about CSV files.

Seven settings make a dialect

Every CSV reader and writer, in any language, is configured by the same short list of decisions. Name them once and every dialect becomes a row in a table.

A reader that gets any one wrong produces output that looks plausible. A wrong delimiter gives one big column, which is obvious. A wrong escape rule gives rows that parse, with a stray backslash inside some fields and a quote swallowed. A wrong null marker gives a text column holding the string \N where the database expected a NULL, and nothing complains until an arithmetic query on it. That gradient, from loud to silent, is why the settings should be explicit.

The catalog

Here are the dialects I see most, with the settings that distinguish them. The columns follow the table above, and "typical source" says where you meet each one.

The table hides detail that matters, so the sections below take the groups in turn.

The standard and Excel

RFC 4180 describes comma-separated records, double quotes around fields containing commas, quotes or line breaks, doubled quotes inside such fields and CRLF line endings. It also admits it documents common practice and is not a specification that producers obey, which is why it uses "should" so often. A parser that handles only the RFC will fail on a good share of the files you meet.

Excel writes the RFC form on a machine whose regional list separator is a comma, and a semicolon-delimited variant on one where it is a semicolon, with a decimal comma in the numbers. The two are the same program and different dialects, which is how a file that opens perfectly for the sender opens as a single column for the receiver (why Excel shows a CSV in one column). Excel also writes a byte order mark in its "CSV UTF-8" format, treats a first line of sep=; as an instruction and evaluates cells that begin with =. Excel CSV versus RFC CSV differences walks through each divergence.

Database dialects

PostgreSQL's COPY has two text dialects. With CSV, it follows the RFC: a configurable delimiter, quote and escape (the escape defaults to the quote, so it doubles), and an unquoted empty field as NULL. Without CSV, it uses the older "text" format: tab-delimited, backslash escapes for tab, newline and backslash itself, and \N as the NULL marker. The two read different files with different results, and mixing them up is a classic cause of invalid input syntax errors (PostgreSQL CSV versus text format in COPY).

MySQL's SELECT ... INTO OUTFILE and LOAD DATA default to the text-like form: tabs between fields, newline at the end of records, backslash as the escape character and \N for NULL, with no quoting unless you ask for FIELDS ENCLOSED BY '"'. The defaults make sense for round trips inside MySQL and are surprising to anyone feeding the file to a CSV library. Setting FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' produces something an RFC parser will read (importing a CSV into MySQL).

SQLite's shell .mode csv writes the RFC form, and .mode tabs the tab form. DuckDB's COPY ... TO defaults to comma, double quote, LF and an empty string for NULL, which is close to the RFC and not identical in line endings.

Tab, pipe and fixed width

A tab-separated file avoids the comma problem because tabs rarely occur inside data, and the IANA registration for text/tab-separated-values forbids them in fields, which means no quoting is defined or needed. It breaks the day a field contains a tab, which free text sometimes does, and then there is no escape (TSV versus CSV and when tabs are the saner choice).

Pipe-delimited files are older. Banks, insurers and mainframe exports use | because it is rare in names and addresses, and they usually do not quote. A value with a pipe in it either breaks the row or has been replaced upstream (pipe delimited files from legacy systems).

Fixed width files have no delimiter at all. Each field occupies a stated range of columns, padded with spaces. They are not CSV, and they arrive under the .csv name often enough that you should know the shape: a header description in a separate document, lines of identical length and no way to find the structure from the file alone (fixed width files and converting them to CSV).

Report-style and decorated files

Some files claim to be CSV and are really reports exported to text: title lines above the header, a header that spans two or three lines, blank separator rows, subtotals in the middle and a footer with a grand total. The structure is visible to a person and invisible to a parser. Parsing multi-line headers in report style CSV and detecting and handling embedded headers repeated mid file cover the two commonest, and handling CSV with comment lines the lines that begin with # and are not data.

Identify a file's dialect

You can read most of the seven settings off the first few lines. The delimiter shows as the character that repeats the same number of times on every line. The quote character shows if some field is wrapped. The escape rule shows if a quote appears inside a wrapped field, doubled or preceded by a backslash. The line terminator needs a hex view: \r\n and \n look identical in an editor. Encoding shows as a BOM, or as garbage where accents should be.

Programs can do this too. DuckDB's sniff_csv returns its conclusion for a file, and running it over five samples is a fast way to learn what a sniffer can and cannot see. The samples were an RFC file with CRLF and doubled quotes, a semicolon file, a tab file, a pipe file and a comma file with backslash-escaped quotes:

SELECT Delimiter, Quote, Escape, NewLineDelimiter, HasHeader FROM sniff_csv('rfc.csv');

rfc.csv     (',', '"', '"', '\r\n', True)
semi.csv    (';', '(empty)', '(empty)', '\n', True)
tab.tsv     ('\t', '(empty)', '(empty)', '\n', True)
pipe.txt    ('|', '(empty)', '(empty)', '\n', True)
bslash.csv  (',', '"', '\\', '\n', True)

The sniffer got every delimiter and line ending right and found the escape in the backslash file, where the quote and the escape differ. In the three files without any quoted field, it reported an empty quote character, which is accurate for that sample and a trap for the next file: a sniffer sees only the lines it samples, and a file whose first thousand lines have no quotes may have quotes on line 40,000. That is why the sniffer is a tool for exploring a file and a poor one for a production load. Letting DuckDB sniff the delimiter shows its options, and telling what delimiter an unknown CSV file uses shows how to do the same by hand.

Python's standard library has a sniffer as well, csv.Sniffer, with less capability, and it guesses only delimiter and quote. Pandas and other tools use their own heuristics. Treat every one of them as a way to find out what to write in your configuration, not as the configuration.

How a wrong guess shows up

Each mismatch has a recognizable symptom, and learning them is faster than reading documentation at the moment something breaks. Match the symptom to the setting and you know where to look.

One wide column with delimiters inside the text. The delimiter is wrong. A comma file read as semicolons, or the reverse, is the commonest case, and it is the loud one. Fix the delimiter and everything lines up.

The right number of columns, but some rows have extra or too few. The quote handling is wrong. Either the file quotes fields and the reader was told it does not, so a comma inside quotes splits the field, or the file has an unescaped quote that the reader treats as the start of a quoted field and swallows lines until the next quote. A tell is that the bad rows cluster around names, addresses and notes, the fields where commas and quotes occur. Counting the fields on every line finds them in one pass (checking row and column counts in a CSV).

Backslashes in the data, or quotes that look doubled. The escape rule is wrong. A file written with \" for an inner quote and read as the RFC form leaves the backslash in the value and ends the quoted field early, and the opposite mismatch makes a field that ends with a backslash eat the quote after it. The symptom is a handful of rows with a stray \ near a quote, which is easy to miss in a large file.

An extra empty line between records, or a carriage return glued to the last field. The line terminator is wrong or mixed. \r\n read as \n leaves a \r at the end of the last column of every row, which makes every value in that column unequal to its clean twin, and joins on it silently return nothing (line endings in CSV files).

Accents turned into two characters, or question marks. The encoding is wrong, or a legacy code page has already replaced characters. See fixing garbled text in CSV files for good.

The first column name looks right and does not match. A byte order mark is attached to it. The string id and the string \ufeffid print the same and compare unequal.

**A text column full of \N, NULL or NA where you expected missing values.** The null marker is wrong. The reader treated a marker as a string, so counts of missing values are zero and averages are over text. Pass the marker explicitly to the reader, and check with a count of the marker string after the load.

The pattern is that the loud failures get fixed and the quiet ones ship. The checks above are cheap, which is the argument for running them on every new feed before trusting it.

One record, five dialects

It helps to see one record written five ways. Take a customer called Smith, J who wrote the note He said "hi" and has no phone number. Under each dialect the same facts look different:

The first and last read as the same data through an RFC parser, apart from the empty string in the last field, which the unix writer quotes and which a reader may then treat as an empty string and not a missing value. The tab forms need no quoting and no doubling, since neither a comma nor a quote is special, and they use \N for the missing phone, which an RFC parser reads as the two characters backslash and N. A pipeline that reads PostgreSQL's text format with a CSV reader loads a phone number of \N into every row with no phone, and the error is invisible until someone calls it.

Numbers, dates and booleans belong to the dialect too

The seven settings stop at the field boundary, and a dialect in practice goes further. The same CSV can write 1234.50 or 1.234,50 or 1,234.50, the date 2026-10-02 or 02/10/2026 or 10/2/26, the boolean true, TRUE, 1, t, yes or Y. None of these is part of the CSV rules, and all of them are part of what makes two files incompatible. They are usually decided by the locale of whoever wrote the file, which is why a regional Excel export changes the decimal separator along with the delimiter.

The practical consequence is that "read it as CSV" is two steps. The first splits text into fields, using the seven settings. The second converts fields into values, using decisions about types and locales. Do them separately in code: read everything as text, then convert column by column with explicit formats, so that a failure names a column and a value. DuckDB's all_varchar=true, pandas' dtype=str and a csv.reader all give you the first step alone. Why a CSV file has no types and what that costs you explains the cost of skipping the split.

Pick formats for what you write, too. ISO 8601 for dates and timestamps, a point for decimals, no thousands separators, and true and false for booleans give a reader nothing to guess (writing dates and booleans so other tools read them).

What the common libraries assume

Defaults differ, and the differences account for many "it worked in my script" reports. A short tour of what the usual tools do when you pass nothing:

• **Python csv** defaults to the excel dialect for reading and writing: comma, double quote, doubled quotes, CRLF on write. Reading with newline="" is required so embedded newlines survive.

• **pandas read_csv** assumes a comma and infers types per column, treats a set of strings (NA, NaN, null, empty and others) as missing and uses the C engine, which is lenient about ragged rows only when told (pandas read_csv and the files it does not like).

• **R readr::read_csv** assumes commas and a point decimal, and read_csv2 assumes semicolons with a decimal comma, which is the regional Excel dialect by name (reading CSV files in R with readr).

• **Go encoding/csv** follows the RFC, requires every record to have the same number of fields unless FieldsPerRecord is changed and rejects a bare quote in an unquoted field unless LazyQuotes is on (reading and writing CSV in Go).

• Java Apache Commons CSV ships named formats, DEFAULT, EXCEL, RFC4180, MYSQL, POSTGRESQL_CSV, POSTGRESQL_TEXT, TDF and others, which is the clearest published catalog of the family (Commons CSV).

• **DuckDB read_csv** sniffs everything it can and lets you override each setting, reporting what it chose with sniff_csv.

If your pipeline touches more than one of them, you have several defaults in play, and a file that crosses a boundary can change meaning without anyone editing it. The simplest defense is to write the full list of settings in each reader's call, even where the default would do.

Pin the dialect in code

The cure for guessing is to say what you expect. In Python, the csv module bundles three named dialects, and printing their attributes shows what each one means:

import csv
for name in ["excel", "excel-tab", "unix"]:
    d = csv.get_dialect(name)
    print(name, repr(d.delimiter), repr(d.quotechar), d.doublequote, repr(d.lineterminator), d.quoting)

excel ',' '"' True '\r\n' 0
excel-tab '\t' '"' True '\r\n' 0
unix ',' '"' True '\n' 1

excel is the RFC form with CRLF, excel-tab is the same with tabs and unix is the same with LF. The last number is the quoting mode: 0 is QUOTE_MINIMAL, which quotes only when needed, and 1 is QUOTE_ALL, which wraps every field in quotes. The unix dialect therefore writes "1","Ada" and not 1,Ada, which surprises people who chose it because the name suggested LF endings alone. Read the Python csv module reference for the arguments, and set them explicitly rather than rely on a named dialect you have not looked at.

In SQL loaders, the same discipline applies. A COPY statement lists FORMAT csv, DELIMITER ',', QUOTE '"', ESCAPE '"', NULL '' in full when the source matters. DuckDB's read_csv takes delim, quote, escape, nullstr, header and encoding, and passing them turns off the sniffer for those settings (DuckDB's CSV reading options). Pandas takes sep, quotechar, escapechar, na_values and encoding. In every tool the full list is longer than people use, and the ones they leave out are the ones that bite.

Describe the dialect where the file lives

A dialect that is written down is a dialect you can check. The W3C's CSV on the Web standard defines a metadata file that carries a dialect description (delimiter, quote character, header row count, line terminators, encoding) plus the column types, next to the CSV (CSVW and describing CSV with metadata). The Frictionless Data standard has a similar descriptor (writing a schema for a CSV). Even a plain text README next to a feed, listing the seven settings and an example row, is far better than nothing, and it is the document you send to the provider when something breaks.

When you produce files for others, publish the dialect on purpose. For a mixed audience, the RFC form in UTF-8 without a BOM is the best default, with a documented variant for Excel users (BOM, and a semicolon if the audience requires it). A header row with plain column names, ISO 8601 dates, a decimal point and an explicit empty value for missing data removes most of the remaining ambiguity.

Converting between dialects without damage

The tempting way to turn a semicolon file into a comma file is a text substitution: sed 's/;/,/g'. It works on the sample you tried and fails on the first field that contains a comma, because that field was not quoted in the source (nothing forced it to be) and is now split in two. It also leaves decimal commas as they were, so a total of 1234,50 becomes a field that a comma reader splits. The only safe conversion parses the source completely with its own dialect and then writes the target completely with its own.

That means two sets of settings in one operation. Read with the source delimiter, quote, escape, encoding and decimal separator. Write with the target delimiter, the quoting rule that protects the new delimiter, the target line terminator and a decimal point. Any tool that exposes both sides does the job: Python's csv.reader and csv.writer, DuckDB's read_csv inside a COPY ... TO, Miller's --icsv --ifs ';' --ocsv, and csvkit's csvformat. What you must check afterwards is the same short list: the row count, the field count per row and a sample of the awkward values (a field with a delimiter in it, a quote, a newline, an accent and a missing value) compared with the source.

Conversions that cross the text/CSV boundary need extra care. PostgreSQL's text format escapes tab, newline and backslash as \t, \n and \\, and turns NULL into \N, so converting it to RFC CSV means undoing the escapes, not only changing the delimiter, and the null marker has to become an empty unquoted field. A reader that skips this step produces files with literal backslash sequences in them, which is the worst kind of corruption: it looks like intended content.

A checklist for producers

If you write CSV that other people will read, the dialect is your responsibility, and a short list of choices keeps readers out of trouble. Use a comma unless the audience needs otherwise, and say so when it does. Quote any field containing the delimiter, a quote or a line break, and double the inner quotes; do not use backslash escapes in a file meant for RFC readers. Write UTF-8, and decide on the byte order mark deliberately: include it for files opened by double-clicking in Excel, leave it out for files read by programs. End lines with LF or CRLF consistently, and never mix them. Put a header row of plain, unique, stable column names on the first line, with no decoration above it.

Write missing values as an empty unquoted field, and say in the documentation whether an empty string and a missing value differ. Write dates as ISO 8601, numbers with a point and no grouping, booleans as true and false. Keep one record per line of logical content, and where a text field may contain line breaks, say so. Put the dialect in a sentence in the feed's documentation, or in a descriptor file, and give an example row with every awkward character in it. A producer who does these eight things gets almost no mail about the file.

Test a reader against the family

If you write a parser, a loader or a validator, test it against files from several dialects. The edge cases belong in a fixture folder: quoted newlines, doubled quotes, a backslash before a quote, CRLF and LF files, a BOM, an empty last field, a trailing comma, a header only, an empty file. A cheat sheet of CSV edge cases to test every parser with lists them with expected parses. The test that earns its keep is a round trip across dialects: write a record with every awkward character through one dialect and read it back through the matching reader, and assert that the fields are identical.

When to stop supporting variants

Every dialect you accept is a promise to maintain a reading of it. A service that takes uploads from the public will receive all of them, and a policy of "we read anything" ends with a parser full of special cases that each broke the previous one. A better policy is to accept a small named set, say RFC with UTF-8, Excel regional with a declared delimiter, and tab-separated, validate hard and reject the rest with a message that says which setting was wrong.

For feeds between systems you control, accept exactly one dialect and write it down. A semicolon file or a backslash escape is a bug in the producer, and fixing the producer costs less than teaching every consumer about it. Support extra dialects only when the producer cannot change, and then isolate the conversion at the edge, a small step that turns the odd file into the standard one and fails loudly on anything it does not recognize. The reference for the standard dialect is RFC 4180, the registered media types are at IANA for CSV and IANA for TSV, and the PostgreSQL behavior is in the COPY documentation.