Export Excel to CSV with Python xlrd

Excel files are widely used for data storage. But CSV format offers better compatibility. Python xlrd makes conversion easy.

This guide shows you how to export Excel data to CSV. You will learn step by step. No prior experience needed.

Why Convert Excel to CSV?

CSV files have several advantages. They are lightweight and universally compatible. Most data tools accept CSV format.

Excel files can be complex. They contain formatting and multiple sheets. CSV keeps only raw data.

Python automation saves time. Manual conversion is tedious. Scripts handle bulk files efficiently.

Installing xlrd Library

First, install the xlrd package. Use pip command in your terminal. This downloads the library.

pip install xlrd

Verify installation works. Import the module in Python. No errors means success.

import xlrd
print("xlrd imported successfully")

xlrd imported successfully

For installation help, see our guide on Install xlrd and xlwt in Python Easily.

Basic Excel to CSV Conversion

Start with a simple Excel file. It has one sheet with basic data. We will convert it to CSV.

Create a sample Excel file. Name it "data.xlsx". Add some sample rows and columns.

Here is the conversion code. It reads the Excel file. Then writes to CSV format.

import xlrd
import csv

# Open the Excel file
workbook = xlrd.open_workbook('data.xlsx')

# Select the first sheet
sheet = workbook.sheet_by_index(0)

# Create CSV file
with open('output.csv', 'w', newline='') as csvfile:
    csvwriter = csv.writer(csvfile)
    
    # Write each row from Excel to CSV
    for row_idx in range(sheet.nrows):
        row_data = sheet.row_values(row_idx)
        csvwriter.writerow(row_data)

print("Conversion completed successfully")

Conversion completed successfully

The open_workbook function loads your Excel file. The sheet_by_index method selects the worksheet.

Learn more about workbook handling in our Python xlrd Guide: Load Workbooks and Iterate Sheets.

Handling Multiple Sheets

Excel files often contain multiple sheets. You can export each sheet separately. Or combine them into one CSV.

This example exports each sheet to its own CSV file. The sheet name becomes part of the filename.

import xlrd
import csv

workbook = xlrd.open_workbook('data.xlsx')

# Process each sheet
for sheet_idx in range(workbook.nsheets):
    sheet = workbook.sheet_by_index(sheet_idx)
    
    # Create CSV filename with sheet name
    csv_filename = f"sheet_{sheet.name}.csv"
    
    with open(csv_filename, 'w', newline='') as csvfile:
        csvwriter = csv.writer(csvfile)
        
        for row_idx in range(sheet.nrows):
            row_data = sheet.row_values(row_idx)
            csvwriter.writerow(row_data)
    
    print(f"Exported {sheet.name} to {csv_filename}")

Exported Sheet1 to sheet_Sheet1.csv
Exported Data to sheet_Data.csv

The nsheets property tells how many sheets exist. Loop through each one for processing.

Working with Specific Data Ranges

You might not need all data. Export only specific rows and columns. This saves processing time.

This code exports only rows 5-15 and columns 0-3. It skips headers or unwanted data.

import xlrd
import csv

workbook = xlrd.open_workbook('data.xlsx')
sheet = workbook.sheet_by_index(0)

with open('filtered_data.csv', 'w', newline='') as csvfile:
    csvwriter = csv.writer(csvfile)
    
    # Export specific range (rows 5-15, columns 0-3)
    for row_idx in range(5, 16):  # 5 to 15 inclusive
        row_data = []
        for col_idx in range(0, 4):  # Columns 0 to 3
            cell_value = sheet.cell_value(row_idx, col_idx)
            row_data.append(cell_value)
        csvwriter.writerow(row_data)

print("Filtered data exported successfully")

Filtered data exported successfully

For advanced data selection, see Read Specific Rows and Columns with Python xlrd.

Handling Data Types and Formatting

Excel stores various data types. Dates, numbers, and text need proper handling. CSV files store everything as text.

