How to Read in Excel File Python
The .xlsx is the extension of the excel certificate that tin store a large amount of data in tabular grade, and many types of arithmetics and logical calculation can be washed hands in an excel spreadsheet. Sometimes it is required to read the data from the excel document using Python script for programming purposes. Many modules be 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 will be required to check the examples of this tutorial. Yous can use whatsoever existing excel file or create a new one. Hither, a new excel file named sales.xlsx file has been created with the following data. This file has used for reading past using unlike python modules in the next function of this tutorial.
sales.xlsx
| Sales Engagement | Sales Person | Amount |
|---|---|---|
| 12/05/xviii | Sila Ahmed | 60000 |
| 06/12/xix | Mir Hossain | 50000 |
| 09/08/20 | Sarmin Jahan | 45000 |
| 07/04/21 | Mahmudul Hasan | 30000 |
Case-1: Read excel file using xlrd
The xlrd module is non installed with Python past default. And so, you have to install the module earlier using it. The latest version of this module does not support the excel file with the .xlsx extension. So, you lot have 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==ane.two.0
Later on completing the installation process, 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 one sheet simply. So, the workbook.sheet_by_index() role has been used in the script with the statement value 0. Side by side, 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 sheet data. The cell_value() function 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 past 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, v ):
for j in range( 0, 3 ):
# Print the cell values with tab space
print(worksheet.cell_value(i, j), stop='\t' )
print( '' )
Output:
The post-obit output will appear after executing the in a higher place script.
Case-2: Read excel file using openpyxl
The openpyxl is another python module to read the xlsx file, and it is also not installed with Python by default. Run the post-obit command from the terminal to install this module before using it.
After 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 every bit the argument value of this office. 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 sail. Each field in the output will exist separated past two tab spaces.
# Import openyxl module
import openpyxl
# Ascertain 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):
impress( col [i].value, end="\t \t" )
print( '' )
Output:
The following output volition appear subsequently executing the above script.
Case-3: Read excel file using pandas
The pandas module is not installed with python-like the previous module. So, if you didn't install it before, then you take to install it. Run the post-obit command to install the pandas from the final.
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 shop the values in the variable named data. 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', 'Corporeality' ] )
# Print the content
print( "The content of the file is:\n", data)
Output:
The post-obit output will announced after executing the above script. The output of this script is different from the previous 2 examples. The row numbers are printed in the showtime 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.
Conclusion:
The python users demand to work with xlsx files for unlike programming purposes. Three different ways to read the xlsx file have been shown in this tutorial past using three python modules. Each module has different functions and properties to read the xlsx file. This tutorial will assistance python users read the xlsx file easily using the python script later reading this tutorial.
Most the author
I am a trainer of web programming courses. I like to write commodity or tutorial on various It topics. I have a YouTube aqueduct where many types of tutorials based on Ubuntu, Windows, Word, Excel, WordPress, Magento, Laravel etc. are published: Tutorials4u Help.
Source: https://linuxhint.com/read-excel-file-python/
0 Response to "How to Read in Excel File Python"
Post a Comment