Convert grades from BlackBoard's gradebook to UEES internal format

blackboard_to_uees_converter.py

import csv
import pandas as pd
import sys
import codecs
import re

def find_column(columns, pattern):
    for col in columns:
        if re.search(pattern, col):
            return col
    raise KeyError(f"Column matching pattern '{pattern}' not found")

def convert_csv_to_xlsx(csv_path, xlsx_path):
    # Read the CSV file with UTF-8-BOM encoding
    with codecs.open(csv_path, 'r', encoding='utf-8-sig') as file:
        csv_reader = csv.DictReader(file)
        data = list(csv_reader)

    if not data:
        print("No data found in the CSV file.")
        return

    # Find the correct column names
    columns = data[0].keys()
    actividades_col = find_column(columns, r'P1: Nota Total Actividades \[Puntos totales: hasta 60 Puntuación\]')
    evaluacion_col = find_column(columns, r'P1: Nota Total Evaluación \[Puntos totales: hasta 40 Puntuación\]')

    # Process the data
    processed_data = []
    for row in data:
        if row['ID de estudiante']:  # Skip rows without a student ID
            processed_row = {
                'CODIGO ALUMNO': row['ID de estudiante'],
                'NOMBRE ALUMNO': f"{row['Apellidos']}, {row['Nombre']}",
                'ACTIVIDADES (60)': float(row[actividades_col].replace(',', '.')),
                'EVALUACION (40)': float(row[evaluacion_col].replace(',', '.'))
            }
            processed_data.append(processed_row)

    # Create a DataFrame
    df = pd.DataFrame(processed_data)

    # Write to XLSX with sheet name 'Hoja1'
    with pd.ExcelWriter(xlsx_path, engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='Hoja1', index=False)

    print(f"XLSX file created successfully: {xlsx_path}")

if __name__ == "__main__":
    if len(sys.argv) != 3:
        print("Usage: python script.py <input_csv_path> <output_xlsx_path>")
        sys.exit(1)

    input_csv = sys.argv[1]
    output_xlsx = sys.argv[2]
    convert_csv_to_xlsx(input_csv, output_xlsx)