What Is a CSV File?

A CSV file is a plain text file that stores a table. One line per row, commas between the values. That is genuinely the whole format — you can open one in Notepad and read it with your own eyes, which is exactly why CSV has outlived nearly every proprietary spreadsheet format ever shipped.

The trouble starts the moment you double-click one. Excel opens CSV files without asking you anything, and in the process it will quietly rewrite your data: postal code 01234 becomes 1234, an order ID becomes 1.23457E+18, and José becomes José. Nothing warns you. The file on disk was fine — the import broke it.

This guide covers what is actually inside a CSV, how to open one on any platform, and the specific import settings that stop Excel from mangling it. If you just need the file converted cleanly right now, FileNaut's CSV to Excel converter writes every value as text, so leading zeros and long IDs survive intact — and it runs in your browser, so the file never leaves your device.

CSV stands for comma-separated values. The .csv extension tells your operating system which program to suggest, but it has no effect on the contents — a CSV is just text, and any program that can read text can read it.

Here is a complete, valid CSV file:

name,role,city
Ada Lovelace,Engineer,London
Grace Hopper,Admiral,New York

Three lines, three columns. The first line is the header row — labels, not data. Every line after it is a record. There are no fonts, no colours, no formulas, no multiple sheets and no cell widths, because CSV has nowhere to store any of that. It holds values and nothing else.

That poverty is the point. Because there is almost nothing to disagree about, a CSV exported from a 1998 accounting package still imports into a 2026 database. It is the format nearly every system falls back on when it has to talk to a system it knows nothing about — which is why your bank, your CRM, your payroll provider and your email platform all offer "Export to CSV".

CSV was used for decades before anyone wrote it down. The nearest thing to an official specification is RFC 4180, published in 2005, and it exists mostly to settle the awkward cases — what happens when the data itself contains a comma.

The rules that matter:

• A value containing a comma must be wrapped in double quotes. Smith, John is written "Smith, John", otherwise it reads as two columns.
• A double quote inside a quoted value is doubled. He said "hi" is written "He said ""hi""".
• A value may contain a line break as long as it is quoted. A single record can therefore span several lines in the file — which is why counting lines is not the same as counting rows.
• Rows end with a carriage return + line feed (\r\n). In practice almost every parser accepts a plain \n too.

Put together, a CSV carrying awkward data looks like this:

id,name,note
1,"Smith, John","He said ""hi"""
2,"Multi
line",plain

That is three rows of data across four lines of text, and it round-trips perfectly through any compliant parser. If a file looks like this and your spreadsheet still mangles it, the file is not the problem — the import settings are.

The single most common reason a CSV opens as one useless column is that it is not comma-separated at all.

In countries where the decimal separator is a comma — most of Europe and much of South America — 1,5 means one and a half. Using a comma as the column separator there would be chaos, so those systems use a semicolon instead, and Excel follows the list separator in your regional settings. A German colleague's export will therefore open perfectly on their machine and collapse into column A on yours.

You will meet three separators in the wild:

Open the file in any text editor first and look at line one. Whatever sits between the column names is your separator — and every good importer lets you set it explicitly.

Pick the route that matches what you actually want to do with it.

Just to read it

Any text editor opens a CSV instantly and shows you the truth: TextEdit on Mac, Notepad on Windows, or VS Code anywhere. This is always the right first move on a file that has behaved strangely, because it shows you the raw bytes rather than one program's interpretation of them.

To work with it as a spreadsheet

1. Convert it to a real Excel file first. Drop it into FileNaut's CSV to Excel converter and open the .xlsx it gives you. Every value is written exactly as it appears in the file, so nothing is reinterpreted on the way in.
2. Or import it into Excel properly — using the import dialog rather than a double-click. The exact steps are in the next section.
3. Or use Google Sheets: File → Import → Upload. Sheets lets you choose the separator, but note that it still converts data types automatically, so leading zeros are lost here too unless you format the column as plain text first.

To feed it to code or an API

Convert it to JSON with FileNaut's CSV to JSON tool, which turns each row into an object keyed by the header row. The reverse direction is available too.

This is the section worth bookmarking. Excel does not ask permission before interpreting your data, and all four of these happen on a plain double-click.

1. Leading zeros disappear

Excel sees 01234, decides it is the number 1,234 and drops the zero. This destroys postal codes, phone numbers with country prefixes, SKUs, bank sort codes and any ID with a fixed width. The file still contains 01234 — check it in a text editor and you will see it there. Only the imported copy is wrong.

