How to Read Input From Excel File in Python

The .xlsx is the extension of the excel certificate that can shop a large corporeality of information in tabular form, and many types of arithmetic and logical calculation can be 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 means 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 volition be required to cheque the examples of this tutorial. Yous can use whatsoever existing excel file or create a new one. Here, a new excel file named sales.xlsx file has been created with the following information. This file has used for reading by using different python modules in the next role of this tutorial.

sales.xlsx

Sales Date	Sales Person	Amount
12/05/18	Sila Ahmed	60000
06/12/19	Mir Hossain	50000
09/08/20	Sarmin Jahan	45000
07/04/21	Mahmudul Hasan	30000

Example-ane: Read excel file using xlrd

The xlrd module is not installed with Python by default. So, you take to install the module before using it. The latest version of this module does not support the excel file with the .xlsx extension. So, you take to install the ane.2.0 version of this module to read the xlsx file. Run the following control from the last to install the required version of xlrd.

$ pip install xlrd==1.2.0

Subsequently completing the installation procedure, create a python file with the following script to read the sales.xlsx file using the xlrd module. open_workbook() function is used in the script open up the xlsx file for reading. This excel file contains 1 canvass only. So, the workbook.sheet_by_index() part has been used in the script with the argument value 0. Next, the nested 'for' loop has used to read the cell values of the worksheet using the row and column values. Two range() functions have been used in the script to define the row and column size based on the sail data. The cell_value() role has used to read the particular prison cell value of the canvas in each iteration of the loop. Each field in the output will be separated by one tab space.

# Import the xlrd module
import xlrd

# Open the Workbook
workbook = xlrd.open_workbook( "sales.xlsx" )

# Open the worksheet
worksheet = workbook.sheet_by_index( 0 )

# Iterate the rows and columns
for i in range( 0, 5 ):
for j in range( 0, 3 ):
# Impress the jail cell values with tab space
print(worksheet.cell_value(i, j), end='\t' )
print( '' )

Output:

The following output will appear afterwards executing the above script.

Case-ii: Read excel file using openpyxl

The openpyxl is another python module to read the xlsx file, and it is besides not 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. Similar the xlrd module, the openpyxl module has the load_workbook() function to open the xlsx file for reading. The sales.xlsx file is used equally the statement 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 take 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 canvass. Each field in the output will be separated by ii 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 canvas:
worksheet = wookbook.active

# Iterate the loop to read the prison cell values
for i in range( 0, worksheet.max_row):
for col in worksheet.iter_cols( 1, worksheet.max_column):
print( col [i].value, cease="\t \t" )
print( '' )

Output:

The following output will appear subsequently executing the in a higher place script.

Example-three: Read excel file using pandas

The pandas module is non installed with python-like the previous module. And then, if y'all didn't install it before, and then y'all have to install it. Run the post-obit command to install the pandas from the terminal.

After completing the installation process, create a python file with the post-obit 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 information. The value of the data has been printed later.

# 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', 'Amount' ] )
# Print the content
print( "The content of the file is:\n", data)

Output:

The following output will appear afterwards executing the above script. The output of this script is different from the previous two examples. The row numbers are printed in the kickoff column, where the row value has counted from 0. The date values are aligned centrally. The names of the salespersons are aligned right. The amount is aligned left.

Decision:

The python users demand to work with xlsx files for different programming purposes. Iii unlike ways to read the xlsx file take been shown in this tutorial by using three python modules. Each module has different functions and backdrop to read the xlsx file. This tutorial will help python users read the xlsx file easily using the python script afterwards reading this tutorial.

About the writer

I am a trainer of web programming courses. I similar to write article or tutorial on various It topics. I have a YouTube channel where many types of tutorials based on Ubuntu, Windows, Word, Excel, WordPress, Magento, Laravel etc. are published: Tutorials4u Help.

mendezsooppeed.blogspot.com

Source: https://linuxhint.com/read-excel-file-python/

Share this post