Reading an excel file using Python

One can retrieve information from a spreadsheet. Reading, writing, or modifying the data can be done in Python can be done in using different methods. Also, the user might have to go through various sheets and retrieve data based on some criteria or modify some rows and columns and do a lot of work. Here, we will see the different methods to read our excel file.

Required Module

pip install xlrd

Input File: 

Method 1: Reading an excel file using Python using Pandas

In this method, We will first import the Pandas module then we will use Pandas to read our excel file. You can read more operations using the excel file using Pandas in this article. Click here

# import pandas lib as pd
import pandas as pd

# read by default 1st sheet of an excel file
dataframe1 = pd.read_excel('book2.xlsx')

print(dataframe1)

Output:

Method 2: Reading an excel file using Python using openpyxl

The load_workbook() function opens the Books.xlsx file for reading. This file is passed as an argument to this function. The object of the dataframe.active has been created in the script to read the values of the max_row and the max_column properties. These values are used in the loops to read the content of the Books2.xlsx file. You can read other operations using openpyxl in this article.

import openpyxl

# Define variable to load the dataframe
dataframe = openpyxl.load_workbook("Book2.xlsx")

# Define variable to read sheet
dataframe1 = dataframe.active

# Iterate the loop to read the cell values
for row in range(0, dataframe1.max_row):
    for col in dataframe1.iter_cols(1, dataframe1.max_column):
        print(col[row].value)

Output: 

Method 3: Reading an excel file using Python using Xlwings

Xlwings can be used to insert data in an Excel file similarly as it reads from an Excel file. Data can be provided as a list or a single input to a certain cell or a selection of cells. You can read other operations using Xlwings in this article.

# Python3 code to select
# data from excel
import xlwings as xw

# Specifying a sheet
ws = xw.Book("Book2.xlsx").sheets['Sheet1']

# Selecting data from
# a single cell
v1 = ws.range("A1:A7").value

print("Result:", v1)

Output:

Result: ['Name  Age    Stream  Percentage', 
'0      Ankit   18      Math          95', 
'1      Rahul   19   Science          90', 
'2    Shaurya   20  Commerce          85', 
'3  Aishwarya   18      Math          80', 
'4   Priyanka   19   Science          75', 
None]

RECOMMENDED ARTICLE – How to Automate an Excel Sheet in Python?

Reading an excel file using Python – FAQs

How to read Excel file in Python using path?

You can read an Excel file in Python using the openpyxl library, which supports both .xlsx and .xls formats:

import openpyxl
# Load workbook
wb = openpyxl.load_workbook('path/to/your/excel_file.xlsx')
# Select active sheet (optional)
sheet = wb.active
# Access cell values
cell_value = sheet['A1'].value
print(cell_value)
# Close workbook when done
wb.close()

Replace ‘path/to/your/excel_file.xlsx’ with the actual path to your Excel file.

How to open an Excel file from Python?

To open and manipulate Excel files, you can also use the xlrd library, which supports .xls files (not .xlsx):

import xlrd
# Open Excel file
workbook = xlrd.open_workbook('path/to/your/excel_file.xls')
# Access sheet by index or name
sheet = workbook.sheet_by_index(0)  # Access first sheet
# OR
# sheet = workbook.sheet_by_name('Sheet1')  # Access sheet by name
# Read cell value
cell_value = sheet.cell_value(0, 0)  # Access cell A1
print(cell_value)
# Close workbook when done
workbook.close()

How to read an xlsx file in Python using Pandas?

Pandas provides a convenient way to read Excel files directly into a DataFrame:

import pandas as pd
# Read Excel file into DataFrame
df = pd.read_excel('path/to/your/excel_file.xlsx')
# Display DataFrame
print(df.head())

Pandas’ read_excel() function can handle both .xls and .xlsx formats.

How to read xlsx file in Python using CSV?

If you prefer working with CSV format data extracted from an Excel file, you can convert an Excel file into CSV format using Pandas:

import pandas as pd
# Read Excel file into DataFrame
df = pd.read_excel('path/to/your/excel_file.xlsx')
# Export DataFrame to CSV
df.to_csv('output_file.csv', index=False)

This converts the Excel data into a CSV file named output_file.csv in the current directory.

aishwarya.27

Follow

Improve