How to Read Excel Files in Python
The .xlsx is the extension of the excel document that can shop a large amount of data in tabular form, and many types of arithmetics and logical calculation tin can exist done easily in an excel spreadsheet. Sometimes it is required to read the data from the excel document using Python script for programming purposes. Many modules exist in Python to read the excel document. Some of the useful modules are xlrd, openpyxl, and pandas. The ways to use these modules to read the excel file in Python have been shown in this tutorial.
Pre-requisite:
A dummy excel file with the .xlsx extension will exist required to check the examples of this tutorial. Y'all can employ any existing excel file or create a new i. Here, a new excel file named sales.xlsx file has been created with the following data. This file has used for reading by using different python modules in the side by side part of this tutorial.
sales.xlsx
| Sales Date | Sales Person | Amount |
|---|---|---|
| 12/05/eighteen | Sila Ahmed | 60000 |
| 06/12/19 | Mir Hossain | 50000 |
| 09/08/twenty | Sarmin Jahan | 45000 |
| 07/04/21 | Mahmudul Hasan | 30000 |
Instance-1: Read excel file using xlrd
The xlrd module is non installed with Python by default. And then, you accept to install the module before using information technology. The latest version of this module does not support the excel file with the .xlsx extension. So, y'all accept to install the 1.2.0 version of this module to read the xlsx file. Run the following command from the terminal to install the required version of xlrd.
$ pip install xlrd==1.two.0
After completing the installation process, create a python file with the post-obit script to read the sales.xlsx file using the xlrd module. open_workbook() function is used in the script open the xlsx file for reading. This excel file contains one sheet only. So, the workbook.sheet_by_index() role has been used in the script with the statement value 0. Next, the nested 'for' loop has used to read the cell values of the worksheet using the row and cavalcade values. 2 range() functions have been used in the script to define the row and column size based on the sheet information. The cell_value() role has used to read the particular cell value of the sheet in each iteration of the loop. Each field in the output will be separated by one tab infinite.
# Import the xlrd module
import xlrd
# Open the Workbook
workbook = xlrd.open_workbook( "sales.xlsx" )
# Open up the worksheet
worksheet = workbook.sheet_by_index( 0 )
# Iterate the rows and columns
for i in range( 0, 5 ):
for j in range( 0, iii ):
# Print the jail cell values with tab infinite
print(worksheet.cell_value(i, j), end='\t' )
print( '' )
Output:
The following output will appear after executing the above script.
Example-2: Read excel file using openpyxl
The openpyxl is some other python module to read the xlsx file, and it is also non installed with Python past default. Run the following command from the terminal to install this module before using it.
Subsequently completing the installation process, create a python file with the following script to read the sales.xlsx file. Like the xlrd module, the openpyxl module has the load_workbook() function to open up the xlsx file for reading. The sales.xlsx file is used as the argument value of this function. The object of the wookbook.active has been created in the script to read the values of the max_row and the max_column properties. These properties have been used in the nested for loops to read the content of the sales.xlsx file. The range() function has been used to read the rows of the sheet, and the iter_cols() function has been used to read the columns of the sheet. Each field in the output volition exist separated by two tab spaces.
# Import openyxl module
import openpyxl
# Define variable to load the wookbook
wookbook = openpyxl.load_workbook( "sales.xlsx" )
# Define variable to read the active sheet:
worksheet = wookbook.active
# Iterate the loop to read the cell values
for i in range( 0, worksheet.max_row):
for col in worksheet.iter_cols( ane, worksheet.max_column):
print( col [i].value, cease="\t \t" )
print( '' )
Output:
The post-obit output will appear subsequently executing the above script.
Example-3: Read excel file using pandas
The pandas module is non installed with python-similar the previous module. So, if y'all didn't install it before, then y'all have to install it. Run the following control to install the pandas from the terminal.
After completing the installation process, create a python file with the following script to read the sales.xlsx file. The read_excel() function of pandas is used for reading the xlsx file. This function has used in the script to read the sales.xlsx file. The DataFrame() function has used here to read the content of the xlsx file in the data frame and store the values in the variable named data. The value of the data has been printed subsequently.
# Import pandas
import pandas as pd
# Load the xlsx file
excel_data = pd.read_excel( 'sales.xlsx' )
# Read the values of the file in the dataframe
data = pd.DataFrame(excel_data, columns=[ 'Sales Date', 'Sales Person', 'Corporeality' ] )
# Print the content
impress( "The content of the file is:\north", information)
Output:
The following output volition appear after executing the above script. The output of this script is different from the previous two examples. The row numbers are printed in the first column, where the row value has counted from 0. The date values are aligned centrally. The names of the salespersons are aligned right. The corporeality is aligned left.
Decision:
The python users need to work with xlsx files for different programming purposes. Iii different ways to read the xlsx file have been shown in this tutorial by using iii python modules. Each module has unlike functions and properties to read the xlsx file. This tutorial will help python users read the xlsx file easily using the python script after reading this tutorial.
Virtually the author
I am a trainer of web programming courses. I similar to write article or tutorial on various IT topics. I accept a YouTube aqueduct where many types of tutorials based on Ubuntu, Windows, Word, Excel, WordPress, Magento, Laravel etc. are published: Tutorials4u Assistance.
Source: https://linuxhint.com/read-excel-file-python/