How to Find the First Empty Row of an Excel File in Python

When working with data in Excel files, one common task is to find the first empty row where new data can be added. Whether you’re automating a data entry process or performing data analysis, Python offers several methods to accomplish this task efficiently. In this article, we’ll explore different approaches to finding the first empty row of an Excel file using Python.

Excel sheet ( testdata.xlsx )

The Excel sheet (testdata.xlsx) that we utilize for various approaches is provided below. The third row is the first empty row in the Excel sheet.

testdata.xlsx

Find the First Empty Row of an Excel File in Python

Below are some of the ways by which we can find the first empty row of an excel file in Python:

1. Using Openpyxl Library
2. Using Pandas Library

Find the First Empty Row of an Excel File Using Openpyxl Library

In this example, a Python function named find_empty_row utilizes the openpyxl library to locate the first empty row in a specified Excel file and sheet by iterating through each row and checking if all cells in a row have a None value. If an empty row is found, its index is returned; otherwise, the index of the next available row is returned.

Python

from openpyxl import load_workbook

def find_empty_row(file_path, sheet_name):
    workbook = load_workbook(file_path)
    sheet = workbook[sheet_name]

    for row in range(1, sheet.max_row + 1):
        if all([cell.value is None for cell in sheet[row]]):
            return row

    return sheet.max_row + 1

empty_row = find_empty_row("./testdata.xlsx", "Sheet")
print("The first empty row is:", empty_row)

Output:

Output 1

Find the First Empty Row of an Excel File Using Pandas Library

In this example, a Python function named find_empty_row reads an Excel file using Pandas, checks for rows with all NaN values, and returns the index of the first such row plus two, or the length of the DataFrame plus one if no empty row is found. Finally, it prints the index of the first empty row.

Python

import pandas as pd

def find_empty_row(file_path, sheet_name):
    df = pd.read_excel(file_path, sheet_name=sheet_name)
    index = df.index[df.isnull().all(axis=1)].tolist()

    if index:
        return index[0] + 2
    else:
        return len(df) + 1

empty_row = find_empty_row("./testdata.xlsx", "Sheet")
print("The first empty row is: ", empty_row)

Output:

Output 2