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.

On a Mac without Excel, Numbers opens a CSV directly — drag the file onto the Numbers icon in the Dock or in Applications. When a semicolon-separated export arrives as a single column, the fix is in the table itself: select it, open the Table tab in the Format sidebar, and click Adjust Import Settings. The Delimited tab lets you pick the separator, a text qualifier for quoted fields, and the text encoding, then rebuild the table with Update Table (Apple Support). Apple's guide carries one condition worth knowing before you start tidying: you "can't adjust import settings after you edit the imported table." Check the columns first, edit second.

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. The leading-zero case is the same failure in reverse: the spreadsheet guesses a type the file never declared, and a zip code becomes a number. Both are worth fixing before anything else, because how to clean data in Excel step by step only works on columns that already hold the type you think they do — and the routes for converting values Excel stored as text cover the direction this table's last two rows describe.

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 — or, if the file is already open in a workbook, the concatenate formulas in Excel do the same join before export — 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.

Can a CSV file contain a virus?

The file cannot. The program that opens it can be made to do something you did not ask for, which is a different problem with the same consequences.

A CSV holds no macros, no scripts, and no embedded objects — there is nowhere in the format to put them. An .xlsm workbook has a macro container; a CSV has line breaks and commas. On its own it is inert.

The real risk has a name: CSV injection, also called formula injection. OWASP documents it as the case where a cell's contents are interpreted as a formula by the spreadsheet that opens the file rather than as text. Their mitigation is stated as a rule for whoever generates the file — "ensure that no cells begin with any of the following characters: Equals to (=), Plus (+), Minus (-), At (@)" [6]. A cell starting with one of those is a formula as far as Excel and Sheets are concerned, and it runs when the file opens.

What that buys an attacker is worth being precise about, because the usual retellings overstate it. OWASP lists exfiltrating the contents of the spreadsheet or other open spreadsheets, and hijacking the machine — but by exploiting a vulnerability in the spreadsheet software, or by exploiting the user's tendency to click through the security warning [6]. Modern Excel does warn before running an external-data or command formula from a file it does not trust. The attack leans on that dialog being dismissed.

This matters most for a file you did not produce: a form export, a supplier's price list, a CSV a customer emailed you. Two habits cover it.

• Look at the text before the spreadsheet does. A CSV opened in Notepad, TextEdit, or any code editor shows its exact contents with nothing evaluated. This is a property of the format rather than a trick — the whole file is readable characters.
• Import rather than double-click. The Data > From Text/CSV path described above lets you set a column to Text in the preview, so its values are never parsed as formulas in the first place — the full import-wizard walkthrough covers where that dialog moved in recent versions. It is the same habit that protects your leading zeros, for the same underlying reason: type inference is the thing doing the damage.

If you are the one generating CSVs for other people, the mitigation belongs at the export end, not theirs — strip or prefix any leading =, +, -, or @ before the file is written.

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.

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.

CSV vs Excel: choose the format for the next task

Use CSV for transferring a flat table when the destination requests it. Use a workbook when formulas, charts or multiple tabs are part of the deliverable. Neither extension guarantees clean data. A CSV can contain strings that a spreadsheet interprets as formulas; treat untrusted values carefully.

Converting an existing workbook to CSV exports a single sheet's values and discards workbook structure. Keep the original workbook if you will need formulas or other sheets again. Converting CSV to XLSX afterwards cannot recover features or digits already lost. Check leading zeros, long identifiers, decimal separators, dates and accented characters before and after conversion.

File size depends on the data and compression; CSV is not always smaller than XLSX. Choose by what must survive the exchange rather than assuming the smaller file is the safer one. Sigmera's CSV to Excel converter and Excel to CSV converter provide the two conversion directions; review the output against the original.

Import a CSV into Google Sheets without changing identifiers

1. Keep an untouched copy of the CSV. Open a spreadsheet and choose File > Import > Upload.
2. Select the file and choose the import location deliberately. A new sheet avoids replacing an existing table.
3. Select the delimiter that matches the file rather than assuming detection is correct. Check that quoted commas do not create extra columns.
4. Turn off Convert text to numbers, dates, and formulas when preserving identifiers is the priority. Otherwise a leading-zero postcode or a code resembling a date can change during import.
5. Inspect phone numbers, postcodes, long IDs, date-like strings and accented characters. Convert only genuine numeric measures when you need calculations; identifiers should remain text.

A clean import is also the moment to check for repeated records, because an export that was appended to more than once arrives with the same rows twice. How to remove duplicates in Google Sheets compares the Data cleanup tool, UNIQUE and a COUNTIF highlight, and says which one deletes rows and which one only shows them. If the file opened in Excel instead, the Remove Duplicates button and COUNTIF method in Excel cover the same ground, with a note on which step is permanent.

For data published at a public URL, =IMPORTDATA("https://example.com/data.csv") loads CSV or TSV into Sheets. It does not provide the same identifier-protection settings as the file-import dialog. Do not publish a private customer export to make the formula work, and do not treat a refreshed remote source as a fixed historical copy.

To export, choose File > Download > Comma-separated values (.csv, current sheet). Export other tabs separately if needed; formulas and workbook styling do not travel with the CSV. Open the downloaded file in a text editor or the receiving importer to check the delimiter, quoting and preserved identifiers. Saving a file again cannot restore leading zeros or digits already changed on import.

Sheets has spreadsheet-wide cell limits, so a wide file can hit capacity before a tall, narrow one. Reduce unneeded columns or choose a workflow that supports the full dataset rather than assuming a partial import is complete. Google Sheets processes an uploaded file in Google's service; it is a different privacy choice from local browser processing.