GameParticles
Developer

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,joined
  • 1,Ada Lovelace,London,2024-03-01
  • 2,"Hopper, Grace",New York,2024-04-15
  • 3,"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:

  1. Each record is on its own line, ended by a line break (the RFC says CRLF; most readers also accept a bare LF).
  2. The last record may or may not end with a line break.
  3. An optional header line can come first, with the same number of fields as the rest of the file.
  4. 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.
  5. Any field may be wrapped in double quotes.
  6. A field containing a comma, a double quote or a line break must be wrapped in double quotes.
  7. 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:

DelimiterUsual extensionWhere you meet it
Comma.csvThe default almost everywhere
Semicolon.csvExcel exports in locales that use a comma as the decimal mark
Tab.tsv or .tabSpreadsheet clipboard data, bioinformatics, database dumps
Pipe.psv or .txtLegacy 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 csv module and pandas.read_csv, R has read.csv, and most databases have a bulk loader such as PostgreSQL's COPY.

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 02134 becomes 2134 and product code 000451 becomes 451.
  • 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-2 becomes a date in January or February depending on your locale, and gene names such as SEPT2 and MARCH1 were 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 as José.
  • 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.

Tools in this guide

Frequently asked questions

How do I open a CSV file?

Double-click it to open it in Excel, Numbers or LibreOffice Calc, or upload it to Google Sheets with File, Import. Because a CSV is plain text, any text editor such as Notepad, TextEdit or VS Code also opens it and shows the raw commas and quotes.

What is a TSV file?

A TSV file is the same idea as a CSV with a tab character between values instead of a comma. Because tabs rarely appear inside real data, TSV files usually need no quoting. Spreadsheets open them the same way, and copying cells from a spreadsheet produces TSV on the clipboard.

Why does Excel put my whole CSV in one column?

Excel splits on the list separator from your regional settings. In many European locales that is a semicolon, so a comma-separated file lands in column A. Import it through Data, From Text/CSV and choose comma as the delimiter instead of double-clicking the file.

What is the difference between CSV and XLSX?

A CSV is plain text holding one table of values. An XLSX file is a zipped set of XML documents that stores multiple sheets, formulas, cell types, formatting and charts. CSV is readable by almost every program; XLSX keeps everything a spreadsheet can do.

More developer guides

JSON Formatter
Developer · Format, validate and minify JSON with syntax highlighting.
↵
Base64 Encode / Decode
Developer · Encode text to Base64 or decode Base64 back to plain text.
↵
URL Encoder / Decoder
Developer · Encode or decode URL components and query strings.
↵
Hash Generator
Developer · Generate MD5, SHA-1, SHA-256 and SHA-512 hashes.
↵
UUID Generator
Developer · Generate UUIDs v4 (random) in bulk with one click.
↵
Regex Tester
Developer · Test regular expressions against text with live match highlighting.
↵
Word Counter
Text · Count words, characters, sentences, paragraphs and reading time.
↵
Case Converter
Text · Convert text between UPPER, lower, Title, camelCase, snake_case and more.
↵
List Sorter
Text · Sort, reverse, deduplicate and shuffle lists of text.
↵
Markdown Preview
Text · Write Markdown and see a live rendered preview side by side.
↵
Percentage Calculator
Numbers · Calculate percentages, increases, decreases and what % one number is of another.
↵
Color Converter
Colors · Convert colors between HEX, RGB, HSL and HSV instantly.
↵
Snake
Arcade · Eat food, grow longer, don't hit the walls. A timeless classic.
↵
2048
Puzzle · Slide tiles and combine them to reach the legendary 2048 tile.
↵
Word Grid
Puzzle · Guess the hidden five letter word in six tries using color clues.
↵
Sudoku
Puzzle · Fill the 9x9 grid with logic. Four difficulties from easy to expert.
↵
Minesweeper
Strategy · Use number clues to clear the minefield. Classic logic, three levels.
↵
Breakout
Arcade · Smash every brick with paddle and ball. Power-ups, combos, endless levels.
↵
Typing Practice
Casual · Build your WPM and accuracy with timed drills from 30 seconds to 5 minutes. Find your weak keys.
↵
TypeRunner
Arcade · Multiplayer typing race. Race friends by link, beat computer runners, or chase your own ghost.
↵
Typing Blocks
Arcade · Type each word before its wooden block lands. Miss, and the pile climbs toward the line.
↵
Bubble Shooter
Arcade · Aim, bank shots off the walls, and pop groups of three or more bubbles.
↵
Road Rush
Arcade · Race seven rivals through traffic and punch your way to the front.
↵