In this article, let us look at how to find the first empty row of an excel sheet using python.
Excel files can be exhausting; with around 1,048,576 rows and 16,384 columns, they are frequently used to keep a record of entries from many subjects. Excel has also found use in data science.
With nearly 1,048,576 Rows, you might want to know if a Row is empty or not, which is a complex one. But Python has an effective solution for that, where we can just find it using a simple and shortcode.
sampleExcelFile.xlsx:
Python xlrd Module:
The xlrd module can be used to get data from a spreadsheet. It can be used to read, write, or modify data. In addition, the user may have to navigate through several sheets and get data based on some criteria, or edit some rows and columns, among other things.
To extract data from a spreadsheet, use the xlrd module.
The Python xlrd library, which is available on Pypi, is a library that allows users to read Excel files with the extensions “.xls” or “.xlsx.” Before you move into the code, be sure to install it.
NOTE:
Support for reading xlsx sheets has been explicitly removed from xlrd module.
Installation:
Type the below command to install the xlrd module:
pip install xlrd
Here to determine the empty row, we converted the code into a collection function.
These functions are extremely important because:
- Simple accessibility
- Easier to use
- Any part can be simply modified.
Functions Used:
- open_workbook(): It is used to open a workbook.
- sheet_by_index(): This function is used to access/open the sheet with a specific index number.
- nrows: It is used to give the total number of rows.
- ncols: It is used to give the total number of rows.
Python Program to Find the First Empty Row of an Excel file in Python
Step#1: Importing the module inside the try-except blocks
Import xlrd module using the import keyword to work with the excel file. We use the try-except blocks to handle the errors/exceptions.
# Use the try-except blocks to handle the exceptions
try:
# Import xlrd module using the import keyword
import xlrd
Step#2: Input the excel File
# create a function say firstEmptyrow() which finds the find empty row number in the given excel file
def firstEmptyrow():
# Take a variable that store the number of empty cells
count = 0
# Give the path of the excel file to be used as static input and store it in a variable
excelFilePath="sampleExcelFile.xlsx"
Step#3: Creating a workbook and a worksheet
# Pass the above file path to the open_workbook() function of the xlrd module # to create/open a workbook. workbook=xlrd.open_workbook(excelFilePath) # Open a first worksheet in the above workbook using the sheet_by_index() function # by passing the index value 0 as argument to it. # you can open any other sheets just by changing the index number(starts from 0) worksheet=workbook.sheet_by_index(0) # Iterate till the number of rows in a worksheet using the nrows attribute # The nrows attribute is used to extract the number of rows
Step#4: Finding the first empty row of an excel file
for excelrow in range(worksheet.nrows) :
# Iterate till the number of columns in a worksheet using the ncols attribute
# using the nested for loop
# The ncols attribute is used to extract the number of columns
for excelcolumn in range (worksheet.ncols) :
ptrow=excelrow
# Check if the current cell value with row number as ptrow and column as iterator value (excelcolumn) is equal to null
if(worksheet.cell_value(ptrow,excelcolumn)=="") :
# If it is null then increment the count of blank cell by 1
count +=1
# Check if number of blank cell value is equal to number of cells then it means the whole row is blank so it is the first empty row
if (count==worksheet.ncols):
# Return the current row number
return ptrow
else:
continue
Complete Code
Here is the complete code for you.
Approach:
- Use the try-except blocks to handle the exceptions
- Import xlrd module using the import keyword
- create a function say firstEmptyrow() which finds the find empty row number in the given excel file
- Take a variable that stores the number of empty cells
- Give the path of the excel file to be used as static input and store it in a variable
- Pass the above file path to the open_workbook() function of the xlrd module
to create/open a workbook. - Open a first worksheet in the above workbook using the sheet_by_index() function by passing the index value 0 as an argument to it.
- you can open any other sheets just by changing the index number(starts from 0)
- Iterate till the number of rows in a worksheet using the nrows attribute
- The nrows attribute is used to extract the number of rows
- Iterate till the number of columns in a worksheet using the ncols attribute using the nested for loop
- The ncols attribute is used to extract the number of columns
- Check if the current cell value with row number as ptrow and column as iterator value (excelcolumn) is equal to null
- If it is null then increment the count of blank cell by 1
- Check if the number of blank cell value is equal to the number of cells then it means the whole row is blank so it is the first empty row
- Return the current row number
- Call the above-created firstEmptyrow() function that returns the index of the first empty row
- Increment the row number by 1 as it is 0 based index
- Print the index where the first empty row is found
- If any typeError arrives then it means no empty row is found.
- The Exit of the Program.
Below is the implementation:
# Use the try-except blocks to handle the exceptions
try:
# Import xlrd module using the import keyword
import xlrd
# create a function say firstEmptyrow() which finds the find empty row number in the given excel file
def firstEmptyrow():
# Take a variable that store the number of empty cells
count = 0
# Give the path of the excel file to be used as static input and store it in a variable
excelFilePath="sampleEx.xlsx"
# Pass the above file path to the open_workbook() function of the xlrd module
# to create/open a workbook.
workbook=xlrd.open_workbook(excelFilePath)
# Open a first worksheet in the above workbook using the sheet_by_index() function
# by passing the index value 0 as argument to it.
# you can open any other sheets just by changing the index number(starts from 0)
worksheet=workbook.sheet_by_index(0)
# Iterate till the number of rows in a worksheet using the nrows attribute
# The nrows attribute is used to extract the number of rows
for excelrow in range(worksheet.nrows) :
# Iterate till the number of columns in a worksheet using the ncols attribute
# using the nested for loop
# The ncols attribute is used to extract the number of columns
for excelcolumn in range (worksheet.ncols) :
ptrow=excelrow
# Check if the current cell value with row number as ptrow and column as iterator value (excelcolumn) is equal to null
if(worksheet.cell_value(ptrow,excelcolumn)=="") :
# If it is null then increment the count of blank cell by 1
count +=1
# Check if number of blank cell value is equal to number of cells then it means the whole row is blank so it is the first empty row
if (count==worksheet.ncols):
# Return the current row number
return ptrow
else:
continue
# Call the above created firstEmptyrow() function that returns the index of the first empty row
firstEmptyRowNumber=firstEmptyrow()
# Increment the row number by 1 as it is 0 based index
firstEmptyRowNumber=firstEmptyRowNumber+1
# Print the index where the first empty row is found
print("The first Empty Row is found at the index:", firstEmptyRowNumber)
# If any typeError arrives then it means no empty row is found
except TypeError:
print("There is NO empty Row!!")
Output:
The first Empty Row is found at the index: 7