Untitled

import pandas as pd
import os

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

# Initialize an empty DataFrame to store the consolidated data
consolidated_data =[]
# consolidated_data = pd.DataFrame()
print(excel_files)
# 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)
    print(file_path)
    df = pd.read_csv(file_path)  # Assuming the data is in the first sheet; adjust if needed
    print(df.head())
    consolidated_data = consolidated_data.append(df)
import pdb;pdb.set_trace()
# consolidated_data.to_excel(r"C:\Users\ensaani\Downloads\consolidated_report.xlsx", index=False,engine='openpyxl')
finaldf= pd.concat(consolidated_data, ignore_index=True)
finaldf.to_excel(r"C:\Users\ensaani\Downloads\consolidated_report.xlsx", index=False,engine='openpyxl')
print(f"Consolidated data has been saved to {consolidated_data}")