How to create a CSV file in Python

Python offers a straightforward way to create CSV files, a vital skill for data exchange and analysis. Its csv module simplifies how you work with tabular data for many different projects.

In this article, we'll show you several techniques to write data to CSV files. You'll get practical tips, see real-world examples, and learn how to debug common errors effectively.

Using the csv module to create a basic file

import csv

with open('example.csv', 'w', newline='') as file:
writer = csv.writer(file)
writer.writerow(['Name', 'Age', 'City'])
writer.writerow(['John', 25, 'New York'])
writer.writerow(['Alice', 30, 'Boston'])--OUTPUT--Name,Age,City
John,25,New York
Alice,30,Boston

This code uses the with statement to safely open and write to a file. The real magic happens with two key details:

• The newline='' argument is essential. It prevents Python from inserting extra blank rows between your data, a common issue caused by how different operating systems handle line endings.
• The csv.writer object and its writerow() method do the heavy lifting. You just pass a list to writerow(), and it automatically formats it into a comma-separated line in the file.

Standard CSV writing techniques

While writerow() is great for simple lists, the csv module offers more powerful tools for handling dictionaries, multiple rows at once, and custom formatting.

Using csv.DictWriter with dictionaries

import csv

data = [
{'Name': 'John', 'Age': 25, 'City': 'New York'},
{'Name': 'Alice', 'Age': 30, 'City': 'Boston'}
]

with open('dict_example.csv', 'w', newline='') as file:
fieldnames = ['Name', 'Age', 'City']
writer = csv.DictWriter(file, fieldnames=fieldnames)
writer.writeheader()
writer.writerows(data)--OUTPUT--Name,Age,City
John,25,New York
Alice,30,Boston

When your data is structured as a list of dictionaries, csv.DictWriter provides a more robust way to write your file. It maps dictionary keys directly to CSV columns, so you don't have to worry about the order of your data and is memory-efficient. This approach is the reverse of reading CSV files in Python.

• You must provide a list of fieldnames, which defines the column headers and their sequence.
• The writeheader() method conveniently writes the first row using your specified fieldnames.
• writerows() handles the rest, writing all dictionaries from your list in one go and ensuring values align with the correct headers.

Writing multiple rows with writerows()

import csv

data = [
['Name', 'Age', 'City'],
['John', 25, 'New York'],
['Alice', 30, 'Boston'],
['Bob', 22, 'Chicago']
]

with open('multiple_rows.csv', 'w', newline='') as file:
writer = csv.writer(file)
writer.writerows(data)--OUTPUT--Name,Age,City
John,25,New York
Alice,30,Boston
Bob,22,Chicago

When you have all your data ready as a list of lists, writerows() is the most direct way to write it all. Instead of looping and calling writerow() for each line, you can pass your entire dataset to this single method.

• It processes an iterable (like a list) where each element is a list representing one row. This approach is not only cleaner but also more efficient for writing data in bulk, as it handles the entire operation in one go.

Creating CSV files with custom delimiters

import csv

with open('custom.csv', 'w', newline='') as file:
writer = csv.writer(file, delimiter=';', quotechar='"')
writer.writerow(['Name', 'Age', 'City'])
writer.writerow(['John', 25, 'New York'])
writer.writerow(['Alice', 30, 'Boston'])--OUTPUT--Name;Age;City
John;25;New York
Alice;30;Boston

While commas are the default, you might need a different separator, especially if your data contains commas. The csv.writer is flexible enough to handle this by allowing you to specify a custom delimiter.

• The delimiter parameter lets you define the character used to separate fields. In this case, delimiter=';' replaces the standard comma with a semicolon.
• You can also control how fields are quoted with the quotechar argument, which is useful for data that might contain the delimiter itself.

Advanced CSV creation methods

While the csv module is powerful, you'll often need more specialized tools like pandas or methods for handling database exports and complex character encodings.

Using pandas to create CSV files

import pandas as pd

data = {
'Name': ['John', 'Alice', 'Bob'],
'Age': [25, 30, 22],
'City': ['New York', 'Boston', 'Chicago']
}

df = pd.DataFrame(data)
df.to_csv('pandas_example.csv', index=False)--OUTPUT--Name,Age,City
John,25,New York
Alice,30,Boston
Bob,22,Chicago

For more complex data manipulation, the pandas library is a game-changer. It uses a structure called a DataFrame, which you can think of as a smart, programmable spreadsheet. In this example, we create a DataFrame directly from a dictionary of lists.

• Exporting is incredibly simple with the to_csv() method—it writes the entire table to a file in one command.
• The index=False argument is crucial. It prevents pandas from adding an extra column with row numbers, ensuring your CSV file contains only the data you intended.

Creating CSV files from database records

