How to Read in Excel Data 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 arithmetic and logical calculation can be done easily in an excel spreadsheet. Sometimes information technology 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 bank check the examples of this tutorial. You lot can apply whatsoever existing excel file or create a new one. Here, a new excel file named sales.xlsx file has been created with the post-obit data. This file has used for reading by using different python modules in the side by side role of this tutorial.
sales.xlsx
| Sales Date | Sales Person | Amount |
|---|---|---|
| 12/05/eighteen | Sila Ahmed | 60000 |
| 06/12/19 | Mir Hossain | 50000 |
| 09/08/20 | Sarmin Jahan | 45000 |
| 07/04/21 | Mahmudul Hasan | 30000 |
Example-1: Read excel file using xlrd
The xlrd module is not installed with Python by default. So, yous have 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 have 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==i.2.0
After 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 only. Then, the workbook.sheet_by_index() function 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 cavalcade values. Ii range() functions have been used in the script to define the row and cavalcade size based on the sheet information. The cell_value() function has used to read the particular prison cell value of the sheet 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, five ):
for j in range( 0, 3 ):
# Impress the cell values with tab infinite
print(worksheet.cell_value(i, j), end='\t' )
print( '' )
Output:
The following output volition announced after executing the above script.
Example-two: Read excel file using openpyxl
The openpyxl is some other python module to read the xlsx file, and information technology is also not installed with Python by default. Run the post-obit command from the terminal to install this module earlier using it.
Afterward completing the installation procedure, create a python file with the post-obit script to read the sales.xlsx file. Like 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 statement 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 accept been used in the nested for loops to read the content of the sales.xlsx file. The range() part has been used to read the rows of the canvas, and the iter_cols() function has been used to read the columns of the sail. Each field in the output will be 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.agile
# Iterate the loop to read the prison cell values
for i in range( 0, worksheet.max_row):
for col in worksheet.iter_cols( ane, worksheet.max_column):
print( col [i].value, terminate="\t \t" )
print( '' )
Output:
The post-obit output will appear afterward executing the in a higher place script.
Case-3: Read excel file using pandas
The pandas module is not installed with python-similar the previous module. Then, if you didn't install information technology earlier, so you have to install information technology. Run the post-obit command to install the pandas from the terminal.
Later 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 hither 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 Engagement', 'Sales Person', 'Amount' ] )
# Impress the content
print( "The content of the file is:\n", data)
Output:
The post-obit output will appear after executing the higher up script. The output of this script is different from the previous 2 examples. The row numbers are printed in the first column, where the row value has counted from 0. The appointment values are aligned centrally. The names of the salespersons are aligned correct. The amount is aligned left.
Conclusion:
The python users need to piece of work with xlsx files for different programming purposes. 3 unlike ways to read the xlsx file have been shown in this tutorial by using iii python modules. Each module has different functions and properties to read the xlsx file. This tutorial will help python users read the xlsx file easily using the python script afterwards reading this tutorial.
Nigh the writer
I am a trainer of web programming courses. I like to write commodity or tutorial on various Information technology topics. I accept a YouTube channel 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 Data Python"
Post a Comment