Python pyexcel Tutorial: Read Write Excel CSV Files

Data handling is crucial in programming. Python offers many libraries for this task. Pyexcel is one such powerful tool.

It simplifies working with spreadsheet files. You can read and write Excel and CSV formats easily. This tutorial covers pyexcel basics.

What is pyexcel?

Pyexcel is a Python library. It provides a single API for various spreadsheet formats. This includes Excel, CSV, and others.

The library is lightweight and easy to use. It does not require Microsoft Excel to be installed. This makes it cross-platform compatible.

You might encounter installation issues sometimes. Our guide on Install pyexcel in Python with pip and Virtualenv can help.

Installing pyexcel

First, you need to install the library. Use pip for installation. Run this command in your terminal.

pip install pyexcel pyexcel-xls pyexcel-xlsx

This installs the core library. It also adds support for Excel formats. For CSV files, the core library is sufficient.

If you face import errors later, check our article on Fix Python ImportError: No Module Named pyexcel.

Reading Excel Files

Pyexcel makes reading Excel files straightforward. Use the get_array function. It reads data into a list of lists.

import pyexcel as pe

# Read an Excel file
data_array = pe.get_array(file_name="sample.xlsx")

# Print the data
for row in data_array:
    print(row)

['Name', 'Age', 'City']
['Alice', 30, 'New York']
['Bob', 25, 'London']
['Charlie', 35, 'Paris']

Each row becomes a list. The entire sheet is a list of these rows. This structure is easy to process.

Reading CSV Files

Reading CSV files is similar. Use the same get_array method. Pyexcel detects the file format automatically.

import pyexcel as pe

# Read a CSV file
csv_data = pe.get_array(file_name="data.csv")

# Display the content
for row in csv_data:
    print(row)

['Product', 'Price', 'Stock']
['Laptop', 999, 15]
['Mouse', 25, 100]
['Keyboard', 75, 50]

The method works identically for both formats. This consistency is a key feature of pyexcel.

Writing Excel Files

Creating Excel files is just as simple. Use the save_as function. Provide your data and a filename.

import pyexcel as pe

# Sample data
data = [
    ['Employee', 'Department', 'Salary'],
    ['John Doe', 'IT', 50000],
    ['Jane Smith', 'HR', 45000],
    ['Mike Johnson', 'Finance', 60000]
]

# Save to Excel file
pe.save_as(array=data, dest_file_name="employees.xlsx")
print("Excel file created successfully!")

Excel file created successfully!

The function creates the file in the current directory. You can open it with any spreadsheet application.

Writing CSV Files

Writing CSV files uses the same approach. Change the file extension to .csv. Pyexcel handles the format automatically.

import pyexcel as pe

# Sample data
inventory = [
    ['Item', 'Quantity', 'Location'],
    ['Printer', 5, 'Warehouse A'],
    ['Monitor', 12, 'Warehouse B'],
    ['Desk', 8, 'Warehouse C']
]

# Save to CSV file
pe.save_as(array=inventory, dest_file_name="inventory.csv")
print("CSV file created successfully!")

CSV file created successfully!

The resulting CSV file is standard compliant. It can be imported into any spreadsheet software.

Working with Dictionaries

Pyexcel can also work with dictionaries. This is useful for structured data. Use get_dict for reading.

import pyexcel as pe

# Read into dictionary
data_dict = pe.get_dict(file_name="sample.xlsx")

print(data_dict)

{'Name': ['Alice', 'Bob', 'Charlie'], 'Age': [30, 25, 35], 'City': ['New York', 'London', 'Paris']}

Each column becomes a dictionary key. The values are lists of column data. This format is great for pandas integration.

Advanced: Using Sheets

Excel files can have multiple sheets. Pyexcel handles this with get_book. It returns a book object.

import pyexcel as pe

# Get the entire book
book = pe.get_book(file_name="workbook.xlsx")

# Access specific sheet by name
sheet1 = book["Sheet1"]
sheet1_data = sheet1.to_array()

print("Sheet1 data:")
for row in sheet1_data:
    print(row)

Sheet1 data:
['Data', 'Value']
['A', 10]
['B', 20]
['C', 30]

This allows working with complex Excel files. You can process multiple sheets in one operation.

Error Handling

Always include error handling in file operations. This prevents crashes from missing files or permission issues.

import pyexcel as pe

try:
    data = pe.get_array(file_name="nonexistent.xlsx")
    print("File read successfully")
except FileNotFoundError:
    print("Error: File not found")
except Exception as e:
    print(f"An error occurred: {str(e)}")

Error: File not found

Proper error handling makes your code more robust. It provides better user experience.

Conclusion

Pyexcel is a versatile library for spreadsheet operations. It offers a simple API for reading and writing files.

You can handle both Excel and CSV formats consistently. The library is perfect for data processing tasks.

Remember to install required dependencies. Handle errors gracefully in your applications.

Pyexcel simplifies working with tabular data in Python. It's a valuable tool for any developer's toolkit.