How to find the first empty row of an excel file in Python?

In this article, we will show you how to find the index of the first empty row in an Excel file using Python. This is useful for data processing tasks where you need to identify gaps in your data or find where to insert new records.

We'll work with a sample Excel file called sampleTutorialsPoint.xlsx containing cricket player data with some empty rows ?

Using xlrd Module

First, install the required module ?

pip install xlrd

The xlrd module allows us to read Excel files and extract data. Here's how to find the first empty row ?

import xlrd

def find_first_empty_row(file_path):
    """
    Returns the index of the first empty row in an Excel file.
    Returns None if no empty rows are found.
    """
    try:
        # Open the workbook
        workbook = xlrd.open_workbook(file_path)
        
        # Get the first worksheet
        worksheet = workbook.sheet_by_index(0)
        
        # Iterate through all rows
        for row_index in range(worksheet.nrows):
            empty_cells_count = 0
            
            # Check each cell in the current row
            for col_index in range(worksheet.ncols):
                cell_value = worksheet.cell_value(row_index, col_index)
                if cell_value == "":
                    empty_cells_count += 1
            
            # If all cells in the row are empty, we found our answer
            if empty_cells_count == worksheet.ncols:
                return row_index
        
        return None  # No empty row found
        
    except Exception as e:
        print(f"Error reading file: {e}")
        return None

# Example usage
file_path = "sampleTutorialsPoint.xlsx"
empty_row_index = find_first_empty_row(file_path)

if empty_row_index is not None:
    # Convert from 0-based to 1-based indexing for user display
    row_number = empty_row_index + 1
    print(f"The first empty row is found at row number: {row_number}")
else:
    print("No empty rows found in the Excel file")

The first empty row is found at row number: 6

Using pandas (Alternative Method)

You can also use pandas for a more modern approach ?

import pandas as pd

def find_empty_row_pandas(file_path):
    """
    Find first empty row using pandas.
    """
    try:
        # Read the Excel file
        df = pd.read_excel(file_path)
        
        # Find rows where all values are NaN
        empty_rows = df.isnull().all(axis=1)
        
        # Get the first empty row index
        first_empty = empty_rows.idxmax() if empty_rows.any() else None
        
        return first_empty
        
    except Exception as e:
        print(f"Error: {e}")
        return None

# Example usage
file_path = "sampleTutorialsPoint.xlsx"
empty_row = find_empty_row_pandas(file_path)

if empty_row is not None:
    print(f"First empty row found at index: {empty_row} (pandas 0-based)")
    print(f"Row number: {empty_row + 1}")
else:
    print("No empty rows found")

How It Works

The algorithm follows these steps:

• Open the Excel file: Use xlrd.open_workbook() to create a workbook object
• Access the worksheet: Get the first sheet using sheet_by_index(0)
• Iterate through rows: Loop through each row using the nrows attribute
• Count empty cells: For each row, count cells with empty values
• Check if row is empty: If empty cell count equals total columns (ncols), the row is empty
• Return the index: Return the first row index where this condition is met

Key Points

• Excel uses 1-based indexing for display, but Python uses 0-based indexing
• Empty cells are detected by comparing cell values to an empty string ""
• The function returns None if no empty rows are found
• Always handle exceptions when working with file operations

Conclusion

Finding empty rows in Excel files is straightforward using Python's xlrd module or pandas. The xlrd approach gives you low-level control, while pandas offers a more concise solution for data analysis workflows.