Finding first empty column in an excel sheet using Python

In this tutorial, we will see how to find the first empty column in an excel file in Python using xlrd.

I have designed this program specifically to guide you through, finding an empty column in an excel sheet using python’s libraries, provided there is an empty column present. The python codes are very flexible so it doesn’t matter if you use Pycharm or Python IDLE, as long as you already have the “xlrd” library installed on your machine.

Find the first empty column in an excel file/sheet in Python using xlrd

The xlrd library is a python library, which can be found on Pypi.org Which helps us to read Excel files. Although the xlrd library can only be used for reading the excel files, there are different libraries for editing the file too.
*Note: The xlrd library can only handle excel files i.e .xls or .xlsx .

I have divided the code by making a function, the advantages of using a function are:

  1. Quicker to use
  2. Modifications to any part can be easily made
  3. Easier accessibility

Also learn: Find the first empty row of an excel file in Python

To find the empty cell in Python, we implement :

try:
    import xlrd
    def empcol() :
        count = 0
        path="C:/Users/user/Desktop/New folder/poj.xlsx"
        wb=xlrd.open_workbook(path)
        sheet=wb.sheet_by_index(0)
        for row in range(sheet.nrows) :
            for column in range (sheet.ncols) :
                ptrow=column
                if(sheet.cell_value(row,column)=="") :
                    count +=1
                if(count==sheet.nrows) :
                    return ptrow
                else :
                    continue


    colnum=empcol()
    colnum=colnum+1
    print(f'The presence of a empty column is at :{colnum}')

except TypeError :
    print("No empty column found")

Output :

*I had a empty column at index 5
"The presence of the first empty column is :6"

The first thing we do is, create a try block and import the xlrd library inside it, which is followed up by creating the function ’empcol()’. This function is going to base through which we will find the no of the empty column. We initialize the counter ‘count’ and set the path to the excel file (hold down shift, and right-click on the file and click on copy path). We then open the workbook using the ‘xlrd.open_workbook’ command and also initialize the sheet. We then use for loops to converse through each cell in the excel file. The first for loop is for Rows, while the second is for the Columns. The next step is to initialize ‘ptcol’ with the value of the column and check if the corresponding cell is empty or not. If empty we iterate counter with plus one. The next step is checking if counter equals to number of columns. If true we return the value of the ptcol to main.
In the main, we get the index of empty column and add one to it to get the value of the columun.
We have used a the try and except block, just in case the sheet doesn’t have a single empty column. This would return the value of Null/false. So to avoid it we implement the except block and set it to print no empty column found.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *