Which Method Should You Use?
How you create the file should follow from who opens it later, and in what software. Start with the overview.
| Method | Best for | Encoding | Watch out for |
|---|---|---|---|
| Excel | Building a table quickly; you already work in Excel daily | Selectable (UTF-8 recommended) | One sheet only; leading zeros can vanish |
| Text editor | A few lines of test data; strict control over encoding | Whatever you choose | You type everything by hand |
| Google Sheets | Several people editing before you export | UTF-8 only | Cannot export Shift-JIS |
| Code | Large datasets; scheduled generation | Whatever you choose | You have to write the code |
- Microsoft Support — CSV file format — what Excel does when it saves as CSV
- Google Help — Download a file — exporting from Sheets
Creating a CSV in Excel (the Most Common Route)
- Type the column names into row 1Name, Department, Notes — whatever your fields are. Leave no blank columns and no merged cells.
- Enter the dataFormulas are not saved; only the calculated values are written out.
- Choose File → Save As
- Set the file type to "CSV UTF-8 (Comma delimited)"This is the default choice in Excel 2016 and later, and Microsoft's own guide to save formats recommends the UTF-8 variant too.
- Pick a location and saveThe "some features will be lost" warning is normal for CSV. Click straight through it.
1. Only one sheet is saved. A CSV file holds exactly one sheet. Save a multi-sheet workbook as CSV and only the active sheet is written out.
2. Leading zeros and symbols disappear. "0012" is stored as the number 12. Format the column as text, or prefix the value with an apostrophe: '0012.
Creating a CSV in a Text Editor
This is the right tool when the encoding has to be guaranteed, or when you only need a handful of test rows. In Windows Notepad you can choose the encoding from the dropdown when you save (see Microsoft's Notepad guide).
product_code,product_name,unit_price,stock
A-001,Laptop,89800,12
A-002,"Mouse, wireless",2980,140
A-003,27-inch monitor,32800,7
Save with the .csv extension and UTF-8 encoding without a BOM. For web systems, that combination causes the fewest problems.
The status bar in the bottom right shows the encoding (UTF-8 / Shift-JIS) and the line endings (LF / CRLF). Add the Rainbow CSV extension and each column gets its own color, so a shifted column is obvious at a glance.
- Python documentation — csv module — reading and writing that follows RFC 4180
- RFC 4180 — how to quote a value correctly
Creating a CSV in Google Sheets
- Build the tableThe advantage is that people can edit it together.
- File → Download → Comma-separated values (.csv)Only the sheet you currently have open is exported (Google's official steps).
- Check the encodingThe export is always UTF-8. If an internal system needs Shift-JIS, use the conversion steps on the garbled text page.
- Google Help — Download a spreadsheet — the official procedure
Generating a CSV with Code
For scheduled exports or large volumes, writing the file from code is the dependable option. With pandas it takes a single call: to_csv (official docs).
import pandas as pd
df = pd.DataFrame([
{"product_code": "A-001", "product_name": "Laptop", "unit_price": 89800},
{"product_code": "A-002", "product_name": "Mouse", "unit_price": 2980},
])
# Forget index=False and you get an extra row-number column
# utf-8-sig (with a BOM) opens in Excel without garbled text
df.to_csv("products.csv", index=False, encoding="utf-8-sig")
How to Open a CSV File
Opening in Excel (when you want to work with the data)
Don't double-click. Go through Excel's Data → From Text/CSV instead. A double-click is precisely what eats leading zeros and turns long numbers into scientific notation (covered in detail on the conversion page).
Opening in Notepad or a text editor (when you want to see the raw data)
A CSV is plain text, so an editor shows the original characters exactly as stored. If double-clicking keeps dragging you into Excel, right-click → Open with → Notepad, then tick "Always use this app" and the editor will open it from then on.
Windows simply associates .csv with Excel. Either change the association, or use Open with each time.
- Microsoft Support — importing text and CSV files — the correct way to open a CSV in Excel
Common Mistakes and How to Fix Them
| Symptom | Cause | Fix |
|---|---|---|
| Columns are shifted | A value containing a comma was not quoted | Wrap it in "…", following the quoting rules in RFC 4180 |
| Leading zeros disappear | The value was read as a number | Set the column to Text during import |
| Dates change | Automatic date conversion | Same as above — make the column text |
| Garbled text on open | Encoding mismatch | The three fixes on the garbled text page |
| Everything lands on one line | Wrong line endings (classic Mac CR, for example) | Re-save with LF or CRLF |
- RFC 4180 — quoting and line-break rules
- Microsoft Support — import procedure
Frequently Asked Questions
Q.Does a CSV file have to use the .csv extension?
It is a convention rather than a rule. Most software will open .CSV in capitals, but for system integrations stick to lowercase .csv.
Q.Which encoding should I use when writing a CSV in Notepad?
UTF-8 for the web and for tool integrations, Shift-JIS (shown as ANSI in Notepad) for domestic core systems. Confirm what the receiving system expects.
Q.Can a CSV contain empty cells?
Yes. Two consecutive commas — value,,value — produce an empty cell between them. Trailing empty cells are handled the same way.
Q.Why won't other software open a CSV I made in Excel?
The encoding or the delimiter is the likely culprit. Open the file in Notepad first to see what is actually in it, then follow the steps on the garbled text page.
Q.How should I check the CSV I just created?
Open it in Notepad or VS Code and check the row count, column count, and quoting by eye. For large files, use Excel's status-bar count or count the lines in code.