import csv
import sqlite3

# Simulate database with in-memory SQLite
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE users (name, age, city)')
cursor.execute('INSERT INTO users VALUES ("John", 25, "New York")')
cursor.execute('INSERT INTO users VALUES ("Alice", 30, "Boston")')

with open('db_export.csv', 'w', newline='') as file:
writer = csv.writer(file)
writer.writerow(['Name', 'Age', 'City'])
cursor.execute('SELECT * FROM users')
writer.writerows(cursor.fetchall())--OUTPUT--Name,Age,City
John,25,New York
Alice,30,Boston

Exporting data from a database is a common task, and Python makes it seamless. This example uses the sqlite3 module to simulate a database connection and fetch records. After querying the database, the process is streamlined with just two key methods. This type of data workflow is perfect for vibe coding when you need quick prototypes.

• The cursor.fetchall() method retrieves all rows from your query result, returning them as a list of tuples.
• This list is perfectly formatted for writer.writerows(), which writes all the database records to the CSV file in a single, efficient step.

Handling special characters and encoding

import csv

with open('special.csv', 'w', newline='', encoding='utf-8') as file:
writer = csv.writer(file, quoting=csv.QUOTE_ALL)
writer.writerow(['Name', 'Description', 'Symbol'])
writer.writerow(['Euro', 'European currency', '€'])
writer.writerow(['Pi', 'Mathematical constant', 'π'])
writer.writerow(['Quote Example', 'Text with "quotes"', 'Sample'])--OUTPUT--"Name","Description","Symbol"
"Euro","European currency","€"
"Pi","Mathematical constant","π"
"Quote Example","Text with ""quotes""","Sample"

When your data includes special characters or quotes, you'll need to handle them carefully to avoid corrupting your CSV file. This example shows two key techniques for robust data writing.

• The encoding='utf-8' argument is vital. It ensures that characters like the Euro symbol (€) or Pi (π) are saved correctly.
• Using quoting=csv.QUOTE_ALL wraps every field in quotes. This prevents errors if your data contains commas or other special characters, making your file more reliable.

Move faster with Replit

Replit is an AI-powered development platform where all Python dependencies come pre-installed, so you can skip setup and start coding instantly. This lets you move from learning individual techniques, like the ones in this article, to building complete applications faster.

With Agent 4, you can describe the app you want to build, and it will take your idea to a working product. It handles writing the code, connecting to databases, and even deployment. Instead of just writing CSVs, you can build the tools that use them:

• A business intelligence tool that queries a database and exports daily sales figures into a CSV for analysis.
• A content management utility that takes a list of articles, structured as dictionaries, and writes them to a CSV file with headers.
• A data migration script that converts records to a CSV using a custom delimiter to avoid issues with text containing commas.

Simply describe your app, and Replit will write the code, test it, and fix issues automatically, all within your browser.

Common errors and challenges

Even with Python's simple tools, you might run into issues like extra blank rows, missing data, or incorrect formatting when creating CSV files.

Fixing the newline parameter for cross-platform CSV files

One of the most common frustrations is finding unexpected blank rows in your CSV file. This usually happens because different operating systems handle line endings differently. The fix is simple: adding the newline='' parameter. See what happens when it's omitted.

import csv

with open('example.csv', 'w') as file: # Missing newline=''
writer = csv.writer(file)
writer.writerow(['Name', 'Age'])
writer.writerow(['John', 25])

Without the newline='' argument, Python's file handling and the csv module can both add line endings. This conflict results in extra, unwanted blank rows in your output. The corrected code below demonstrates the proper approach.

import csv

with open('example.csv', 'w', newline='') as file:
writer = csv.writer(file)
writer.writerow(['Name', 'Age'])
writer.writerow(['John', 25])

The corrected code works because newline='' stops Python's default text translation, giving the csv module full control over line endings. This resolves the conflict that causes extra blank rows. It's a crucial habit to adopt whenever you write to a CSV file, as it guarantees consistent, clean output across different platforms like Windows, macOS, and Linux, where line ending conventions vary.

Handling missing keys in csv.DictWriter

A common pitfall with csv.DictWriter is inconsistent data. If a dictionary is missing a key defined in fieldnames, Python raises a KeyError and halts execution because it can't find a corresponding value. The following code demonstrates this problem.

import csv

data = [
{'Name': 'John', 'Age': 25, 'City': 'New York'},
{'Name': 'Alice', 'Age': 30} # Missing 'City' key
]

with open('dict_example.csv', 'w', newline='') as file:
fieldnames = ['Name', 'Age', 'City']
writer = csv.DictWriter(file, fieldnames=fieldnames)
writer.writeheader()
writer.writerows(data) # Will raise KeyError

