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

In this article, we will see how to read all Excel files in a folder into single Pandas dataframe. The task can be performed by first finding all excel files in a particular folder using glob() method and then reading the file by using pandas.read_excel() method and then displaying the content.

Approach:

1. Import necessary python packages like pandas, glob, and os.
2. Use glob python package to retrieve files/pathnames matching a specified pattern i.e. ‘.xlsx’
3. Loop over the list of excel files, read that file using pandas.read_excel().
4. Convert each excel file into a dataframe.
5. Display its location, name, and content.

Below is the implementation.

Python3

# import necessary libraries
import pandas as pd
import os
import glob


# use glob to get all the csv files
# in the folder
path = os.getcwd()
csv_files = glob.glob(os.path.join(path, "*.xlsx"))


# loop over the list of csv files
for f in csv_files:

    # read the csv file
    df = pd.read_excel(f)

    # print the location and filename
    print('Location:', f)
    print('File Name:', f.split("\\")[-1])

    # print the content
    print('Content:')
    display(df)
    print()

Output :

Note: The program reads all Excel files in the folder in which the program itself is present.