convert_excel_to_csv.py

import pandas as pd
import os

def convert_excel_to_csv(excel_file_path, csv_file_path):
    """Converts an Excel file to a CSV file."""
    try:
        # Read the Excel file (first sheet by default)
        # If specific sheets need to be read, this needs to be adjusted
        df = pd.read_excel(excel_file_path, sheet_name=0)
        df.to_csv(csv_file_path, index=False, encoding='utf-8')
        print(f"Successfully converted '{excel_file_path}' to '{csv_file_path}'")
    except Exception as e:
        print(f"Error converting '{excel_file_path}': {e}")

def main():
    """Main function to find and convert Excel files."""
    source_directory = os.path.join('data', 'downloads')
    target_directory = source_directory # Save CSVs in the same directory

    if not os.path.exists(source_directory):
        print(f"Source directory '{source_directory}' not found.")
        return

    excel_files_to_convert = [
        "2021SeniorSecondaryCompletionAndAchievementInformation.xlsx",
        "2022SeniorSecondaryCompletionAndAchievementInformation.xlsx",
        "2023SeniorSecondaryCompletionAndAchievementInformation.xlsx",
        "2024SeniorSecondaryCompletionAndAchievementInformation.xlsx",
        "vcaa-search-export.xlsx"
    ]

    for excel_file_name in excel_files_to_convert:
        excel_file_path = os.path.join(source_directory, excel_file_name)
        if excel_file_name.endswith('.xlsx') and not excel_file_name.startswith('~$'):
            base_name = os.path.splitext(excel_file_name)[0]
            csv_file_name = f"{base_name}.csv"
            csv_file_path = os.path.join(target_directory, csv_file_name)
            
            if os.path.exists(excel_file_path):
                convert_excel_to_csv(excel_file_path, csv_file_path)
            else:
                print(f"Excel file '{excel_file_path}' not found.")
        else:
            print(f"Skipping non-Excel or temporary file: {excel_file_name}")

if __name__ == "__main__":
    main()