RESOURCES
Published 2026-11-09 · 4 min read
What is a CSV file, and how do you open it correctly?
CSV is a plain-text table. How to open one in Excel, Google Sheets or Numbers without losing leading zeros, dates or accented characters.
What a CSV file is
A CSV (comma-separated values) file is a plain-text table: each line is a row, and commas separate the values within it. The first line usually holds the column names. There are no fonts, colours, formulas or multiple sheets — just text — which is why almost every program that handles tables can read and write CSV, and why it's the standard format for exports from banks, shops, CRMs and databases.
Opened in a text editor, a contacts export might read name,email,city on the first line, then Ana Lopez,[email protected],Madrid on the next, one person per line.
The rules that trip people up
CSV looks trivial, but a few conventions decide whether a file opens cleanly.
- Quoting: a value containing a comma, a double quote or a line break is wrapped in double quotes, and a quote inside it is doubled: "Smith, John" or "He said ""hi""". RFC 4180 documents this common form, though not every program follows it.
- Separators: in countries where the comma is the decimal separator, such as Germany and France, Excel writes and expects semicolons instead. A semicolon file opened on a computer set up for commas lands in a single column, and vice versa.
- Encoding: a CSV doesn't say which character encoding it uses. Most modern exports are UTF-8; older Windows software often writes a regional code page such as Windows-1252.
Why Excel changes your data when you double-click a CSV
Double-clicking opens the file with Excel's guesses applied. It splits columns using your system's list separator, can read a UTF-8 file without a byte-order mark as the legacy encoding (so é turns into é), and converts anything that looks like a number or a date.
- Leading zeros are dropped: ZIP codes, phone numbers and product codes like 00742 become 742.
- Long numbers such as account or card numbers are shown in scientific notation, and digits after the 15th become zeros.
- Values that look like dates — 3-4, 1/2, MAR1 — are turned into dates.
- If you then save, the changed values are written back into the CSV.
How to open a CSV file in Excel without breaking it
Import the file instead of double-clicking it. In Excel for Microsoft 365 or Excel 2016 and later on Windows:
- Open a blank workbook and choose Data → From Text/CSV, then pick the file.
- Set File Origin to 65001: Unicode (UTF-8) if accented characters look wrong, and check the Delimiter.
- Set Data Type Detection to Do not detect data types to keep every value as text. Or click Transform Data, select columns such as codes, IDs and phone numbers, set Data Type to Text, and choose Replace current if asked.
- Click Close & Load. To save as CSV again with accents intact, use File → Save As → CSV UTF-8 (Comma delimited).
Google Sheets, Numbers and text editors
Google Sheets: File → Import → Upload, then choose the separator type. Untick Convert text to numbers, dates, and formulas to keep every value exactly as written.
Numbers on a Mac opens a CSV directly as a new spreadsheet; File → Export To → CSV saves it back. LibreOffice Calc shows an import dialog every time, with character set, separator and per-column type, which makes it a good choice for awkward files.
Notepad, TextEdit or any code editor shows the raw text. It's the quickest way to see which separator and quoting a file actually uses before importing it anywhere.
How to make a CSV file
From Excel, use File → Save As (or Save a Copy) and choose CSV UTF-8 (Comma delimited). From Google Sheets, use File → Download → Comma-separated values. Either way only the current sheet is saved, formulas are replaced by their results, and formatting is lost, so keep the original workbook.
By hand, type one row per line with commas between values and save as plain text with a .csv extension. In TextEdit, choose Format → Make Plain Text first.
Converting CSV to Excel, PDF and back
Convyx reads a CSV as UTF-8 and falls back to Windows-1252 for older exports that aren't valid UTF-8, so accented characters come through when turning CSV into an XLSX workbook. Going the other way, XLSX to CSV exports only the first worksheet's cell values. CSV to PDF expects UTF-8 with comma separators and lays the table out the way it would print.