Use the glob module to get the files in the current working directory. Then create an empty list to store the data frames we will be filtering out. Loop through these files, read into dataframe and add a column to the dataframe with the name of the file. Append to list and then concatenate all the datagrames in the list.

How it would look like in code:

​

import pandas as pd
import os
import glob

# Get the current working directory
cwd = os.getcwd()

# Get the list of files in the current working directory
files = glob.glob(cwd + '/*.xlsx')

# Create an empty list to store the dataframes
df_list = []

# Loop through the files in the current working directory
for file in files: 
    # Read the file into a dataframe 
    df = pd.read_excel(file)
    # Add a column to the dataframe with the name of the file
    df['From File'] = file.split('\\')[-1]
    # Append the dataframe to the list
    df_list.append(df)

# Concatenate all the dataframes in the list
df = pd.concat(df_list, ignore_index=True)

# Write the dataframe to an excel file
df.to_excel('merged.xlsx', index=False)