RFC 4180 is the document most software treats as “the CSV format.” Its rules are short: a field that contains a comma, a double quote or a line break should be wrapped in double quotes, and a double quote inside a quoted field is written twice (""). When an export breaks those rules, or a program reads them differently, you get shifted columns, half-rows and stray quote marks. CSVPrune from Automata Labs opens the file on your PC, lets you check every column and invalid value in a grid, and exports clean, properly quoted UTF-8 CSV, with nothing uploaded.
Open the CSV, see what broke, and export a properly quoted file again.
Get it from Microsoft
What is RFC 4180?
RFC 4180, published in October 2005, is titled “Common Format and MIME Type for Comma-Separated Values (CSV) Files.” Two details matter before anything else:
- It is Informational, not a strict standard. The RFC itself says that CSV “has never been formally documented” and that it documents “the format that seems to be followed by most implementations.”
- It registered the
text/csvMIME type, with two optional parameters:charsetandheader.
So RFC 4180 is less a law than a written-down consensus. That is why it is the best reference you have, and also why real files drift from it.
What are the RFC 4180 rules?
Here are the seven rules from section 2, in plain language:
| # | Rule | Plain meaning |
|---|---|---|
| 1 | Each record is on its own line, ending with CRLF | One row per line, with Windows-style line endings |
| 2 | The last record may or may not end with a line break | A missing final newline is fine |
| 3 | An optional header line can come first, in the same format | The first row may be column names |
| 4 | Fields are separated by commas, each line should have the same number of fields, spaces are part of the field, and no trailing comma | Same column count on every row, and a, b keeps the space |
| 5 | Fields may or may not be enclosed in double quotes | Quoting is optional when it is not needed |
| 6 | Fields containing line breaks, double quotes or commas should be enclosed in double quotes | Quote anything that would otherwise confuse a reader |
| 7 | A double quote inside a quoted field is escaped by another double quote | She said "hi" becomes "She said ""hi""" |
The RFC also notes, in rule 5, that “some programs, such as Microsoft Excel, do not use double quotes at all.” In other words, even the RFC acknowledges that real programs differ.
How do you put a comma inside a CSV field?
Wrap the whole field in double quotes. Without quotes, a reader splits on every comma:
name,city
Smith, John,TorontoThat second row has three fields, not two, so “John” lands in the city column. Quoted correctly:
name,city
"Smith, John",TorontoThe same applies to addresses, product descriptions and anything a person typed into a free-text box. Those are the columns that usually break an export.
How do you escape a double quote in CSV?
Double it, inside a quoted field. RFC 4180 rule 7 shows the example "aaa","b""bb","ccc", where the middle field reads b"bb.
| You want the field to read | Write it as |
|---|---|
12" pipe | "12"" pipe" |
She said "yes" | "She said ""yes""" |
"quoted" | """quoted""" |
A backslash escape (\") is common in programming languages, but it is not part of RFC 4180. A reader following the RFC will treat the backslash as a normal character and then trip over the quote.
Can a CSV field contain a line break?
Yes, if the field is quoted. Rule 6 says fields containing line breaks should be enclosed in double quotes, and the RFC’s example shows a field that starts on one line and finishes on the next. This is how notes, comments and multi-line addresses survive in CSV.
It is also the single most common reason a file “has more rows than it should.” A tool that reads line by line, instead of following the quotes, sees one record as two or three. If row counts do not match between two programs, look for quoted line breaks first.
Why do CSV exports still break?
Because the RFC describes what “most implementations” do, and some do not. The usual causes:
- Unquoted commas or quotes. A quick script joins values with commas and never adds quotes.
- Backslash escaping. A developer escapes quotes as
\"instead of"". - A different delimiter. Many exports in Europe use semicolons because the comma is the decimal separator. See fix CSV encoding and delimiter problems.
- A different encoding. RFC 4180 says common usage is US-ASCII, with other character sets allowed through the
charsetparameter. Real files arrive as UTF-8, UTF-16 or Latin-1. - Line endings. The RFC uses CRLF, and its own encoding note warns that “some implementations may use other values,” such as a bare LF.
- Inconsistent column counts. Rule 4 says each line should have the same number of fields. A trailing comma or a missing value without a placeholder breaks that.
- Spreadsheet edits. Opening a CSV in a spreadsheet and saving it again can change quoting, dates and leading zeros. See why Excel removes leading zeros from CSV.
How do I check whether a CSV follows the rules?
Work through this in order:
- Count columns per row. Every record should match the header. A row with one extra field usually has an unquoted comma.
- Search for stray quotes. A field that starts with a quote but never closes it will swallow the rest of the file.
- Look for
\". That is a sign of backslash escaping. - Compare row counts between the tool that exported the file and the tool reading it. A difference often points to quoted line breaks.
- Confirm the delimiter and encoding before you blame the data.
- Re-export with consistent quoting once the data is right.
Doing that by eye in Notepad is slow on a big file, and Excel is not a neutral viewer: Microsoft lists a worksheet limit of 1,048,576 rows, and it reinterprets values as it opens them.
How does CSVPrune help with RFC 4180 problems?
CSVPrune is a local CSV workbench for Windows. According to its product page and Microsoft Store listing, it can:
- Auto-detect or override the delimiter (comma, semicolon, tab, or pipe) and encoding (UTF-8, UTF-16, Latin-1), so the file decodes the way it was written.
- Show the whole file in a virtualized grid that handles 100,000+ rows, with search, literal or regex filters and type-aware sorting.
- Profile every column for types, blanks, distinct counts and invalid cells, with a one-click “show only invalid rows” filter.
- Keep leading zeros and long IDs as text instead of converting them.
- Find and replace with plain text or regex, scoped to one column, which is how you fix a run of
\"or stray characters. - Export clean, properly quoted UTF-8 CSV, JSON or XLSX, with an optional audited export that records every changed cell.
- Save the steps as a recipe and replay them on next week’s export.
- Run with no network code at all, so customer data never leaves the PC.
An honest limit: if a file is badly malformed, for example a quote that opens and never closes, no parser can know where the writer meant the field to end. In that case, fix the source export or the one broken row, then let CSVPrune handle the rest. The quickest test is your own worst file.
Check columns, spot invalid rows, and save a clean, quoted CSV in minutes.
Get it from Microsoft
Which CSV tools follow RFC 4180 by default?
Behavior varies by program and version, so test with a small sample file containing a comma, a doubled quote and a line break in one field. If all three survive a round trip, the tool follows the rules you care about. Related guides: open a large CSV file on Windows, remove duplicate rows from a CSV and mask personal data in a CSV before sharing.
Frequently asked questions
Is RFC 4180 an official CSV standard?
It is an Informational RFC that documents common practice and registers the text/csv MIME type. It is the closest thing CSV has to a standard, and most tools aim to follow it.
How do I escape a double quote in a CSV file?
Put the field in double quotes and write each quote inside it twice. 5" disk becomes "5"" disk".
Does every CSV field need quotes?
No. Rule 5 makes quoting optional. Fields that contain commas, double quotes or line breaks should be quoted.
Why does my CSV have more rows in one program than another?
Usually because a quoted field contains a line break. A reader that follows the quotes sees one row, and a line-by-line reader sees several.
Does RFC 4180 require UTF-8?
No. It says common usage is US-ASCII and allows other character sets through the charset parameter. In practice UTF-8 is the safest choice for exchange.
Try CSVPrune
Get CSVPrune from the Microsoft Store. It is a one-time purchase with no subscription, and the Store listing shows the current price.
Turn a broken export into a clean, properly quoted CSV, on your own PC.
Get it from Microsoft
Microsoft, Excel and Windows are trademarks of the Microsoft group of companies. CSVPrune is not affiliated with or endorsed by Microsoft or the IETF.
Last updated 20 October 2026. Published by Automata Labs, a software company in Canada.