How to create a CSV file in Python

Creating CSV files in Python is essential for data export, storage, and sharing. Python's built-in csv module simplifies this process, letting you easily convert data into a universally readable format.

You'll learn several techniques for writing CSV files, complete with practical tips, real-world applications, and debugging advice to ensure you can handle your data correctly every time.

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 snippet creates a CSV file by first opening example.csv in write mode ('w'). The csv.writer object takes this file and provides the writerow() method, which you use to add the header and subsequent data rows one by one.

The most important detail is setting newline='' when opening the file. This prevents the writer from inserting extra blank rows between your data, which can otherwise happen due to how different operating systems handle line endings. It’s a small but crucial step for creating a clean, properly formatted file.

Standard CSV writing techniques

The csv module also provides more specialized tools, letting you write from dictionaries with DictWriter, add multiple rows with writerows(), or even customize your file's format.

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 is the ideal tool. It simplifies writing by mapping dictionary keys directly to CSV columns, making your code more readable and less error-prone.

• You initialize it by providing a list of fieldnames, which defines the column headers and their order.
• The writeheader() method writes this header row to your file.
• Finally, writerows() takes your entire list of dictionaries and writes all the data at once, correctly aligning values under their corresponding 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 your data is already structured as a list of lists, writerows() is the most efficient way to write it to a file. It's a bulk operation that saves you from looping and calling writerow() for each line.

• You simply pass your entire dataset—including the header row—to the method.
• A single call to writer.writerows(data) handles writing all the rows, making your code cleaner and more concise, especially for large datasets.

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 standard, they aren't always practical, especially if your data includes them. The csv.writer gives you control over the file's structure using optional parameters.

• Set the delimiter to use a different separator, like a semicolon (';').
• Use quotechar to specify how to wrap data. This is essential when a field might contain the delimiter character.

These options make it easy to create custom-formatted files, like TSVs (tab-separated values) or other delimited formats, ensuring your data remains clean and parsable.

Advanced CSV creation methods

For more complex tasks, you can move beyond the standard library to use tools like pandas, pull data directly from databases, and manage tricky encoding issues.

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

The pandas library is a go-to for data analysis and manipulation. This code first organizes your data into a DataFrame—a powerful, table-like structure ideal for handling datasets. The to_csv() method then writes this DataFrame directly to a file in one simple step.

• The most important detail is setting index=False.
• This tells pandas not to write the DataFrame's row index as an extra column in your CSV, ensuring a clean output.

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 into a CSV is a common task. This example uses Python's built-in sqlite3 module to connect to a database and retrieve records.

• First, you write the header row manually using writerow().
• Then, cursor.fetchall() retrieves all the records from your SQL query as a list of rows.
• Finally, you pass this list directly to writer.writerows() to write all the data at once, making the export process clean and efficient.

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 contains special characters or quotes, you need to handle them carefully to avoid corrupting your file. This code demonstrates two key techniques for robust CSV writing.

• Setting encoding='utf-8' is essential for correctly saving non-English characters like '€' and 'π'. Without it, you risk writing garbled text or triggering an error.
• Using quoting=csv.QUOTE_ALL wraps every field in quotes. This is a safe way to handle data that might contain the delimiter and also correctly escapes any internal quotes within your strings.

Move faster with Replit

Replit is an AI-powered development platform that transforms natural language into working applications. You can describe what you want to build, and Replit Agent creates it—complete with databases, APIs, and deployment.

For the CSV creation techniques we've explored, Replit Agent can turn them into production-ready tools. You can go from an idea to a functional app that leverages methods like writerow() or the power of pandas.

• Build a data export tool that pulls records from a database and generates a downloadable CSV report.
• Create a data cleaning utility that reads a messy CSV, applies custom formatting with different delimiters, and outputs a clean file.
• Deploy a JSON-to-CSV converter that transforms structured API data into a universally readable format.

Describe your app idea, and Replit Agent will write the code, test it, and fix issues automatically, all within your browser.

Common errors and challenges

Even with the right tools, you can run into a few common pitfalls when writing CSV files in Python.

Fixing the newline parameter for cross-platform CSV files

Forgetting the newline='' parameter is a classic mistake that leads to extra blank rows in your CSV file. This happens because different operating systems use different characters to mark the end of a line. By setting newline='' when you open the file, you tell Python's csv module to handle line endings correctly, ensuring your file is clean and portable across Windows, macOS, and Linux without any weird spacing issues.

Handling missing keys in csv.DictWriter

