What is a CSV File?Open Standard

📖 What is a CSV File?

A CSV file is a simple way to store a table as plain text. The name stands for Comma-Separated Values, and that is exactly what it is, rows of information with a comma placed between each value to mark where one column ends and the next begins.

Picture a spreadsheet with names in one column and email addresses in the next. Saved as a CSV, each person becomes a single line of text, with a comma separating their name from their email. The first line often holds the column titles, a header row that labels what each column contains, and every line after it is one row of data. There is nothing else in the file, no colours, no fonts, no formulas, just the raw values and the commas that keep them apart. That plainness is the whole point.

Because a CSV is only text, almost every program that deals with data can read it, which is why it has become the common language for moving information between systems. A CSV exported from one application can be opened in another without any special software, and spreadsheet programs like Microsoft Excel and Google Sheets turn those comma-separated lines back into a neat grid of cells. The format is old and wonderfully stable, with its modern rules written down in a short technical standard called RFC 4180, and it remains one of the most widely used ways to hand data from one place to another.

⚡ Quick Facts

🧱 How a CSV is Structured

The layout of a CSV file is refreshingly easy to follow once you have seen it written out, because there are really only two rules at work, one for rows and one for columns.

Each line of the file is one row of the table. Within a line, commas separate the values, and each gap between commas is one cell. The first line is usually a header that names the columns, though it is optional, and a well-formed file gives every row the same number of values so the columns line up. Reading a raw CSV, you simply count the commas to see where each column falls. Here is a small example of what a CSV actually looks like inside, with a header row followed by two rows of data.

name,email,city       <- header row (column names)
Ada Lovelace,ada@example.com,London
Alan Turing,alan@example.com,Manchester

Opened in a spreadsheet, that same file appears as three tidy columns named name, email and city, with each person on their own row. The spreadsheet has simply used the commas as instructions for where to split each line into cells.

🔤 What About Commas Inside Values?

A fair question springs to mind as soon as you understand the format. If commas separate the values, what happens when a value itself contains a comma, like a city written as "London, England"? The format has a neat answer.

When a value contains a comma, it is wrapped in double quotes, and everything inside those quotes is treated as a single value even though a comma sits within it. So a row might read Jane Smith followed by a comma and then "Berlin, Germany" in quotes, and a reader knows the second value is the whole quoted phrase, not two separate columns. The same trick handles values that contain a line break. There is one more wrinkle, which is what to do when a value contains a double quote of its own. The rule is to double it up, writing two quote marks where one is meant, so the reader can tell the difference between a quote that is part of the data and a quote that marks the edge of a value. These quoting rules are the part of CSV that trips people up most often, and they are exactly what the RFC 4180 standard pins down so that different programs agree on how to read the same file.

📐 Delimiters and Variations

Despite the name pointing squarely at the comma, not every file that people call a CSV actually uses one, and knowing this saves a lot of confusion when a file does not open the way you expect.

The comma is the official separator, the one the RFC 4180 standard specifies and the one most files use. In practice, though, other characters turn up doing the same job. Semicolons are common in places where the comma is used as a decimal point, since a file full of numbers would otherwise be full of ambiguous commas. Tabs are also popular, and a tab-separated file is often given its own name of TSV and sometimes the .tsv extension, though it works on the same idea. Pipes and spaces appear too. Strictly speaking these are different formats, but they are so close in spirit that the CSV label gets stretched to cover them, and many programs let you choose which separator to use when opening or saving a file. If a CSV opens as a single jumbled column, a mismatched separator is usually the reason, and picking the right one when you import the file puts everything back into place.

👁️ How to Open a CSV File

Opening a CSV is easy precisely because it is plain text, so you have a range of choices depending on whether you want to read the raw data or work with it as a table.

The most common way is a spreadsheet program. Microsoft Excel, Google Sheets, Apple Numbers and the free LibreOffice Calc all open a CSV and lay it out as a grid of cells, ready to sort, filter and chart. A basic editor like Windows Notepad or the Mac TextEdit app will open one too, revealing the raw comma-separated lines just as they sit on disk, which helps for a fast look or for hunting down a formatting slip. If you just want to see the contents without installing anything, an in-browser CSV viewer like the one Univik hosts shows the data as a clean table right in a tab. One small caution with Excel is that it sometimes reformats values on opening, turning a long number into scientific notation or trimming a leading zero from a code, so for sensitive data the import dialog, where you can set each column's type, is safer than a plain double-click.

