MillMin/n8n-processing-data

1from fastapi import FastAPI, File, UploadFile, HTTPException2from fastapi.responses import StreamingResponse3import pandas as pd4import io5import logging6 7# Setup logging8logging.basicConfig(level=logging.INFO)9logger = logging.getLogger(__name__)10 11# Create a FastAPI instance12app = FastAPI(13    title="Affina Excel Processor",14    description="An API to process Excel files by removing duplicate rows based on 'phone' and 'license' columns.",15    version="1.0.0"16)17 18@app.get("/", tags=[""])19async def read_root():20    return {"message": "Welcome to the Affina Excel Processing Service! Send a POST request to /process-excel/ to process your file."}21 22@app.post("/process-excel/", tags=["Excel Processing"])23async def process_excel_file(file: UploadFile = File(...)):24    """25    Processes an uploaded XLSX file to remove duplicate rows based on 'phone' and 'license' columns.26 27    - **file**: The XLSX file to be processed.28 29    Returns the processed XLSX file for download.30    """31    logger.info(f"Received file: {file.filename}, content_type: {file.content_type}, size: {file.size if hasattr(file, 'size') else 'unknown'}")32    33    # More flexible file type checking34    if file.filename:35        if not (file.filename.lower().endswith('.xlsx') or file.filename.lower().endswith('.xls')):36            raise HTTPException(status_code=400, detail="Invalid file format. Please upload an .xlsx or .xls file.")37    elif file.content_type:38        valid_content_types = [39            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',40            'application/vnd.ms-excel',41            'application/octet-stream'  42        ]43        if file.content_type not in valid_content_types:44            raise HTTPException(status_code=400, detail=f"Invalid content type: {file.content_type}. Expected Excel file.")45 46    try:47        # Read the uploaded file content48        contents = await file.read()49        logger.info(f"File read successfully, bytes length: {len(contents)}")50        51        # Check if file is empty52        if len(contents) == 0:53            raise HTTPException(status_code=400, detail="Uploaded file is empty.")54        55        # Try to read as Excel file56        try:57            # First try with openpyxl engine (for .xlsx)58            df = pd.read_excel(io.BytesIO(contents), engine='openpyxl')59        except Exception as xlsx_error:60            logger.warning(f"Failed to read as .xlsx: {xlsx_error}")61            try:62                # Fallback to xlrd engine (for .xls)63                df = pd.read_excel(io.BytesIO(contents), engine='xlrd')64            except Exception as xls_error:65                logger.error(f"Failed to read as .xls: {xls_error}")66                raise HTTPException(67                    status_code=400, 68                    detail=f"Failed to read Excel file. XLSX error: {str(xlsx_error)}, XLS error: {str(xls_error)}"69                )70 71    except HTTPException:72        raise73    except Exception as e:74        logger.error(f"Unexpected error reading file: {str(e)}")75        raise HTTPException(status_code=400, detail=f"Failed to read file: {str(e)}")76    77    logger.info(f"DataFrame shape: {df.shape}, columns: {list(df.columns)}")78    79    # Check if DataFrame is empty80    if df.empty:81        raise HTTPException(status_code=400, detail="The Excel file is empty or contains no data.")82    83    # Check if required columns exist (case-insensitive)84    required_columns = ['phone', 'license']85    df_columns_lower = [col.lower().strip() for col in df.columns]86    87    # Create mapping of lowercase to original column names88    column_mapping = {col.lower().strip(): col for col in df.columns}89    90    missing_columns = []91    actual_column_names = []92    93    for req_col in required_columns:94        if req_col.lower() in df_columns_lower:95            actual_column_names.append(column_mapping[req_col.lower()])96        else:97            missing_columns.append(req_col)98    99    if missing_columns:100        available_columns = list(df.columns)101        raise HTTPException(102            status_code=400, 103            detail=f"Missing required columns: {missing_columns}. The file must contain 'phone' and 'license' columns. Available columns: {available_columns}"104        )105 106    try:107        # Get the original count108        original_count = len(df)109        logger.info(f"Original row count: {original_count}")110        111        # Remove duplicate rows based on the actual column names found112        df_cleaned = df.drop_duplicates(subset=actual_column_names, keep='first')113        cleaned_count = len(df_cleaned)114        duplicates_removed = original_count - cleaned_count115        116        logger.info(f"After deduplication: {cleaned_count} rows, removed {duplicates_removed} duplicates")117 118        # Save the processed DataFrame to an in-memory Excel file119        output_buffer = io.BytesIO()120        121        # Use openpyxl engine for writing .xlsx files122        with pd.ExcelWriter(output_buffer, engine='openpyxl') as writer:123            df_cleaned.to_excel(writer, index=False, sheet_name='processed_data')124        125        output_buffer.seek(0)126 127        # Define headers for the file response128        headers = {129            'Content-Disposition': 'attachment; filename="processed_data.xlsx"',130            'X-Original-Rows': str(original_count),131            'X-Cleaned-Rows': str(cleaned_count),132            'X-Duplicates-Removed': str(duplicates_removed)133        }134 135        logger.info("File processed successfully, returning response")136 137        # Return the processed file as a streaming response138        return StreamingResponse(139            io.BytesIO(output_buffer.getvalue()),140            media_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',141            headers=headers142        )143 144    except Exception as e:145        logger.error(f"Error during processing: {str(e)}")146        raise HTTPException(status_code=500, detail=f"An error occurred while processing the file: {str(e)}")147 148# Add an endpoint to check service health149@app.get("/health", tags=["Health"])150async def health_check():151    return {"status": "healthy", "service": "Affina Excel Processor"}