The code stops because the second dictionary in the data list is missing the 'City' key. The csv.DictWriter requires every dictionary to match the fieldnames list. Understanding accessing dictionary elements in Python can help you check for missing keys before writing. The example below shows how to manage this gracefully.

import csv

data = [
{'Name': 'John', 'Age': 25, 'City': 'New York'},
{'Name': 'Alice', 'Age': 30} # Missing 'City' key
]

with open('dict_example.csv', 'w', newline='') as file:
fieldnames = ['Name', 'Age', 'City']
writer = csv.DictWriter(file, fieldnames=fieldnames, restval='')
writer.writeheader()
writer.writerows(data)

The fix is to add the restval='' parameter when creating the csv.DictWriter. This argument provides a default value—in this case, an empty string—for any keys from your fieldnames that are missing in a dictionary. It gracefully prevents a KeyError and keeps your program running. This is especially useful when your data comes from sources with optional fields, like API responses or user-submitted forms, ensuring your CSV output remains consistent.

Fixing numeric data type issues with the quoting parameter

By default, the csv module doesn't quote numeric values. This can cause problems when other programs expect all CSV fields to be strings, leading to import errors. The code below shows how this creates a mix of quoted and unquoted data.

import csv

data = [
['Product', 'Price'],
['Laptop', 1299.99],
['Mouse', 24.50]
]

with open('products.csv', 'w', newline='') as file:
writer = csv.writer(file)
writer.writerows(data)

The output contains raw numbers instead of text, which can cause import errors in systems expecting all values to be strings. The following example shows how to ensure every field is correctly formatted.

import csv

data = [
['Product', 'Price'],
['Laptop', 1299.99],
['Mouse', 24.50]
]

with open('products.csv', 'w', newline='') as file:
writer = csv.writer(file, quoting=csv.QUOTE_NONNUMERIC)
writer.writerows(data)

By setting quoting=csv.QUOTE_NONNUMERIC, you instruct the csv.writer to enclose all non-numeric data in quotes. This leaves numbers as-is, creating a clear distinction between text and numeric types in the output file. You'll find this especially helpful when your CSV is destined for spreadsheet software or databases that might otherwise get confused. It’s a simple way to ensure text is always interpreted as text, preventing potential import errors.

Real-world applications

With the fundamentals and error-handling covered, you can apply these skills to real-world tasks like exporting scraped data, converting JSON to CSV in Python, and preparing it for analysis.

Exporting web scraping results to CSV

Saving data you've scraped from the web into a CSV file is a practical way to organize and preserve it for analysis.

import csv
import requests
from bs4 import BeautifulSoup

response = requests.get('https://books.toscrape.com/')
soup = BeautifulSoup(response.text, 'html.parser')
books = [[book.h3.a['title'], book.select_one('.price_color').text]
for book in soup.select('.product_pod')[:3]]

with open('scraped_books.csv', 'w', newline='') as file:
writer = csv.writer(file)
writer.writerow(['Title', 'Price'])
writer.writerows(books)

This script combines web scraping with file writing. It starts by using the requests library to download the HTML from a book-listing website. Then, BeautifulSoup parses this HTML, making it searchable.

• A list comprehension efficiently targets and extracts the title and price for the first three books.
• The collected data, now a list of lists, is written to a CSV file using writer.writerows(), which handles all the rows in a single operation after a header is written.

Creating a CSV file with calculated fields for data analysis

You can make your data more useful for analysis by performing calculations on the fly and writing the results as new fields in your CSV file.

import csv

sales = [
{'date': '2023-01-15', 'product': 'Laptop', 'price': 1200, 'quantity': 2},
{'date': '2023-02-10', 'product': 'Mouse', 'price': 25, 'quantity': 15}
]

with open('sales_analysis.csv', 'w', newline='') as file:
writer = csv.writer(file)
writer.writerow(['Product', 'Total Revenue'])
for item in sales:
total = item['price'] * item['quantity']
writer.writerow([item['product'], f"${total:.2f}"])

This script shows how to process data before writing it to a file. It iterates through a list of sales records, performing a calculation within the loop to create new, summarized information.

• For each sale, it computes the total revenue by multiplying the price and quantity.
• It then writes a new row to the CSV containing only the product name and the calculated revenue, which is formatted into a currency string using an f-string.

This technique is useful for creating clean, analysis-ready reports directly from raw data sources. You can also combine multiple reports using merging CSV files in Python. AI coding with Python can automate these data transformation workflows even further.

Get started with Replit

Turn these techniques into a real tool with Replit Agent. Describe what you need, like "Convert a JSON file of users to CSV" or "Build a tool that calculates sales revenue and exports a summary CSV".

Replit Agent writes the code, tests for errors, and deploys your application. Start building with Replit.