- Trending Categories
- Data Structure
- Operating System
- C Programming
- 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 −
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
- 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?