2. Long numbers become scientific notation

Anything past 15 significant digits exceeds what a spreadsheet's number type can represent exactly. A 19-digit order ID such as 1234567890123456789 is displayed as 1.23457E+18 — and the underlying value is genuinely rounded, not merely displayed oddly. Widening the column will not bring the digits back.

3. Anything date-shaped becomes a date

Values that merely resemble dates get converted on sight. Gene names, version numbers, fractions and part codes are the classic casualties — 3-4 becomes 4 March. The reverse also bites: 03/04/2026 is 3 April in the UK and 4 March in the US, and CSV carries no locale information to settle it.

4. Accented characters turn to gibberish

This one is the encoding trap, and it is worth understanding because it is the easiest to prevent. A modern CSV is written in UTF-8. Windows Excel, however, assumes a legacy regional encoding unless the file starts with a three-byte marker called a byte order mark (BOM). Without it, the UTF-8 bytes for é get read as two separate characters:

In the file (UTF-8):   José,Montréal
Excel without a BOM:   José,Montréal

The file is not damaged — it is being decoded with the wrong alphabet. Importing it as UTF-8 explicitly, as shown below, displays it correctly.

The fix is to never double-click. Use the import dialog, which asks you how to interpret each column instead of guessing.

1. Open a blank workbook first. Do not open the CSV.
2. Go to the Data tab and choose From Text/CSV.
3. Select your file. A preview window opens.
4. Set File Origin to 65001: Unicode (UTF-8). This fixes the accented-character problem immediately — watch the preview update.
5. Check the Delimiter dropdown matches what you saw in the text editor (comma, semicolon or tab).
6. Click Transform Data rather than Load. In the editor that opens, select any column that must keep its exact characters — IDs, postal codes, phone numbers — and set its type to Text.
7. Click Close & Load.

Every column you marked as Text arrives exactly as written. It is roughly ninety seconds of work, and it is the difference between a clean import and a spreadsheet of subtly wrong IDs that nobody notices until someone ships to the wrong postal code.

If you would rather skip the dialog entirely, converting the CSV to XLSX first achieves the same result — the converter writes each value as text, so Excel has nothing left to guess about when it opens the file.

Use CSV to move data between systems, to import or export, and for anything a script will read. Use Excel when a human needs to work in the file — when you need formulas, several sheets, or formatting that survives being saved.

The trap to avoid: opening an .xlsx, saving it as CSV, and expecting your work to still be there. Saving to CSV keeps the values of the active sheet only. Formulas collapse to their last computed result, every other sheet is discarded, and all formatting is gone. Excel warns you once, in a dialog most people dismiss on reflex.

Going the other way is lossless, because you are only ever adding capability: CSV to Excel for a single file, or Merge CSVs to Excel to combine several CSVs into one workbook with a tab per file.

From a spreadsheet: File → Save As (Excel) or File → Download (Google Sheets) and pick CSV. In Excel, choose CSV UTF-8 if it is offered — that variant writes the byte order mark, which is what keeps accented characters readable when the file is opened again on Windows.

From an existing Excel file: use FileNaut's Excel to CSV converter. It handles the quoting rules for you, and exports each sheet as a separate CSV, since one CSV can only ever hold one table.

By hand: open a text editor, type your header row, then one line per record, and save with a .csv extension. Quote any value containing a comma. For a handful of rows this is genuinely the fastest method available.

From several files at once: Merge CSV files stacks multiple CSVs with matching columns into one.

1. Open the file in a text editor before you debug anything. It takes two seconds and tells you the separator, the encoding and whether the data was ever correct. Most "corrupted CSV" problems are import problems, and this is how you tell the difference.
2. Keep the original. Never save over the CSV you were sent. If an import goes wrong you want the untouched file to go back to.
3. Watch for the "number stored as text" warning. When values arrive as text — which is exactly what protects your IDs — Excel marks them with a small green triangle and SUM will ignore them. For columns that really are numbers, select them and use Data → Text to Columns → Finish to convert them back in one step.
4. A trailing empty line is normal. Most exporters end the final row with a line break. It is not a missing record and no parser will mind.
5. CSV has no types, so agree on formats up front. Every value is text until something interprets it. If you are exchanging CSVs regularly, standardise on ISO dates (2026-08-17) — they are unambiguous in every locale and sort correctly as plain text.