Handle Multiple Spreadsheet Formats with Python pyexcel

Working with spreadsheets is common in data processing. Different formats exist. Excel, CSV, and JSON are popular. Each format has unique characteristics. Handling them individually can be tedious.

Python's pyexcel library simplifies this task. It provides a unified interface. You can work with multiple formats seamlessly. This saves time and reduces complexity.

What is pyexcel?

pyexcel is a Python library. It reads and writes various spreadsheet formats. It supports Excel, CSV, TSV, and more. The API is simple and consistent.

You don't need multiple libraries. pyexcel handles everything. It abstracts format differences. You focus on data manipulation.

If you need help setting up pyexcel, check our guide on Install pyexcel in Python with pip and Virtualenv.

Installing pyexcel

Installation is straightforward. Use pip package manager. Run the command below.

pip install pyexcel pyexcel-xlsx pyexcel-ods

The core package is pyexcel. Additional plugins handle specific formats. pyexcel-xlsx for Excel files. pyexcel-ods for OpenDocument spreadsheets.

If you encounter installation issues, see our article on Fix Python ImportError: No Module Named pyexcel.

Reading Different File Formats

pyexcel makes reading files easy. Use the get_array function. It works with any supported format. The method returns data as lists.

import pyexcel as pe

# Read Excel file
excel_data = pe.get_array(file_name="data.xlsx")
print("Excel data:", excel_data)

# Read CSV file
csv_data = pe.get_array(file_name="data.csv")
print("CSV data:", csv_data)

# Read ODS file
ods_data = pe.get_array(file_name="data.ods")
print("ODS data:", ods_data)

Excel data: [['Name', 'Age', 'City'], ['John', '30', 'New York'], ['Alice', '25', 'London']]
CSV data: [['Product', 'Price'], ['Laptop', '999'], ['Mouse', '25']]
ODS data: [['ID', 'Value'], ['1', '100'], ['2', '200']]

The same function works for all formats. You don't change code for different files. This is very efficient for batch processing.

Writing to Multiple Formats

Writing data is just as simple. Use the save_as function. Provide data and filename. pyexcel detects the format from extension.

import pyexcel as pe

# Sample data
data = [
    ['Name', 'Department', 'Salary'],
    ['Bob', 'IT', '50000'],
    ['Carol', 'HR', '45000']
]

# Save to different formats
pe.save_as(array=data, dest_file_name="output.xlsx")
pe.save_as(array=data, dest_file_name="output.csv")
pe.save_as(array=data, dest_file_name="output.ods")

This creates three files. Each has the same data. But in different formats. The process is automatic.

Converting Between Formats

Format conversion is a common need. pyexcel makes it trivial. Read from one format. Write to another.

import pyexcel as pe

# Convert Excel to CSV
data = pe.get_array(file_name="input.xlsx")
pe.save_as(array=data, dest_file_name="converted.csv")

# Convert CSV to JSON
data = pe.get_array(file_name="input.csv")
pe.save_as(array=data, dest_file_name="converted.json")

This is powerful for data pipelines. You can automate format conversions. No manual intervention needed.

For more conversion examples, visit our Python pyexcel Guide: Convert CSV XLSX JSON.

Working with Dictionaries

pyexcel can work with dictionaries too. Use get_dict function. It returns data as key-value pairs. This is useful for structured data.

import pyexcel as pe

# Read as dictionary
dict_data = pe.get_dict(file_name="data.xlsx")
print("Dictionary data:", dict_data)

# Sample output structure

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

Dictionary format is intuitive. Each column becomes a key. Values are lists of column data.

Handling Large Files

pyexcel can handle large datasets. Use the iget_array function. It returns a generator. This saves memory.

import pyexcel as pe

# Process large file efficiently
for row in pe.iget_array(file_name="large_data.xlsx"):
    print("Processing row:", row)
    # Your processing logic here

This reads one row at a time. Memory usage remains low. Essential for large files.

Real-World Example: Data Processing Pipeline

Let's create a practical example. We'll process sales data. Multiple formats are involved.

import pyexcel as pe

def process_sales_data():
    # Read from various sources
    excel_sales = pe.get_array(file_name="sales.xlsx")
    csv_sales = pe.get_array(file_name="sales.csv")
    
    # Combine data
    all_sales = excel_sales + csv_sales[1:]  # Skip header from second file
    
    # Save consolidated report
    pe.save_as(array=all_sales, dest_file_name="consolidated_sales.xlsx")
    pe.save_as(array=all_sales, dest_file_name="consolidated_sales.csv")
    
    print("Data processing completed successfully")

process_sales_data()

This script combines data from Excel and CSV. It creates consolidated reports. The process is automated and reliable.

Error Handling

Always include error handling. Files might be missing or corrupted. Use try-except blocks.

import pyexcel as pe

try:
    data = pe.get_array(file_name="missing_file.xlsx")
    pe.save_as(array=data, dest_file_name="output.csv")
except FileNotFoundError:
    print("Error: Input file not found")
except Exception as e:
    print(f"An error occurred: {str(e)}")

Proper error handling makes scripts robust. Users get meaningful messages. Debugging becomes easier.

Best Practices

Follow these tips for better results. Always specify file paths clearly. Use absolute paths for reliability.

Handle different data types carefully. pyexcel converts automatically. But verify important conversions.

Keep your pyexcel updated. New versions fix bugs. They add support for more formats.

For comprehensive usage examples, see our Python pyexcel Tutorial: Read Write Excel CSV Files.

Conclusion

pyexcel is a powerful tool. It simplifies spreadsheet handling. Multiple formats become manageable.

The library offers consistent API. Reading and writing is format-agnostic. Conversion between formats is straightforward.

pyexcel saves development time. You write less code. Maintenance becomes easier.

Start using pyexcel in your projects. Handle Excel, CSV, JSON and more. Make your data processing efficient and reliable.