This example shows type conversion. It ensures correct CSV output format.

import xlrd
import csv
from datetime import datetime

workbook = xlrd.open_workbook('data.xlsx')
sheet = workbook.sheet_by_index(0)

with open('formatted_data.csv', 'w', newline='') as csvfile:
    csvwriter = csv.writer(csvfile)
    
    for row_idx in range(sheet.nrows):
        formatted_row = []
        for col_idx in range(sheet.ncols):
            cell_value = sheet.cell_value(row_idx, col_idx)
            cell_type = sheet.cell_type(row_idx, col_idx)
            
            # Handle different data types
            if cell_type == xlrd.XL_CELL_DATE:
                # Convert Excel date to string
                date_tuple = xlrd.xldate_as_tuple(cell_value, workbook.datemode)
                formatted_value = datetime(*date_tuple).strftime('%Y-%m-%d')
                formatted_row.append(formatted_value)
            else:
                formatted_row.append(str(cell_value))
        
        csvwriter.writerow(formatted_row)

print("Data with proper formatting exported")

Data with proper formatting exported

The cell_type method identifies data types. Date conversion ensures proper CSV format.

Error Handling and Validation

Real-world files may have issues. Add error handling to your script. This prevents crashes.

This improved code includes file validation. It checks if files exist and are accessible.

import xlrd
import csv
import os

def excel_to_csv(excel_path, csv_path):
    try:
        # Check if Excel file exists
        if not os.path.exists(excel_path):
            raise FileNotFoundError(f"Excel file not found: {excel_path}")
        
        # Open workbook with error handling
        workbook = xlrd.open_workbook(excel_path)
        
        if workbook.nsheets == 0:
            raise ValueError("Excel file contains no sheets")
        
        sheet = workbook.sheet_by_index(0)
        
        # Create CSV directory if needed
        os.makedirs(os.path.dirname(csv_path), exist_ok=True)
        
        with open(csv_path, 'w', newline='', encoding='utf-8') as csvfile:
            csvwriter = csv.writer(csvfile)
            
            for row_idx in range(sheet.nrows):
                row_data = sheet.row_values(row_idx)
                csvwriter.writerow(row_data)
        
        print(f"Successfully converted {excel_path} to {csv_path}")
        return True
        
    except Exception as e:
        print(f"Error during conversion: {str(e)}")
        return False

# Usage example
excel_to_csv('data.xlsx', 'output/converted_data.csv')

Successfully converted data.xlsx to output/converted_data.csv

Error handling makes your script robust. It handles missing files and other issues gracefully.

Performance Optimization

Large Excel files need efficient processing. Optimize your code for better performance.

This example shows batch processing. It handles large files without memory issues.

import xlrd
import csv

def export_large_excel(excel_path, csv_path, batch_size=1000):
    workbook = xlrd.open_workbook(excel_path)
    sheet = workbook.sheet_by_index(0)
    
    with open(csv_path, 'w', newline='') as csvfile:
        csvwriter = csv.writer(csvfile)
        
        # Process in batches to manage memory
        for start_row in range(0, sheet.nrows, batch_size):
            end_row = min(start_row + batch_size, sheet.nrows)
            
            for row_idx in range(start_row, end_row):
                row_data = sheet.row_values(row_idx)
                csvwriter.writerow(row_data)
            
            print(f"Processed rows {start_row} to {end_row-1}")
    
    print("Large file export completed")

# Export with batch processing
export_large_excel('large_data.xlsx', 'large_output.csv')

Processed rows 0 to 999
Processed rows 1000 to 1999
Large file export completed

For more on handling big files, read Read Large Excel Files Efficiently with Python xlrd.

Conclusion

Python xlrd provides powerful Excel to CSV conversion. You can handle various scenarios effectively.

Start with basic conversion. Then add features like multiple sheets and data filtering. Error handling ensures reliability.

Automate your data workflow today. Save time and reduce manual errors. Python makes Excel processing simple and efficient.