When using csv.DictWriter, you'll get a ValueError if one of your dictionaries is missing a key specified in your fieldnames. To prevent this, you can set a default value for any missing keys by using the restval parameter. For example, csv.DictWriter(file, fieldnames=..., restval='N/A') would write 'N/A' in any cell where data is missing. If your dictionaries have extra keys not in fieldnames, you can tell the writer to ignore them by setting extrasaction='ignore'.

Fixing numeric data type issues with the quoting parameter

The csv module treats all data as strings, which can cause problems if another program expects numeric values. To signal which fields are numeric, you can use the quoting parameter.

• By setting quoting=csv.QUOTE_NONNUMERIC, you instruct the writer to wrap only non-numeric fields in quotes.
• When another tool reads the file, it can interpret any unquoted values as numbers, preserving your data types and preventing potential import errors.

Fixing the newline parameter for cross-platform CSV files

It's a classic snag: you write a CSV file, only to find it riddled with extra blank rows. This happens because operating systems handle line endings differently, and without specific instructions, Python's csv module can misinterpret them. See what this looks like below.

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, the csv writer adds its own line endings on top of the system's default, creating extra blank rows. The corrected code below shows how to prevent this behavior.

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 by adding newline='' to the open() function. This is the key to preventing extra blank rows. Here’s why it’s so important:

• It gives the csv module full control over line endings.
• It stops the operating system from inserting its own line breaks, which create the unwanted spacing.

Make it a habit to always include this parameter to ensure your CSVs are clean and portable across different platforms.

Handling missing keys in csv.DictWriter

Using csv.DictWriter is great for clean code, but it expects your data to be perfectly uniform. If a dictionary is missing a key that’s listed in your fieldnames, the process will halt with an error. See what happens below.

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 writerows() method fails because Alice's dictionary lacks the 'City' key, which the fieldnames list requires. This mismatch causes a KeyError. The corrected code below shows how to handle 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 corrected code prevents a KeyError by adding the restval='' parameter to the csv.DictWriter. This tells the writer to insert an empty string for any key that's missing from a dictionary, which keeps your script from crashing when it hits inconsistent data.

• This ensures your CSV file is always created, with blank cells for any missing values, making your code more robust and reliable.

Fixing numeric data type issues with the quoting parameter

Python's csv module doesn't distinguish between data types; it writes everything as a string. This can cause headaches when another program expects numeric values, leading to import errors or incorrect calculations. The code below shows what happens when numbers become text.

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 resulting CSV file contains prices as text, not numbers. This means any program trying to perform calculations on the Price column will fail. See how to preserve the numeric type in the code below.

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)

The corrected code adds the quoting=csv.QUOTE_NONNUMERIC parameter to the csv.writer. It’s a simple change that tells the writer to only wrap non-numeric fields in quotes, leaving numbers as they are. This is crucial when your CSV will be used by other tools that need to perform calculations.

• It ensures that values like 1299.99 are treated as numbers, not text.
• This prevents data type errors during import or analysis.

Real-world applications

Moving beyond troubleshooting, you can apply these CSV creation skills to practical tasks like exporting web scraping results and preparing data for analysis.

Exporting web scraping results to CSV

After scraping data from a website, saving it to a CSV is a practical way to store and organize the information for later use.

The process starts by using the requests library to fetch the HTML from a URL, then BeautifulSoup parses it into a searchable structure. From there, you can target specific HTML elements, like book titles and prices, and extract them into a list. With your data collected, you can write it to a file using the standard csv.writer, first adding a header with writerow() and then saving all the scraped information with writerows().

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 showcases a common web scraping workflow. A concise list comprehension builds the books list by iterating through the first three product containers found with soup.select('.product_pod')[:3].

• For each book, it grabs the title from the title attribute of a link and the price from the text of an element with the price_color class.
• This creates a nested list where each item is a [title, price] pair.

The writerows() method then efficiently writes this entire list to the CSV file in a single operation.

Creating a CSV file with calculated fields for data analysis

You can process raw data on the fly, calculating new values like total revenue before saving the results directly into a CSV for analysis.

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 transforms a list of sales dictionaries into a summary report. It iterates through the sales data, performing a calculation for each entry before writing it to the file.

• Inside the loop, the total revenue is calculated by multiplying the price and quantity for each product.
• The writerow() method then adds a new line containing the product's name and the calculated revenue, which is formatted into a currency string.

Get started with Replit

Now, turn these techniques into a real tool. Tell Replit Agent to “build a web scraper that exports data to a CSV” or “create a utility that converts JSON API responses to a CSV file.”

Replit Agent writes the code, tests for errors, and deploys your app directly from your browser. Start building with Replit.