📄 CSV Fundamentals · 8 min read
Quotes in CSV Files: When You Need Them and How to Escape
Quotation marks in a CSV are not decoration and they are not an error. They exist for three reasons: a field contains a comma, a field contains a double quote, or a field contains a line break. If none of those is true, the field does not need quotes and most exporters leave it bare.
The rules come from RFC 4180, the document that registered the text/csv MIME type. It is an informational memo rather than a standard, and it says so: there is no formal specification in existence, which allows for a wide variety of interpretations of CSV files. The section that follows is the format most implementations settled on, which is why it is worth reading once rather than guessing per file.
Quick answer
Quote a field only when it contains a comma, a quote character or a line break. Escape a quote character inside a quoted field by doubling it, so the text says "hi" is written as "says ""hi""". And treat any bulk operation that removes quote characters from a whole file as unsafe, because the quotes are what hold a comma inside one column.
The four rules behind the quotes
RFC 4180 section 2 defines the format in a short numbered list. Four entries decide almost every quoting question you will run into.
- Records are lines, fields are comma-separated. Each record sits on its own line, and fields inside it are separated by commas. The last field in a record must not be followed by a comma.
- Quotes are optional, until they are not. The RFC says each field may or may not be enclosed in double quotes, and adds a detail worth remembering: some programs, such as Microsoft Excel, do not use double quotes at all. Where fields are not enclosed in quotes, quote characters may not appear inside them.
- Three characters force quoting. Fields containing line breaks, double quotes and commas should be enclosed in double-quotes.
- An inner quote is doubled.If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote. The RFC's example is
"aaa","b""bb","ccc", which reads back as the valueb"bb.
One more line from the same list catches people out: spaces are considered part of a field and should not be ignored. A trailing space after a comma is real data.
Which fields need quotes
| Field value | Written as | Why |
|---|---|---|
| Smith, John | "Smith, John" | Contains a comma, so an unquoted version would look like two fields |
| Says "hi" | "Says ""hi""" | Contains quote characters, which are doubled rather than backslashed |
| Line one Line two | "Line one Line two" | Contains a line break, so the record spans more than one line in the file |
| Plain text | Plain text | Nothing forces quoting, and most writers leave it bare |
| "already quoted" | """already quoted""" | The quote characters are part of the value, so each one is doubled |
What we measured with a real parser
Rather than describe the failure, we wrote a three-column row where the middle field was the name Smith, John and the third field contained the text says "hi", then had a standard CSV writer produce the file and a naive string split attempt to read it back. This is the raw output, quotes and all:
1,"Smith, John","says ""hi"""Splitting that line on commas returns four fields instead of three, and the two parts of the name land in separate columns. Removing every quote character first and then splitting returns four fields as well, this time with the comma left as its own separator, so the quote-free version is not a fix. Then we put a line break inside one field, which the same writer quoted as expected. That file contains three records and four lines, and a reader that treats every line as a record reports one row too many.
Parsing the same bytes with a real CSV parser returns the original three fields, with the comma and the quote characters intact. The file was never broken. The reading method was.
Why quotes appear that you did not type
Exporters quote more than you expect, for two defensible reasons. Quote-minimal writers add quotes exactly where the RFC demands them, which keeps files readable in a text editor. Quote-all writers enclose every field including the header, which some importers and connector pipelines handle more predictably. Both are valid CSV, and both parse to the same records.
So a file arriving with quotes around every value is not a sign of a bad export. It is a sign of which convention the tool chose, and the useful question is whether the system you are feeding expects one of them.
Three mistakes worth avoiding
Removing all quotes in a text editor. As the measurement above shows, this splits the rows that needed the quotes, which are usually the rows with names, addresses and product descriptions. If the file has no comma, quote or line break inside any field, removal changes nothing and is harmless, which is exactly what makes the habit dangerous in a mixed file.
Running find and replace on quote characters. Replacing every quote with nothing can leave a field half-quoted, which most parsers then read as one giant field followed by an unexpected column. Our guide to find and replace in CSV covers the safer way to target a specific value instead of a character.
Mixing escaping conventions. CSV doubles a quote. JSON wrote \" and so does the output of many APIs and template engines. If you see a backslash before a quote inside a file with a .csv extension, you are probably looking at JSON with a misleading name, and a CSV parser will keep the backslash as literal text.
How to check a file without breaking it
- Parse it with a real parser and compare the field count per row against the header. One row returning more fields than the header tells you the row number to inspect.
- Look at that row in a text editor rather than a spreadsheet. Spreadsheets re-interpret what they read, which is the reason for the neighbour problem of a CSV arriving in a single column.
- If the file is malformed because of an unescaped character rather than the quotes themselves, the special characters guide and the delimiter walkthrough cover the cleaning steps that keep the structure intact.
- When you need to see the parsed columns quickly, paste a few rows into the CSV analyzer, or re-write the file with the delimiter converter if the separator between fields is the actual problem.
Frequently asked questions
When does a CSV field need double quotes?
A field needs quotes when it contains one of three things: a comma, a double quote, or a line break. RFC 4180 puts it as follows: fields containing line breaks, double quotes and commas should be enclosed in double-quotes. A field with none of those three can be written without quotes, and most exporters leave it that way.
How do you put a double quote inside a quoted CSV field?
Double it. RFC 4180 states that if double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote. The RFC's own example is aaa, b followed by two quote characters and the letter bb, then ccc, which parses back to the original text with single quotes.
Can I just remove all the quotes from a CSV file?
Not safely. Quotes are what hold a comma inside a single field. We measured it with a three-column row where the second field was the text Smith, John. Splitting that row on commas produced four fields instead of three, and stripping the quote characters first produced four fields as well, with the name cut in half. Removing quotes only works on files where no field contains a comma, a quote or a line break.
Why does Excel add quotation marks when it saves a CSV?
Excel quotes a field when it has to. RFC 4180 notes that some programs, such as Microsoft Excel, do not use double quotes at all for ordinary fields, which is exactly why quoted fields stand out when you open the file in a text editor. A quote usually means the value contains a comma, a quote character or a line break, not that something went wrong.
Is there a difference between quote-all and quote-minimal output?
Yes, and neither is wrong. Quote-minimal writers add quotes only where the RFC requires them, which keeps the file readable. Quote-all writers enclose every field, including the header, which some legacy importers handle more predictably. Both parse to the same records, so choose based on the system you are feeding rather than on file size.
Why do I see backslash quotes in my CSV?
That is a different escaping convention leaking in. CSV escapes a quote by doubling it, so a quote inside a quoted field looks like two quote characters in a row. A backslash before a quote belongs to JSON and similar formats. A file containing backslash-quote is usually JSON output that was renamed to .csv, and a CSV parser will read those backslashes as literal text.
Are spaces around a value ignored in CSV?
No. RFC 4180 says spaces are considered part of a field and should not be ignored. That is why a trailing space after a comma survives into the parsed value, and why trimming whitespace is a separate cleaning step rather than something a parser does for you.
What is the safest way to check whether a CSV is quoted correctly?
Parse it with a real CSV parser and count the columns per row. Every parser, including the csv module in Python's standard library, returns the same field count for a well-formed file. If one row returns more fields than the header, that row contains an unescaped comma or a stray quote, and the row number the parser reports is where to look.
Tools mentioned in this guide
Reading a CSV correctly is a parsing problem, and these three shorten the loop between suspecting a file and knowing what is in it:
- OpenCode Go — the measurement in this article is a short script that writes a file with a standard CSV writer and reads it back twice. That kind of check is a minute of work in a terminal. Try OpenCode Go
- Stack AI — when messy files arrive on a schedule, a workflow can validate each one on arrival and alert you on the row that fails instead of letting it into the report. Try Stack AI
- Softr — if the same records get re-uploaded every month, putting them in a no-code app once means the file stops being the only copy that understands the quoting rules. Try Softr
Some links above are affiliate links — if you buy through them we may earn a commission at no extra cost to you. OpenCode Go uses our referral link; the other two currently point to each vendor's official page until our tracking links are approved.
Check the Rows Before They Land in a Report
Paste a slice of the file into the analyzer and read the columns the way a parser sees them, not the way a spreadsheet guessed them.
Quoting rules in this article are quoted from RFC 4180, section 2, retrieved from the RFC Editor on the day this page was published. The parser output was produced the same day with the csv module in Python's standard library.
Related reading
Data Cleaning — other guides that pair well with this one.
Browse all guides in the NoCodeCSV blog.