How to read all excel files under a directory as a Pandas DataFrame ?

To read all excel files in a directory, use the Glob module and the read_excel() method.

Let’s say the following are our excel files in a directory −

Sales1.xlsx

Sales2.xlsx

At first, set the path where all the excel files are located. Get the excel files and read them using glob −

path = "C:\\Users\\amit_\\Desktop\\"

filenames = glob.glob(path + "\*.xlsx")
print('File names:', filenames)

Next, use the for loop to iterate and read all the excels files in a specific directory. We are also using read_excel() −

for file in filenames:
   print("Reading file = ",file)
   print(pd.read_excel(file))

Example

Following is the complete code −

import pandas as pd
import glob

# getting excel files from Directory Desktop
path = "C:\\Users\\amit_\\Desktop\\"

# read all the files with extension .xlsx i.e. excel 
filenames = glob.glob(path + "\*.xlsx")
print('File names:', filenames)

# for loop to iterate all excel files 
for file in filenames:
   # reading excel files
   print("Reading file = ",file)
   print(pd.read_excel(file))

Output

This will produce the following output −

File names:['C:\\Users\\amit_\\Desktop\\Sales1.xlsx','C:\\Users\\amit_\\Desktop\\Sales2.xlsx']

Reading file = C:\Users\amit_\Desktop\Sales1.xlsx
          Car      Place   UnitsSold
0        Audi  Bangalore          80
1     Porsche     Mumbai         110
2  RollsRoyce       Pune         100

Reading file = C:\Users\amit_\Desktop\Sales2.xlsx
          Car      Place   UnitsSold
0         BMW      Delhi         95
1    Mercedes  Hyderabad         80
2  Lamborgini Chandigarh         80

AmitDiwan

Published on 27-Sep-2021 11:20:31

• Related Questions & Answers
• How to read data from all files in a directory using Java?
• Python - Read all CSV files in a folder in Pandas?
• Python - How to Merge all excel files in a folder
• How to Merge all CSV Files into a single dataframe – Python Pandas?
• How to delete all files in a directory with Python?
• How to list all files in a directory using Java?
• How to unzip all zipped files in a Linux directory?
• How to Merge multiple CSV Files into a single Pandas dataframe ?
• How to list all files (only) from a directory using Java?
• How to perform grep operation on all files in a directory?
• How to read a JSON file into a DataFrame using Python Pandas library?
• Java program to List all files in a directory recursively
• Java program to delete all the files in a directory recursively (only files)
• PHP: Unlink All Files Within A Directory, and then Deleting That Directory
• How to put a Pandas DataFrame into a JSON file and read it again?