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:
- Import necessary python packages like pandas, glob, and os.
- Use glob python package to retrieve files/pathnames matching a specified pattern i.e. ā.xlsxā
- Loop over the list of excel files, read that file using pandas.read_excel().
- Convert each excel file into a dataframe.
- 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.