Python xlrd Tutorial: Read Excel XLS Files Step by Step

Python xlrd library reads Excel files. It extracts data from spreadsheets. This tutorial teaches you step by step.

You will learn installation and usage. We cover basic to advanced features. Practical examples included throughout.

What is xlrd?

Xlrd is a Python library. It reads Excel files (.xls format). It extracts data and metadata.

The library supports Excel 2003 format. It handles formulas and cell information. Xlrd works on all operating systems.

Many developers use xlrd for data processing. It integrates with other Python libraries. Data analysis becomes easier.

Installing xlrd

First, install xlrd using pip. Open your command line. Run the installation command.

pip install xlrd

For detailed installation guidance, see our Install xlrd and xlwt in Python tutorial.

Verify installation success. Import the library in Python. Check for errors.

import xlrd
print("xlrd installed successfully")

xlrd installed successfully

Basic xlrd Usage

Start with opening an Excel file. Use the open_workbook function. It returns a workbook object.

import xlrd

# Open Excel file
workbook = xlrd.open_workbook('sample.xls')
print("Workbook opened successfully")

Workbook opened successfully

The workbook contains sheets. Access them by index or name. Get sheet information.

Working with Sheets

Excel files have multiple sheets. List all sheet names. Access specific sheets.

# Get sheet names
sheet_names = workbook.sheet_names()
print("Sheet names:", sheet_names)

# Access first sheet
sheet = workbook.sheet_by_index(0)
print("First sheet name:", sheet.name)

Sheet names: ['Sheet1', 'Data']
First sheet name: Sheet1

Sheets have rows and columns. Get dimensions using properties. Know the data size.

# Get sheet dimensions
print("Number of rows:", sheet.nrows)
print("Number of columns:", sheet.ncols)

Number of rows: 10
Number of columns: 5

Reading Cell Data

Read individual cell values. Use row and column indices. Different data types supported.

# Read cell value
cell_value = sheet.cell_value(0, 0)  # Row 0, Column 0
print("Cell A1:", cell_value)

# Read different data types
for row in range(3):
    for col in range(3):
        value = sheet.cell_value(row, col)
        print(f"Cell ({row},{col}): {value}")

Cell A1: Name
Cell (0,0): Name
Cell (0,1): Age
Cell (0,2): City

Cells contain different data types. Xlrd handles numbers, text, and dates. Automatic conversion occurs.

Reading Entire Rows and Columns

Process data efficiently. Read complete rows or columns. Use built-in methods.

# Read entire row
row_data = sheet.row_values(0)  # First row
print("Row 0:", row_data)

# Read entire column
col_data = sheet.col_values(0)  # First column
print("Column 0:", col_data[:3])  # First 3 elements

Row 0: ['Name', 'Age', 'City', 'Salary']
Column 0: ['Name', 'John', 'Alice']

These methods return Python lists. Easy to process with loops. Data manipulation becomes simple.

Handling Different Data Types

Excel stores various data types. Xlrd provides type information. Use cell type constants.

# Check cell type
cell_type = sheet.cell_type(1, 1)  # Row 1, Column 1
print("Cell type:", cell_type)

# Common type constants
print("Empty cell:", xlrd.XL_CELL_EMPTY)
print("Text cell:", xlrd.XL_CELL_TEXT)
print("Number cell:", xlrd.XL_CELL_NUMBER)

Cell type: 2
Empty cell: 0
Text cell: 1
Number cell: 2

Understanding types helps data processing. Handle each type appropriately. Avoid conversion errors.

Working with Dates

Excel dates need special handling. They are stored as numbers. Convert to Python dates.

# Check if cell contains date
if sheet.cell_type(2, 3) == xlrd.XL_CELL_NUMBER:
    # Convert Excel date to Python date
    excel_date = sheet.cell_value(2, 3)
    python_date = xlrd.xldate_as_datetime(excel_date, workbook.datemode)
    print("Date:", python_date)

Date: 2023-05-15 00:00:00

Date conversion requires datemode. Workbook stores this information. Always use correct datemode.

Practical Example: Read Employee Data

Let's create a complete example. Read employee data from Excel. Process and display information.

import xlrd

def read_employee_data(filename):
    workbook = xlrd.open_workbook(filename)
    sheet = workbook.sheet_by_index(0)
    
    employees = []
    # Skip header row, start from row 1
    for row_idx in range(1, sheet.nrows):
        employee = {
            'name': sheet.cell_value(row_idx, 0),
            'age': int(sheet.cell_value(row_idx, 1)),
            'city': sheet.cell_value(row_idx, 2),
            'salary': float(sheet.cell_value(row_idx, 3))
        }
        employees.append(employee)
    
    return employees

# Usage
employees = read_employee_data('employees.xls')
for emp in employees:
    print(f"{emp['name']}: {emp['age']} years, {emp['city']}, ${emp['salary']}")

John: 28 years, New York, $50000.0
Alice: 32 years, London, $60000.0
Bob: 25 years, Tokyo, $45000.0

This example shows real-world usage. Data extraction becomes automated. Business processes benefit greatly.

Error Handling

Always handle potential errors. Files might not exist. Data might be corrupted.

import xlrd

try:
    workbook = xlrd.open_workbook('nonexistent.xls')
except FileNotFoundError:
    print("Error: File not found")
except xlrd.XLRDError:
    print("Error: Cannot read Excel file")

Error: File not found

Proper error handling prevents crashes. Users get helpful messages. Debugging becomes easier.

Limitations and Alternatives

Xlrd has some limitations. It only reads .xls files. Newer .xlsx format not supported.

For .xlsx files, use openpyxl. It handles modern Excel formats. Installation similar to xlrd.

If you need to remove xlrd, follow our guide on How to Uninstall Xlrd in Python.

Best Practices

Follow these best practices. They improve code quality and performance.

Always close files properly. Use context managers when possible. Handle exceptions gracefully.

Process large files in chunks. Avoid loading everything into memory. Use efficient looping techniques.

Validate data after reading. Check for missing values. Ensure data types are correct.

Conclusion

Xlrd is powerful for Excel reading. It extracts data from .xls files easily. Integration with Python is seamless.

You learned installation and basic usage. We covered reading cells and sheets. Date handling and error management included.

For writing Excel files, check our Install xlrd and xlwt in Python Easily guide. It covers both reading and writing.

Start using xlrd in your projects. Automate Excel data processing. Save time and reduce errors.