🧮 Open in Excel Without Breaking Your Data

One of the most common frustrations with CSV files is opening one in Excel only to find the data quietly mangled, and knowing why it happens makes it easy to avoid.

The trouble is that when you double-click a CSV, Excel gets no information about what the file is, so it guesses, and its guesses are often wrong in ways that damage the data. A long account number turns into scientific notation because Excel treats it as a number and only keeps about fifteen digits of precision. A ZIP code or product code like 01234 loses its leading zero, since Excel sees a number and drops the zero as unnecessary. A value like 1-1 gets read as a date and becomes the first of January. None of this is a fault in the CSV, it is Excel deciding for you.

The reliable fix is to import the file rather than open it, which lets you tell Excel exactly how to read each part. In current versions you go to the Data tab, choose Get Data and then From Text or CSV, sometimes shown as a single From Text/CSV button, and pick your file. Excel shows a preview where you can confirm the delimiter and, most importantly, set any column that holds codes, IDs or long numbers to the Text type, which stops the reformatting before it can happen. This route uses a tool called Power Query, the modern replacement for the older Text Import Wizard. It takes a few more clicks than a double-click, but it is the only way to bring a CSV into Excel with every value intact. Worth knowing too, Google Sheets does not apply the same automatic conversions, so importing there and downloading the result is another way to keep your data safe.

🌐 Encoding and Special Characters

If a CSV opens with names like José or Müller turned into strings of odd symbols, the cause is an encoding mismatch, a common snag once data travels between countries and systems.

Encoding is the scheme that decides how characters are stored as bytes, and because a CSV is only text it carries no built-in label saying which scheme was used. Most modern files are saved as UTF-8, the encoding that covers virtually every language and symbol. Trouble appears when a program assumes a different scheme, at which point accented and non-English characters come out as garbled nonsense, a problem often called mojibake. The cure is to make the encoding explicit. When importing into Excel, the preview has a File Origin setting where you can choose UTF-8 so the characters come through correctly. There is one twist worth knowing. When Excel saves a file as CSV UTF-8, it adds a small invisible marker at the very start called a byte order mark, or BOM, and while this helps Excel it can confuse other programs that expect plain UTF-8, showing up as stray characters before the first value. For files headed to another system rather than back into Excel, saving as UTF-8 without that marker is usually the safer choice.

🔄 Converting CSV and Excel

By far the most common thing people want to do with a CSV is move it to or from Excel, so it is worth being clear on what that conversion actually involves and why you would do it in each direction.

Going from CSV to Excel turns the raw data into a proper workbook, an XLSX file, where you can add formulas, formatting, charts and multiple sheets, none of which a CSV can hold on its own. You do this by opening the CSV in Excel and saving it as an Excel workbook, or with a web-based CSV converter such as the one Univik runs that hands back a ready-made XLSX. Going the other way, from Excel to CSV, strips all that richness back out to leave just the plain values, which is exactly what you need when another system, a database or a web application asks for a simple data file it can import. The key thing to remember is that a CSV keeps only the data itself, so saving an Excel workbook as CSV will drop its formulas, styling and extra sheets, keeping just the values from the active sheet. That is not a fault, it is the trade every time you swap a rich format for a simple, universal one.

🌍 Why CSV is Everywhere

For a format this basic, CSV shows up in a surprising number of places, and the reason it has outlasted flashier alternatives comes down to a few plain strengths.

The first is that everything can read it. Because a CSV is nothing but text and commas, any programming language, database, spreadsheet or analytics tool can take one in without special handling, which makes it the safe choice whenever data has to pass between systems that know nothing about each other. The second is that it is small and quick, carrying only the values with none of the overhead that richer formats add, so it moves large tables efficiently. The third is human readability, since you can open a CSV and understand it at a glance, unlike a binary file that means nothing without the right program. Those qualities are why a CSV is what you get when you export contacts from an email service, download a bank statement, pull a report from a shop's sales system or feed a table into a data analysis tool. It is not glamorous, but it is dependable, and that dependability is precisely why it endures.

❓ Frequently Asked Questions

📝 Summary