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,ada@example.com,UK
Grace,Hopper,grace@example.com,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. The step-by-step import route, and what a double-click costs you, covers the column types, the encoding setting, and the recovery options once a file has already been opened the wrong way. 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. Which of those options you pick, and whether you leave the type-conversion box ticked, is where postcodes and product codes survive or get rewritten — the full Sheets import walkthrough works through the dialog setting by setting.

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. Which of the two you should be holding in the first place is its own question — CSV or Excel, and when each is the right format works through the trade-off for imports, analysis, and archiving. 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. And when the file is meant for a reader rather than a program — something to print, sign, or attach — rendering the CSV as a PDF table skips the spreadsheet entirely.

How to create a CSV file

There are three routes, and they differ mainly in what they do to your encoding.

From Excel. Use File > Save As and choose a CSV option from the file-type list [4]. One thing to expect: a CSV holds a single table, so Excel saves only the active worksheet. A workbook with four sheets does not become one CSV with four sections — it becomes one CSV, and the other three sheets are silently left behind. Save each sheet separately if you need all of them.

From Google Sheets. File > Download > Comma-separated values (.csv) exports the current sheet, with the same one-sheet-at-a-time behaviour.

From a text editor. Because the format is plain text, a CSV needs no spreadsheet at all. Type a header line, then one record per line with commas between the fields, and save it with a .csv extension. That is the whole specification, and it is the fastest way to build a small lookup table or a test file.

Choose CSV UTF-8, not plain CSV

Excel's Save As list offers both CSV (Comma delimited) and CSV UTF-8 (Comma delimited), and the difference is the one that decides whether names survive the round trip.

CSV UTF-8 writes a byte order mark — a few bytes at the very start of the file. The Unicode Consortium is precise about what that is for: an initial BOM is "only used as a signature — an indication that an otherwise unmarked text file is in UTF-8" [5]. It carries no visible content and says nothing about byte order in UTF-8, which is byte-oriented. It exists purely so a program that opens the file knows which encoding it is reading.

That matters because CSV itself has no header, no metadata, and no field where an encoding could be declared. Without the BOM, Excel guesses from your regional settings — which is precisely how Zoë arrives as Zoë and Müller as Müller. Pick CSV UTF-8 whenever the data holds names, addresses, or anything outside plain ASCII.

The one caveat is that the BOM is not universally welcome. Unicode's own guidance notes that systems expecting specific ASCII characters at the start of a file run into trouble when a BOM is present [5], and some strict importers and command-line parsers treat those bytes as part of the first column header — which is why an import occasionally fails on a field named email that looks identical to email on screen. If an importer rejects a UTF-8 file for no visible reason, the BOM is the first thing to suspect.

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. It is also the cheaper order of operations: a CRM will happily accept a bad file and then make you unpick it, which is why the walkthrough on getting a contact file past HubSpot's importer fixes encoding, phone format, and duplicates before the upload rather than after. Our guide to data cleaning walks through the rest of the checklist: trimming whitespace, normalising case, standardising phone numbers — and because a phone column is the field most often mangled by the type inference described above, rewriting one to the E.164 standard is usually the step that pays for itself first. If the destination is specifically a CRM, the data cleaning checklist a contact file has to pass puts those same steps in the order that avoids rework, and adds the file-level rules the importer applies first.

Field shape is the mirror-image problem. Because the format only stores columns, whether a name lives in one field or two is a decision the exporting system made, not something the CSV enforces — so an importer that wants a single full_name will reject a file that has first_name and last_name. Joining two columns into one fixes that in a pass, and the reverse split is a pass too. The name splitter handles that direction, including the "Last, First" rows a plain comma split gets backwards.

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. That is the whole premise of a browser-based CSV cleaner: the parser runs where the file already is.

The same reasoning covers reading the file, not just fixing it. Because the parse already happens in the tab, you can plot the file straight into a chart — group by a category column, summarize a value column, export a PNG — without an import step or a pivot table in between.

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.