Python Read Excel File

Introduction to Python Read Excel File

Python programming language allows developers to access an Excel sheet file where a developer can execute operations like read and write file. There are many Libraries that are responsible for allowing developers to execute these actions. When we have to deal with file input-output operations, specifically Excel sheets, then python provides the number of libraries that holds a list of functions to work with the file. We will use the “xlrd” Python Library to read the excel sheets. The xlrd library will extract data from an excel sheets on any platform, Unix or Windows or Mac. It also supports Excel Dates Formats and is aware of Unicode formats.

How to Read Excel File in Python Using Various Methods?

Moving on, we will demonstrate the working of reading an excel sheet within python, using the python excel library. We will use the xlrd library provided by Python to read our excel file and carry out the operations. Our excel file, that we will be using for demonstration, will have a single sheet, within which we will have three columns and 5 rows, rows including the header of the file.

Method #1

Our first example is, where we attempt to print the name of the first column within our excel sheet, the code is as follows.

Code:

import xlrd 	
fpath = (r "C:\Users\KshirsagarS\Downloads\read_excel_py.xlsx") 
workbook = xlrd.open_workbook(fpath) 
excel_sheet = workbook.sheet_by_index(0) 	
print(excel_sheet.cell_value(0, 0)) 

Code Explanation: Started with importing the most important library, which is xlrd. Followed by a variable to hold the file, where we have passed in path of the excel file with reading permissions. Then we have another variable to hold the content of the sheet, here we have used open_workbook function and passed the file path. We then have data by the sheet and implement the sheet_by_index function and finally, print the value. The sheet_by_index will go to the 0^th column of the 0^th row and pick the data and print it in the final line. Refer the below-attached screenshot.

Output:

Output Explanation: As you can see, the output is “Name”, which is the value of the first row of our first column. Then we have our next example, where we will implement the xlrd library to get the number of rows that are filled with data inside an excel sheet. The code is as follows.

Method #2

Code:

import xlrd 	
fpath = (r "C:\Users\KshirsagarS\Downloads\read_excel_py.xlsx") 
workbook = xlrd.open_workbook(fpath) 
excel_sheet = workbook.sheet_by_index(0) 
excel_sheet.cell_value(0, 0)
print(excel_sheet.nrows)

Code Explanation: Similar to earlier code, started with importing xlrd library and declared the path for the input file. We then have our workbook with the sheet content and with an excel sheet we read the content by index. Then we call in cell_value() function and pass 0^th column and 0^th row. We expect the out to be the number of rows, that’s why our last line is, nrows, which can be described as a number of rows. Refer to the below attached screenshot.

Output:

Output Explanation: Now, as expected the output is 5, which is the total number of rows our sheet has, and this includes the header row. So, moving on to the next example, we will demonstrate the working of python code, which will read an excel file and print the total number of columns the excel sheet has.

Method #3

Code:

import xlrd 	
fpath = (r "C:\Users\KshirsagarS\Downloads\read_excel_py.xlsx") 
workbook = xlrd.open_workbook(fpath) 
excel_sheet = workbook.sheet_by_index(0) 
excel_sheet.cell_value(0, 0) 	
print(excel_sheet.ncols)

Code Explanation: Similar to our earlier examples, we have our necessary variables and functions, then we have an excel sheet which holds the data and we have passed it to the sheet by index and then to cell_value() function, along with two parameters. Then we have our final print statement, which will print the number of columns our excel sheet has and it will be done by the ncols. The ncols can be seen as the number of columns and are used to find out the number of columns any excel spreadsheet has. Refer the below-attached screenshot for output:

Output:

Output Explanation: The output is 3, meaning out the excel sheet has a total of three columns. Until now, we demonstrated with columns and rows for trying out excel operations, for our next example, we will attempt to read data from a whole column. The code for reading the column is as below:

Method #4

Code:

import xlrd 
fpath = (r "C:\Users\KshirsagarS\Downloads\read_excel_py.xlsx") 
workbook = xlrd.open_workbook(fpath) 
excel_sheet = workbook.sheet_by_index(0) 
excel_sheet.cell_value(0, 0)
for i in range(excel_sheet.nrows): 
    print(excel_sheet.cell_value(i, 0))

Code Explanation: Without any changes in our initial part of code, we have file pat, then our workbook and excel sheet. Then we our values by the cell. Our loop will check for every single cell. They will work in a way that will print out the data values from the first column of every row, meaning the whole of the first column. We expect the output to be of total of 5 rows, and those will be the names we have in our sheet. Refer the screenshot for the proper understanding of the output:

Output:

Output Explanation: And just like we expected, the output is of five rows, starting with the header row, which is also to name of the column, then we have four values, which are the whole of the column. In this manner, we can read any part of the sheet, we will have to make some changes in the loop part. The cell_value function is of importance here, as it carries the data from cells to the output.

One of the important things is to use a well-developed library like we used xlrd, which is widely used and recommended. The xlrd library for python developers is an easy way to deal with various operations that are to be executed over an excel spreadsheet. The xlrd library is one of the many libraries available for python developers to work with excel.

Conclusion

The Read Excel sheet function allows us to access and operate read operations over an excel sheet. We demonstrated the working of different functions of the xlrd library, and read the data from the excel sheet. We can read the number of rows, columns, header of the file, and the data within the sheets, anywhere on the sheet.

Recommended Articles

We hope that this EDUCBA information on “Python Read Excel File” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. Python Read CSV File
2. Python Delete File
3. Copy List In Python
4. Python File Methods