Untitled

import os
import glob
import pandas as pd
from google.colab import drive
drive.mount('/content/drive')
# Set the folder path where the Excel files are located
folder_path = "/content/drive/MyDrive/Metropol/raw_data"
excel_files = [file for file in os.listdir(folder_path) if file.endswith(".xlsx")]

# Convert Excel files to CSV
for excel_file in excel_files:
    excel_path = os.path.join(folder_path, excel_file)
    csv_path = os.path.join(folder_path, os.path.splitext(excel_file)[0] + ".csv")

    df = pd.read_excel(excel_path)
    df.to_csv(csv_path, index=False)

    print(f"Converted {excel_file} to CSV")

# Merge CSV files
csv_files = glob.glob(os.path.join(folder_path, "*.csv"))

combined_csv = pd.concat([pd.read_csv(file) for file in csv_files], ignore_index=True)
merged_csv_path = os.path.join(folder_path, "merged_data.csv")
combined_csv.to_csv(merged_csv_path, index=False)

print("CSV files merged and saved as merged_data.csv")