What Is a CSV File? Definition and Three Rules
CSV stands for comma-separated values. The specification people cite most often is RFC 4180, published by the IETF, which defines the file format along with how quotes and line breaks are handled. You only need to remember three rules.
- One line equals one recordThe first line conventionally holds the field names (the header), and every line after it is actual data.
- Fields are separated by commasEach comma marks a column boundary. Column order carries meaning, so shuffle the fields and you have a different dataset.
- Quote any value containing a comma, a line break, or a double quoteA double quote inside a value is escaped by doubling it:
"". Break this rule and your columns shift.
What Excel shows is not what the file contains
The same data looks like one thing in Excel and quite another as plain text.
| As shown in Excel | What the CSV file actually contains |
|---|---|
| Name / Department / Notes | name,department,notes |
| Sato / Sales / Tokyo | Sato,Sales,Tokyo |
| Suzuki / Engineering / Osaka, available for travel | Suzuki,Engineering,"Osaka, available for travel" |
The notes field on the third row contains a comma, so the whole value is wrapped in double quotes. Without those quotes every column shifts by one and every following row collapses. Most CSV trouble traces back to this.
Much of Europe uses a comma as the decimal separator, so semicolon-delimited CSVs are the norm there. When you read a file, let the parser detect the delimiter instead of hard-coding it.
- RFC 4180 — the common format for CSV files and its MIME type — the primary specification
- Wikipedia — Comma-separated values — overview and terminology
How to Create a CSV File (Three Ways)
There are three common routes: Excel, a plain text editor, and Google Sheets. Which one wins depends on how carefully you need to control character encoding. The traps specific to Excel — only one sheet is saved, leading zeros disappear — plus the right way to open the result, are covered step by step on a dedicated page.
In short: Excel when speed matters, a text editor when the encoding has to be exact, Google Sheets when you're collaborating.
- Microsoft Support — CSV file format — official description of Excel's CSV save formats
- Google Help — Download a spreadsheet — the official export procedure
Converting Between CSV and Excel
Opening and saving are two different operations with two different sets of pitfalls. Avoid double-clicking the file and go through Data → From Text/CSV instead. Follow Microsoft's official import steps and you avoid both the disappearing leading zero and scientific notation such as 1.23457E+17.
The full procedure — converting in both directions, choosing the right format, and recovering from problems — is on its own page.
- Microsoft Support — Import text (.txt or .csv) files — the official import procedure
- Microsoft Support — Save As — official description of the save formats
Why CSV Files Turn into Garbled Text — and How to Fix It
When an é shows up as é, the data is not damaged. The encoding used to save the file doesn't match the encoding used to read it. Japanese CSVs live in two worlds at once — Shift-JIS (CP932) and UTF-8 — and that split is where the trouble starts.
Identifying the cause, the three fixes, and what you can (and cannot) recover if you have already overwritten the file — all of that is on a dedicated page.
- MDN Web Docs — Encoding API: Encodings — the official reference for character encodings
- Microsoft Support — specifying file origin when importing — the Excel workaround
CSV vs. Excel: What's the Difference?
The short answer: Excel is an application, CSV is a container for data.
| Aspect | CSV | Excel (.xlsx) |
|---|---|---|
| What it really is | Plain text | A compound file that stores formatting, formulas, and charts |
| Appearance | Not preserved (colors, bold, column widths all disappear) | Preserved |
| Formulas | Not stored (only the calculated result) | Stored |
| Worksheets | One sheet per file | Multiple sheets allowed |
| Row limit | None (only whatever limit the software imposes) | 1,048,576 rows / 16,384 columns |
| Interoperability | Readable by virtually any application | Requires Excel-compatible software |
| Best suited to | Moving data between systems | Reports and analysis for people to read |
In practice the division of labour is simple: CSV for hand-off, Excel for shaping and presenting.
Searches for "csv business" or "csv vs csr" land on Creating Shared Value, the management concept Michael Porter introduced to describe companies solving social problems while creating economic value (FSG / Shared Value Initiative, official site). It shares a spelling with the file format and nothing else.
- Shared Value Initiative — Creating Shared Value — primary source for the management term
- RFC 4180 — CSV as a file format
Reading CSVs in Code (Python / VBA / C#)
Whichever language you use, the principle is the same: state the encoding explicitly. In Python that means the csv module (official docs) and pandas.read_csv; in VBA, QueryTables.Add (Microsoft Learn); in C#, TextFieldParser (Microsoft Learn).
Code samples, a table of encoding settings, and notes on writing files back out are collected on a dedicated page.
Public CSV Datasets You Can Actually Use
Many of the CSVs published by government agencies and industry bodies ship as Shift-JIS. Check the encoding before you import anything.
| Dataset | Publisher | Format / encoding | What to watch for |
|---|---|---|---|
| Postal code data | Japan Post | CSV / Shift-JIS | Full-width and half-width characters mixed; splitting addresses is fiddly |
| National holidays | Cabinet Office | CSV / UTF-8 | Covers 1970–2050; the standard source for holiday checks |
| Bank codes | Japanese Bankers Association | CSV / Shift-JIS | Updated often after mergers and name changes |
| Nikkei 225 constituents | Nikkei | CSV / UTF-8 | Constituents are rebalanced on a schedule |
| Accounting software import | Yayoi and others | CSV / Shift-JIS | Dates as YYYY/MM/DD; no thousands separators in amounts |
| Calendar events | Google Calendar | CSV / UTF-8 | Requires the exact column names from Google's template |
| Order history | Amazon | CSV / UTF-8 | Strip personal data before you store the file |
Frequently Asked Questions
Q.What does CSV stand for?
Comma-separated values. It describes a text file in which data is arranged in rows separated by commas, and the extension is .csv.
Q.How is a CSV different from an Excel file?
Excel is spreadsheet software; a CSV is a container for data in plain text. A CSV cannot hold formatting, formulas, or multiple sheets, but it is lightweight and every application can read it.
Q.Why does a CSV turn into garbage when I open it in Excel?
The encoding the file was saved in does not match the encoding Excel assumes. Open it through Data → From Text/CSV and specify the encoding — that fixes it. For the full walkthrough, see the dedicated page on garbled text.
Q.Should I save as UTF-8 or Shift-JIS?
UTF-8 for web services and cloud tools; Shift-JIS for domestic core systems, accounting software, and government submissions. Confirm what the receiving side expects.
Q.Why do leading zeros disappear?
Excel interprets the value as a number. The most reliable fix is to mark that column as Text at import time. See the conversion page.
Q.Can a CSV have multiple sheets?
No. One CSV file equals one sheet. Saving a multi-sheet workbook as CSV exports only the active sheet.
Q.What is the difference between CSV and TSV?
Only the delimiter. CSV separates fields with commas; TSV uses tabs.
Q.Is the CSV in "CSV business" the same thing?
No. There it stands for Creating Shared Value, a management approach that pursues social progress and economic value creation at the same time.
Q.My CSV files show an Excel icon — is something wrong?
No. Windows associates .csv with Excel by default. Nothing is broken, but double-clicking is exactly what causes garbled text. Right-click → Open with → Notepad.
Q.A CSV with tens of thousands of rows won't open in Excel
Excel caps out at 1,048,576 rows, and anything beyond that can be truncated. Load just the columns you need with Power Query, or aggregate the file with Python and pandas.
Q.Can I convert a CSV to PDF?
Yes. Open it in Excel, tidy up the layout, then choose Export → Create PDF/XPS.
Q.Can I use a delimiter other than a comma?
Yes. Semicolons are the norm in much of Europe, and tabs (TSV) are sometimes used instead.
- RFC 4180 — the answers in this FAQ follow this specification and the official documentation cited above
- Microsoft Support — CSV file formats