How to Write Data Into a CSV File

What a CSV file is and why you'd write to one

A CSV file is a plain text document where data sits in rows and columns, separated by commas. CSV stands for "comma-separated values." When you open one in Excel or Google Sheets, it looks like a spreadsheet. When you open it in Notepad, you see text with commas dividing the columns.

You write data into a CSV file when you want to store information in a format that almost any program can read — Excel, Google Sheets, databases, Python scripts, and dozens of other tools all understand CSV. If you're tracking a budget, logging sensor readings, exporting customer names, or moving data between programs, CSV is usually the simplest format that works everywhere.

The process of writing to a CSV file depends on what tool you're using. You might do it in Excel by saving a spreadsheet, in Python by running a script, or in a database program by exporting results. The underlying idea is the same: organize your data in rows and columns, separate each column with a comma, and save it as a plain text file with a .csv extension.

Key Takeaways

A CSV file stores data in rows and columns separated by commas, and any program that reads spreadsheets or text can open it.
In Excel or Google Sheets, you create a CSV by typing data into cells and saving the file with a .csv extension instead of .xlsx.
In Python or other programming languages, you use a CSV library or module to write rows of data to a file automatically.
If your data contains commas, quotation marks, or line breaks, you must wrap those fields in quotes or the file will break when someone opens it.
Always test your CSV file by opening it in a spreadsheet program to make sure the columns line up correctly before sharing it.

Writing a CSV file in Excel or Google Sheets

The easiest way to create a CSV file is to use a spreadsheet program you already know. In Excel, type your data into cells the way you normally would — headers in the first row, data in the rows below. Each column becomes a field separated by a comma in the final file.

When you're done entering data, go to File > Save As. In the "Save as type" dropdown, select "CSV (Comma delimited)" or "CSV UTF-8 (Comma delimited)". Choose a location and filename, then click Save. Excel will warn you that some features may be lost — that's normal, because CSV is a simpler format than Excel's native .xlsx format. Click "Yes" to proceed.

Google Sheets works the same way. Click File > read > Comma-separated values (.csv). Your browser will read the file to your computer. The file will be named after your sheet tab, so if your tab is called "Customers", the file will be "Customers.csv".

Writing a CSV file in Python

If you're writing a program that needs to create CSV files automatically, Python's built-in csv module makes it straightforward. You open a file for writing, create a CSV writer object, and then write rows of data one at a time.

Here's the basic pattern: import the csv module, open a file with a .csv name, create a writer, and use the writerow() method to add each row. If your first row contains column headers, write that as your first row. Then write each data row as a list of values. Python handles the commas and formatting for you.

If you're using a different language like JavaScript, Java, or C#, the process is similar — each language has a CSV library that handles the formatting. The advantage of using a library instead of writing commas yourself is that it automatically handles edge cases, like data that contains commas or quotation marks.

Handling data that contains commas or special characters

CSV files break if your data contains commas, because the program reading the file won't know whether a comma is a separator or part of the data. If a customer's name is "Smith, John" and you write it without protection, the reader will think "Smith" and "John" are separate columns.

The solution is to wrap any field containing a comma, quotation mark, or line break in double quotes. So "Smith, John" becomes "Smith, John" in the CSV file. If your field contains a quotation mark, you escape it by doubling it — so a name like O'Brien becomes "O'Brien" (the quotes protect the apostrophe from being misread).

Most CSV libraries handle this automatically. In Excel or Google Sheets, you don't need to do anything — the program adds quotes where needed when you save. In Python, the csv module does it for you. If you're writing CSV by hand or with a straightforward text editor, you have to add the quotes yourself.

Testing your CSV file before sharing

Always open your CSV file in a spreadsheet program after you create it, even if you created it in a spreadsheet program. This catches mistakes like misaligned columns, missing data, or formatting problems that won't show up in a text editor.

Open Excel or Google Sheets, then open your CSV file the way you would open any spreadsheet. Look at the data — do the columns line up? Is the header row in the first row? Are there any blank columns that shouldn't be there? If something looks wrong, go back to your source, fix the data, and save again.

If you're sharing the CSV with someone else, ask them to open it and check that it looks right on their end too. Different programs sometimes interpret CSV slightly differently, and what looks correct in Excel might display oddly in Google Sheets or a database program.

Common mistakes when writing CSV files

The most common mistake is forgetting to save with the .csv extension. If you save as .xlsx or .txt, the file won't be recognized as a CSV file by other programs, even if the data inside is formatted correctly. Always check the filename and extension before you save.

Another mistake is including extra blank rows or columns. If you have empty rows between your data, or an empty column at the end, they'll show up in the CSV file and confuse the program reading it. Delete any blank rows and columns before you save.

A third mistake is inconsistent data types in the same column. If one row has a number like 100 and another has text like "100 units", the program reading the file might interpret them differently. Be consistent — if a column contains numbers, put only numbers in it; if it contains text, put only text.

When to use CSV instead of other formats

CSV is the right choice when you need a format that works everywhere and you don't need fancy formatting like colors, formulas, or multiple sheets. It's ideal for data that's mostly text and numbers organized in rows and columns.

If you need to preserve Excel formulas, use .xlsx instead. If you need to store complex data with relationships between tables, use a database format like .sqlite or .mdb. If you need to share formatted reports with colors and charts, use .pdf. But for straightforward data exchange between programs, CSV is almost always the best choice.

Frequently Asked Questions

Can I write a CSV file in Notepad?

Yes, but it's error-prone. Open Notepad, type your data with commas separating columns and line breaks separating rows, then save with a .csv extension. The problem is that Notepad won't warn you if you forget a comma or add an extra one, so the file will break when someone opens it. Use a spreadsheet program or a CSV library instead.

What's the difference between CSV and TSV?

TSV stands for "tab-separated values" — it's the same idea as CSV but uses tabs instead of commas to separate columns. TSV is useful when your data contains commas but rarely contains tabs. Most spreadsheet programs can open both formats. Choose based on what your data contains.

Can I write a CSV file with multiple sheets?

No. CSV files are flat — they contain only one sheet of data. If you need multiple sheets, use Excel's .xlsx format instead. If you must use CSV, save each sheet as a separate CSV file with a different name.

What happens if I open a CSV file in Excel and then save it?

Excel will ask you to confirm that you want to save in CSV format, because CSV is simpler than Excel's native format. If you click "Yes", Excel saves it as CSV and any formatting, formulas, or extra sheets are lost. If you want to keep the Excel format, click "No" and save as .xlsx instead.

Can I write a CSV file from a database?

Yes. Most database programs have an export function that writes query results to a CSV file. In Microsoft SQL Server, you can right-click a table and select "Export Data". In MySQL, you can use the SELECT INTO OUTFILE command. In Microsoft Access, you can right-click a table and choose "Export" then "Text File". The exact steps depend on your database program.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.