How to find the first empty row of an excel file in Python
By MrigankaThis tutorial will guide you to find the first empty row in an excel file using Python.
Excel files can be tiring, with about 1,048,576 Rows and 16,384 Columns they are often used to store entries of
various subjects. Excel also has its application in Data Science too.
With about almost 1,048,576 Rows you might want to find out, if a Row is empty or not, well good news for you, I have a very simple and effective solution for you, which I’ll explain in a moment, but first, we need to import the “xlrd” library for it.
The “xlrd” library in python available on Pypi, is a library which would allow the user to read an Excel file, it can be used in the excel extensions of “.xls or .xlsx“. Make sure to install it before proceeding any further.
So in order to find the empty row; here at code speedy we have converted the program into collection function
The importance of functions are:
- Easy accessibility
- Quicker to use
- Modifications to any part can be easily made
Python program to find the first empty row of an excel file
try:
import xlrd
def rtrow():
count = 0
filo="excel.xlsx"
wb=xlrd.open_workbook(filo)
sheet=wb.sheet_by_index(0)
for i in range(sheet.nrows) :
for j in range (sheet.ncols) :
ptrow=i
if(sheet.cell_value(ptrow,j)=="") :
count +=1
if (count==sheet.ncols):
return ptrow
else:
continue
rownum=rtrow()
rownum=rownum+1
print(f"The presence of an empty row is at :{rownum}")
except TypeError:
print("No empty row found")Note for a better understanding :
Provided an empty row is present, the very first thing we do is to create a try block and import the “xlrd” library in it. We then create a function called “rtrow()” to get the value of the empty row. We set the variable count to zero (count is a variable to check the number of cells that are empty).
We then use the variable ‘filo’ to store the full-path of the excel file (the path will vary from machine to machine). Then we use the variable “wb” to open a workbook using the “.open_workbook(path)” statement. Then we set the use the variable “sheet” to store the sheet no. (sheet number will vary from workbook to workbook, set the sheet number using the correct index value). We use a for loop with variable “i”, to transverse between each row, and again use the for loop of “j” to transverse between each column; which gives us access to almost every cell. Set the counter “ptrow” to the index value of the row, and check if the cell value is empty if it is found to be empty, iterate counter with 1. Next, we check if the no of empty cells found is equal to the total no of rows. If they are equal we return the index value of the row, else we go over to the next row.
We call the function from main, and store the Index value in variable “rownum”. The next step is to add 1 to the index to store the actual row number and print it out.
Now if there isn’t a single empty row our program would crash since the program would return null/false. Therefore to avoid it. We have implemented the try and except block, where we have passed the type of error and executed the print statement of having no empty row present.
Output:
I had an empty row at index position 2
output: The presence of an empty row is at :3 .
Commands used:
import xlrd :To import the xlrd library
xlrd.open_workbook(path) : To open the workbook
.sheet_by_index(number) : To open the Sheet with that particular index number.
sheet.nrows : Gives us the total number of Rows.
sheet.ncols : Gives us the total number of Columns.
* Note: Make sure that xlrd library is already installed on your machine.
Thanks for the idea. I offer a little update 😉
“else” in “for in” loop hits only in the end of the loop.
If we break loop as soon as we find not empty cell, then the logic works as it is supposed to.
try:
import xlrd
def rtrow():
filo=”excel.xlsx”
wb=xlrd.open_workbook(filo)
sheet=wb.sheet_by_index(0)
for i in range(sheet.nrows):
is_empty_row = False
for j in range (sheet.ncols) :
if sheet.cell_value(i, j) != “”:
break
else:
is_empty_row = True
if is_empty_row:
return i
rownum=rtrow()
rownum=rownum+1
print(f”The presence of an empty row is at :{rownum}”)
except TypeError:
print(“No empty row found”)