Untitled

import pandas as pd
import os

# Specify the folder containing Excel files
folder_path = r'C:\Users\ensaani\Downloads\NOC Thailand-20240208T093348Z-001'
# import pdb;pdb.set_trace()
# Get a list of all Excel files in the folder
excel_files = [file for file in os.listdir(folder_path) if file.endswith('.xlsx')]

# Initialize an empty DataFrame to store the consolidated data
consolidated_data = pd.DataFrame()

# Loop through each Excel file and append its data to the consolidated DataFrame
for file in excel_files:
    file_path = os.path.join(folder_path, file)
    df = pd.read_excel(file_path)  # Assuming the data is in the first sheet; adjust if needed
    consolidated_data = consolidated_data.append(df, ignore_index=True)

consolidated_data.to_excel(r"C:\Users\ensaani\Downloads\consolidated_report.xlsx", index=False,engine='openpyxl')

print(f"Consolidated data has been saved to {consolidated_data}")