- Python Basic Tutorial
- Python - Home
- Python - Overview
- Python - Environment Setup
- Python - Basic Syntax
- Python - Comments
- Python - Variables
- Python - Data Types
- Python - Operators
- Python - Decision Making
- Python - Loops
- Python - Numbers
- Python - Strings
- Python - Lists
- Python - Tuples
- Python - Dictionary
- Python - Date & Time
- Python - Functions
- Python - Modules
- Python - Files I/O
- Python - Exceptions
- Python Advanced Tutorial
- Python - Classes/Objects
- Python - Reg Expressions
- Python - CGI Programming
- Python - Database Access
- Python - Networking
- Python - Sending Email
- Python - Multithreading
- Python - XML Processing
- Python - GUI Programming
- Python - Further Extensions
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 −
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))
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))
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
- Related Articles
- How to read data from all files in a directory using Java?
- How to Merge all CSV Files into a single dataframe – Python Pandas?
- Python - Read all CSV files in a folder in Pandas?
- Creating a Dataframe using Excel files
- 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 read a JSON file into a DataFrame using Python Pandas library?
- How to list all files (only) from a directory using Java?
- How to perform grep operation on all files in a directory?
- How to put a Pandas DataFrame into a JSON file and read it again?
- Python - How to Merge all excel files in a folder
- Java program to delete all the files in a directory recursively (only files)
- Java program to List all files in a directory recursively