What Is a CSV File? Format, Uses, and Pitfalls

A CSV file is a plain-text file that stores a table — one record per line, values separated by commas. Unlike an Excel workbook, it carries no formulas, formatting, or sheets: only the values themselves. That simplicity is why almost every CRM, bank, and analytics tool can read one, and why so many break on the edge cases.

Below: what is a CSV file made of, the rules the format follows, how to open and save one without damaging it, and the failures worth knowing before your next import.

What a CSV file actually is

A CSV is text, not a document. Open contacts.csv in Notepad or TextEdit and you see the entire file:

first_name,last_name,email,country
Ada,Lovelace,[email protected],UK
Grace,Hopper,[email protected],US

Four things are happening. The first line is a header naming each column. Every line after it is one record. Commas mark the boundary between fields. And the position of a value decides its meaning — the third field is the email because the header said so, not because anything in the file tags it as an email.

That is the whole format. There is no cell colour, no bold, no formula, no second tab, no cell type. A number and a piece of text look identical in the file; whatever program reads it decides how to interpret 01924 — as a number that loses its leading zero, or as text that keeps it.

The trade is deliberate. A CSV is smaller than the equivalent workbook, diffs cleanly in version control, streams line by line, and can be produced by any language in a few lines of code. It is the lowest common denominator that two systems that agree on nothing else can still exchange.

How RFC 4180 defines the format

CSV was a convention for decades before anyone wrote it down; RFC 4180, published in October 2005, is the closest thing to a specification.

The document is Informational rather than a binding standard, which matters — it describes common practice instead of mandating it. Its core rules are short:

• Each record sits on its own line, delimited by a line break (CRLF). The last record may omit the trailing break.
• An optional header line may come first, in the same format as a record, with the same number of fields.
• Fields are separated by commas. Spaces are part of a field's content, not a delimiter.
• A field containing a comma, a double quote, or a line break must be enclosed in double quotes.
• Inside a quoted field, a literal double quote is escaped by doubling it: "b""bb" means b"bb.

RFC 4180 also registers the MIME type text/csv, with an optional header parameter set to present or absent, and a charset parameter that defaults to US-ASCII (RFC 4180).

That last default is the source of a common headache. Most real-world CSVs today are UTF-8, and a tool assuming a different encoding turns Zoë into Zoë. The file is not corrupt; the reader guessed wrong.

Because the RFC is descriptive, plenty of valid-looking files bend it: tab or semicolon separators, no header, quoted-everything, or LF-only line endings from Unix systems. Robust parsers handle all of these. Fragile ones do not.

How to open and save a CSV without breaking it

The safest way to open a CSV is the import path, not a double-click.

In Excel, use Data > From Text/CSV rather than File > Open. The import step lets you choose the delimiter with a live preview and set the data type per column, which is where you stop phone numbers and postcodes from being silently converted to numbers. Saving back out is the riskier direction: Excel exports only the active worksheet, and warns that the sheet may contain features unsupported by text formats (Microsoft Support). A three-sheet workbook saved as CSV becomes one sheet of values.

Google Sheets takes the same approach through File > Import, which accepts .csv, .txt, .tsv, .xlsx, .ods and more. It offers separator detection — automatic, tab, comma, or a custom character — and its automatic mode can recognise fixed-width files and semicolon separators (Google Docs Editors Help). It also asks whether to create a new spreadsheet, replace the current sheet, or append rows, so an import cannot quietly overwrite work.

If you need the file as a workbook to preserve types and multiple tabs, run it through the CSV to Excel converter instead of pasting it in. Going the other way — pulling a clean single-sheet CSV out of a workbook for an import — is what converting XLSX back to CSV is for.

Where CSV files go wrong

Most CSV pain comes from four ambiguities the format never resolved.

The delimiter case is the most frequent. In locales where the comma is the decimal separator, Excel writes the semicolon as its list separator, following the regional list-separator setting rather than the file extension. A colleague in Berlin exports a CSV, you open it in London, and everything lands in column A.

Duplicates are the other reliable problem, and they are not a format issue at all — the format has no concept of a key or a unique constraint, so nothing stops the same contact appearing four times. That is a cleaning job, and removing duplicate rows before an import saves you from doing it inside the CRM afterwards. Our guide to data cleaning walks through the rest of the checklist: trimming whitespace, normalising case, standardising phone numbers.

Why a CSV can be cleaned in your browser

Because a CSV is plain text with no proprietary container, every capable environment can parse it — including the browser tab you already have open.

This is not a marketing claim; it follows from the format. Reading an .xlsx file means unzipping an archive and walking several XML parts. Reading a CSV means splitting text on line breaks and commas while respecting the quoting rules above. JavaScript running on your own machine handles that at the speed of your CPU, on files of any size your memory allows.

The practical consequence: a spreadsheet full of customer emails and phone numbers never has to travel to someone else's server to get deduplicated, reformatted, or converted. Sigmera's browser-based cleaning tools load the file into the page, transform it locally, and hand back a download. Nothing is uploaded, which removes an entire category of GDPR questions about processors and data transfers — there is no processor, because there is no transfer.

Spreadsheets keep their place for exploration and formulas; the Sigmera and Excel comparison is candid about where each one wins. But for the narrow job of taking a messy export and making it importable, plain text is an advantage rather than a limitation. The format that makes a CSV fragile — no types, no structure, no container — is the same one that makes it something you can fix without handing it to anyone.