- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
|Virat Kohli||33||Batsman||India||Royal Challengers Bangalore||6300||20|
|Bhuvaneshwar Kumar||34||Batsman||India||Sun Risers Hyderabad||333||140|
|Mahendra Singh Dhoni||39||Batsman||India||Chennai Super Kings||4500||0|
|Rashid Khan||28||Bowler||Afghanistan||Gujarat Titans||500||130|
|David Warner||34||Batsman||Austrailia||Delhi Capitals||5500||12|
|Kieron Pollard||35||All rounder||West Indies||Mumbai Indians||3000||67|
|Kagiso Rabada||29||Bowler||South Africa||Lucknow Capitals||335||111|
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.
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")
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.
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.
- Related Articles
- How to create an empty file using Python?
- How to add timestamp to excel file in Python?
- Write a Python program to find the average of first row in a Panel
- Python - Writing to an excel file using openpyxl module
- How to alternate row colour in an Excel Pivot Table?
- How to Auto-Highlight the Row and Column of an Active Cell in Excel?
- Write a Python program to read an Excel data from file and read all rows of first and last columns
- Golang Program to create an empty file
- How to create an empty list in Python?
- How to write an empty function in Python?
- How to create an empty class in Python?
- How to create an empty dictionary in Python?
- How to create an empty tuple in Python?
- How to read only the first line of a file with Python?
- Read and Write to an excel file using Python openpyxl module