Save Data to Excel and CSV Files in Python

Key Data Storage Formats in Python

Working with Excel and CSV files is a crucial skill for Python developers, especially in data analysis, automated reporting, and backend development. Writing data to files correctly allows you to efficiently store, transfer, and process information across different systems.

This guide covers the main ways to save data into popular formats using both standard and third-party Python libraries. You'll explore practical examples of working with CSV and Excel files, and learn techniques for formatting and structuring your data.

The CSV Format: Features and Use Cases

What is CSV?

CSV (Comma-Separated Values) is a simple text format for storing tabular data. Values are separated by commas or other delimiters like semicolons or tabs. CSV is widely used for data exchange between different applications and systems.

Advantages of CSV

• Lightweight and human-readable text format
• Opens quickly in any text editor, including Excel
• Ideal for transferring data between systems
• Smaller file sizes compared to binary formats
• Supported by most data processing programs

Limitations of CSV

• No support for complex formatting (colors, fonts)
• Cannot store formulas or calculations
• Limited support for different data types
• Cannot store images or nested tables

Working with CSV Using the Standard Library

Getting Started with the csv Module

The csv module is part of Python's standard library, so no extra installation is needed. It provides convenient tools for reading and writing CSV files with various formatting options.

import csv

data = [
    ['Name', 'Age', 'City'],
    ['John', 28, 'New York'],
    ['Maria', 25, 'Los Angeles']
]

with open('people.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.writer(file)
    writer.writerows(data)

After running this code, a file named people.csv is created with the following content:

Name,Age,City
John,28,New York
Maria,25,Los Angeles

Customizing CSV Delimiters

The default CSV delimiter can be changed based on system requirements or regional settings:

with open('people.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.writer(file, delimiter=';')
    writer.writerows(data)

Using DictWriter for Dictionary Data

For easier handling of data as dictionaries, use the DictWriter class:

import csv

data = [
    {'Name': 'John', 'Age': 28, 'City': 'New York'},
    {'Name': 'Maria', 'Age': 25, 'City': 'Los Angeles'}
]

with open('people.csv', 'w', newline='', encoding='utf-8') as file:
    fieldnames = ['Name', 'Age', 'City']
    writer = csv.DictWriter(file, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(data)

Working with Excel Files Using openpyxl

Installing and Setting Up the Library

Excel files are used when you need to store structured data with formatting, formulas, or charts. The openpyxl library provides a complete set of tools for working with Excel files.

pip install openpyxl

Creating a Basic Excel File

from openpyxl import Workbook

data = [
    ['Name', 'Age', 'City'],
    ['John', 28, 'New York'],
    ['Maria', 25, 'Los Angeles']
]

wb