Working with CSV Files in Python

Table of Contents

    CSV (comma-separated values) files are a popular file format for storing tabular data such as spreadsheets or databases. Python provides a convenient csv module for reading and writing these simple text files. There are some benefits as well as potential issues when working with CSVs that need to be considered.

    The main advantage of using CSV files is that they are compact to store and transmit, yet complete enough to represent full table data. The CSV format is widely supported across applications and has become a standard for data exchange. However, CSVs lack formal structure and are not efficient for complex analytics. There may also be inconsistencies in dialect and encoding that need to be handled.

    Opening and Reading CSV Files

    To open a CSV file in Python, use the open() function with 'r' to specify reading mode. Then use the csv module to parse the file contents.

    import csv

    with open('data.csv', 'r') as file: reader = csv.reader(file) for row in reader: print(row)

    Use csv.DictReader to read CSV rows as dictionaries for easier access by header name.

    import csv

    with open('data.csv', 'r') as file: reader = csv.DictReader(file) for row in reader: print(row['firstname'], row['lastname'])

    Writing to CSV Files

    To write to a CSV file, open it with 'w' mode and create a csv.writer object.

    import csv

    with open('output.csv', 'w') as file: writer = csv.writer(file)

    writer.writerow(['Name', 'Age']) writer.writerow(['John', 20]) writer.writerow(['Jenny', 30])

    Use csv.DictWriter to write dictionaries as CSV rows.

    import csv

    fields = ['Name', 'Age'] rows = [ {'Name':'John', 'Age':20}, {'Name':'Jenny', 'Age':30} ]

    with open('output.csv', 'w') as file: writer = csv.DictWriter(file, fieldnames=fields)

    writer.writeheader() writer.writerows(rows)

    CSV Module Options

    The csv module provides many helpful options:

    • delimiter - Custom field delimiter like | or for TSV files
    • quotechar - Quote character for fields with special characters
    • quoting - Controls quoted fields behavior
    • escapechar - Escape character used in quoted fields

    For example:

    writer = csv.writer(file, delimiter='|', quotechar='"', quoting=csv.QUOTE_ALL)

    Reading and Writing CSV Fields

    • Use csv.Sniffer to automatically detect CSV dialect
    • Read specific columns with usecols parameter
    • Write rows as tuples or dictionaries
    • Convert values during reads/writes with parameters like quoting


    In summary, CSV files provide a simple yet widely supported way to store and exchange tabular datasets. Python's csv module gives full control over reading and writing CSVs with options to configure the dialect, delimiters, quoting and more. Handling inconsistent formatting or encoding is made easy. While CSVs lack the more rigid structure of databases, their compactness and universality make them an ideal format for data exchange and tabular data storage for applications in Python.

    The csv module along with Python's built-in file handling makes it straightforward to read and write CSV data. With the ability to parse fields, encode values and handle errors robustly, CSVs can be used reliably in many contexts for data analytics, file exports and more. Overall, CSV is a key tabular data format supported comprehensively by Python's csv module.

    Andrew ThompsonAndrew Thompson
    I have over 10 years of experience programming in Python. I am skilled in web development with Django and Flask, data analysis with Pandas and NumPy, and scientific computing with SciPy. I am also proficient at Python automation and scripting.