- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
Updated on 27-Sep-2021 11:28:01
- 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 list all files (only) from a directory using Java?
- How to perform grep operation on all files in a directory?
- How to Merge multiple CSV Files into a single Pandas dataframe ?
- 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 do I list all files of a directory in Python?
Advertisements