What Is a CSV File? Format Rules and How to Open One
What is a CSV file? A plain text table with one row per line and commas between values. Learn the quoting rules, how to open one, and the Excel pitfalls.
Updated
A CSV file (comma-separated values) is a plain text file that stores a table: each line is one row, and commas separate the values in that row. It has no formatting, formulas or data types, which is why nearly every spreadsheet, database and programming language can read and write it. The file extension is .csv and the registered media type is text/csv.
What a CSV file looks like
Open a CSV in a text editor and you see something like this, one record per line:
id,name,city,joined1,Ada Lovelace,London,2024-03-012,"Hopper, Grace",New York,2024-04-153,"Linus ""Tux"" T.",Helsinki,2024-05-20
The first line is usually a header row naming the columns. Nothing in the file marks it as a header, so the program reading it has to be told, or has to guess. Every value is text. 2024-03-01 is only a date if the reader decides to treat it as one, and 1 is only a number for the same reason.
The rules: RFC 4180
For decades CSV had no specification, only habits. RFC 4180, published in 2005, wrote down the most common dialect. It is informational rather than a strict standard, but it is what most tools aim for:
- Each record is on its own line, ended by a line break (the RFC says CRLF; most readers also accept a bare LF).
- The last record may or may not end with a line break.
- An optional header line can come first, with the same number of fields as the rest of the file.
- Fields are separated by commas, and every record should have the same number of fields. Spaces are part of the field, not padding to be trimmed.
- Any field may be wrapped in double quotes.
- A field containing a comma, a double quote or a line break must be wrapped in double quotes.
- A double quote inside a quoted field is written twice.
Rules 6 and 7 explain the sample above. "Hopper, Grace" is one field because the comma sits inside quotes. "Linus ""Tux"" T." is read as Linus "Tux" T. because each doubled quote stands for a single literal quote. A quoted field can even span several lines, which is why counting lines in a file does not always give you the row count.
Delimiters other than commas
"CSV" is used loosely for any delimited text file. The common variants:
| Delimiter | Usual extension | Where you meet it |
|---|---|---|
| Comma | .csv | The default almost everywhere |
| Semicolon | .csv | Excel exports in locales that use a comma as the decimal mark |
| Tab | .tsv or .tab | Spreadsheet clipboard data, bioinformatics, database dumps |
| Pipe | .psv or .txt | Legacy data feeds and mainframe exports |
The semicolon variant exists because of numbers like 3,14. If the comma is your decimal separator, using it between fields as well would force quotes around every decimal, so Excel in those regions switches to semicolons.
A TSV file (tab-separated values) swaps the comma for a tab character. Tabs almost never appear inside real values, so TSV usually needs no quoting at all, which makes it easy to split with simple scripts. To switch between the two, the CSV to TSV Converter converts in both directions, detects which delimiter a pasted block uses, and keeps quoted fields with embedded commas intact.
How to open a CSV file
- Spreadsheet apps: Excel, Google Sheets, Apple Numbers and LibreOffice Calc all open CSV directly. In Google Sheets use File, Import and upload the file.
- Text editors: Notepad, TextEdit, VS Code or any other editor shows the raw text. This is the right choice when you need to see exactly what is in the file, including quotes and stray delimiters.
- Code: Python has the built-in
csvmodule andpandas.read_csv, R hasread.csv, and most databases have a bulk loader such as PostgreSQL'sCOPY.
Use a real CSV parser in code. Splitting each line on commas works until the first quoted field arrives, then silently shifts every column after it.
Excel pitfalls
Double-clicking a CSV into Excel is convenient, and it is also where most CSV damage happens. Excel guesses a type for every cell and rewrites the value to match:
- Leading zeros vanish. ZIP code
02134becomes2134and product code000451becomes451. - Long numbers lose digits. Excel keeps 15 significant digits, so a 16-digit card or tracking number has its last digit replaced by zero and is shown in scientific notation.
- Text turns into dates.
1-2becomes a date in January or February depending on your locale, and gene names such asSEPT2andMARCH1were converted so often that the gene naming committee renamed several of them in 2020. - Accented characters break. Excel may assume a legacy encoding unless a UTF-8 file starts with a byte order mark, so
Joséshows up asJosé. - Everything lands in one column when the file's delimiter does not match your regional list separator.
- Rows are capped. A worksheet holds 1,048,576 rows; anything beyond that is not loaded.
The fix for most of these is to import instead of opening: Data, From Text/CSV, then set the delimiter and encoding and mark sensitive columns as Text. If you only wanted to look at the data, do not save on the way out, because saving writes the converted values back into the file.
A CSV has no types. Whatever opens the file decides what is a number, a date or text, and a wrong guess followed by a save is permanent.
CSV, JSON and when to use which
CSV is ideal for flat, tabular data: exports from a database table, analytics reports, contact lists, bank statements.
It is a poor fit for nested data. An order with several line items, each with its own options, does not fit a grid without repeating or flattening something. That is where JSON is the better choice; what a JSON file is covers that format.
Moving between the two is routine. The JSON to CSV Converter turns a JSON array of objects into CSV, using the union of all keys as the header row and quoting values that contain commas or quotes. Nested objects and arrays are written into a single cell as JSON text rather than spread across columns. In the other direction it reads the first CSV row as the keys and returns an array of objects, with every value as a string, which is faithful to what the file actually contains.
Try it
Paste a CSV export into the JSON to CSV Converter and switch to CSV to JSON to see each row as an object, or paste API output to get a spreadsheet-ready table. If the data came from a spreadsheet clipboard or needs tabs for a database loader, the CSV to TSV Converter swaps the delimiter and handles the quoting for you.