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 a first empty row of a given excel file using python.

Assume we have taken an excel file with the name demoTutorialsPoint.xlsx containing some random data with some empty rows. We will return the index of the first empty row in an excel worksheet.

sampleTutorialsPoint.xlsx

Algorithm (Steps)

Following are the Algorithm/steps to be followed to perform the desired task −

• Handle the Errors/exceptions by writing the code inside of try-except blocks.

• Use the import keyword, to import the xlrd module (To read data from a spreadsheet, use the xlrd module. It has the ability to read, write, and alter data. Furthermore, the user may be required to traverse numerous sheets to obtain data based on certain criteria or to alter specific rows and columns, among other things. Use the xlrd module to extract data from a spreadsheet).

pip install xlrd

• Create a function with some random name say firstEmptyRow(). This function returns the index of the first empty row found in an input excel file.

• Create a variable to store the count of empty cells present in an excel sheet.

• Create a variable to store the path of the input excel file.

• To create a workbook object, pass the input excel file to the xlrd module's open_workbook() function (opens a workbook).

• Using the sheet_by_index() method(opens a sheet with the specific index), open the first worksheet in the given workbook (here 0 represents first sheet).

• Traverse in all the rows of the worksheet using the for loop. The nrows attribute is used to get the total number of rows.

• Using the nested for loop, Traverse all the columns of the worksheet using another nested for loop. The ncols attribute is used to get the total number of columns.

• Using the cell_value() function (gives the cell's value in the specified row and column)  and the if conditional statement, determine whether the cell is blank or NOT.

• Increment the empty cells count by 1 if it is a blank cell.

• Check to see if the number of empty cells equals the number of columns in the excel file (This specifies the row is a blank row) and if it is true then return the index of the current row.

• Call the firstEmptyRow() function (which gives the index of the row number) and create a variable to store it.

• Increment the row number by 1 as it is 0-based indexing.

• Print the result of the blank row.

• If there is no blank row, the function does not return anything, resulting in an exception, which we handle in the except block.

Example

The following program prints the index of the first empty row found in an input excel file, it there are no empty rows in the excel file it handles the error by printing random text0

try:
   # import xlrd module
   import xlrd

   # This function returns the index of the first empty row
   def firstEmptyRow():

   # storing the count of empty cells
   emptycellscount = 0

   # input excel file path
   inputExcelFile ="sampleTutorialsPoint.xlsx"

   # creating/opening a workbook
   new_workbook = xlrd.open_workbook(inputExcelFile)

   # Opening the first worksheet in the workbook
   firstWorksheet =new_workbook.sheet_by_index(0)

   # Traversing in all the rows of the worksheet
   # (nrows is used to get the number of rows)
   for each_row in range(firstWorksheet.nrows) :

      # Traversing in all the columns of the worksheet
      # (ncols is used to get the number of columns)
      for each_col in range (firstWorksheet.ncols) :

         # Checking whether the cell is a blank/empty cell
         if(firstWorksheet.cell_value(each_row, each_col)=="") :
            emptycellscount +=1

         # Checking whether the number of empty cells is equal to the number of columns

         # (If they are equal, the row is blank)
         if (emptycellscount==firstWorksheet.ncols):
            return each_row
  
   # Calling the above firstEmptyRow() function to get the index of the first blank row
   row_number=firstEmptyRow()

   # Increment the row number by 1(as it is 0-based indexing)
   row_number=row_number+1
   print("The first empty row is found in row number:", row_number)

# If the function doesn't return anything then there is no blank row
except:
   print("Hey User! there is No Empty Row in a given excel file")

Output

On executing, the above program will generate the following output −

The first empty row is found in row number: 6

In our program, we used a sample excel file with dummy data. The excel file has blank rows. We used a variable to count the number of empty cells, and we used the for loop to traverse the excel file cell by cell, checking whether it was an empty cell or not, counting that cell, and determining whether the number of empty cells equaled the number of columns (this is the condition for blank row), and finally printing the row number of the first blank row.

Conclusion

We learned how to utilize the xlrd module to create a workbook from an excel file and a worksheet from the selected sheet. We also learned how to traverse the excel file cell by cell, how to use the number of columns logic to determine whether a row is empty or not, and how to handle an exception if the function does not return anything.