Processing User Files & Spreadsheets in watsonx Orchestrate: Building an Automated Data Analyst Agent

WXO Labs Series Icon By Markus van Kempen | Series: WXO Labs & Tutorial Guide (Part 3 of 15)
Interactive Lab Portal: https://markusvankempen.github.io/wxo-labs/
GitHub Repository: https://github.com/markusvankempen/wxo-labs

Processing User Files & Spreadsheets in watsonx Orchestrate: Building an Automated Data Analyst Agent

Why This Pattern Matters

Managers often receive spreadsheet rosters or sales dumps and need instant summaries without opening Excel. In this lab, we build an agent capable of ingesting uploaded Excel (.xlsx) workbooks directly from the chat interface, running automated Pandas analytics, and returning instant KPI summaries.

πŸ“‹ Prerequisites & Setup

Ensure your environment has the required dependencies:

pip install ibm-watsonx-orchestrate

Verify active environment:

orchestrate env list

πŸ› οΈ Step 1: Implement the Logic (file_processor.py)

from ibm_watsonx_orchestrate import tool
import pandas as pd
import os

@tool
def analyze_employee_data(file_path: str) -> str:
    """
    Analyze employee data from an uploaded Excel spreadsheet.

    Args:
        file_path: Absolute filesystem path to the uploaded Excel file.

    Returns:
        Structured summary statistics and department breakdown.
    """
    try:
        if not os.path.exists(file_path):
            return f"❌ File not found at path: {file_path}"

        df = pd.read_excel(file_path)
        total_records = len(df)
        columns = ", ".join(df.columns.tolist())
        
        dept_counts = df['Department'].value_counts().to_dict() if 'Department' in df.columns else {}
        avg_salary = f"${df['Salary'].mean():,.2f}" if 'Salary' in df.columns else "N/A"

        breakdown = "\n".join([f"  β€’ {dept}: {count}" for dept, count in dept_counts.items()])
        return f"""
Spreadsheet Analytics Summary:
---------------------------------
Total Headcount:    {total_records}
Detected Columns:   {columns}
Average Base Pay:   {avg_salary}

Department Breakdown:
{breakdown}
"""
    except Exception as e:
        return f"❌ Error processing Excel file: {str(e)}"

πŸ“¦ Step 2: Declare Dependencies (requirements.txt)

ibm-watsonx-orchestrate>=2.9.0
pandas>=2.0.0
openpyxl>=3.1.0

πŸ€– Step 3: Configure the AI Agent (agent.yaml)

spec_version: v1
kind: native
name: hr_data_analyst
title: "HR Data Analyst"
description: "Ingests user-uploaded spreadsheets to extract headcount and department statistics."
model: ibm/granite-3-8b-instruct
instructions: |
  You are an HR analytics assistant. When a user uploads or references an Excel file,
  call `analyze_employee_data` passing the file path, and summarize the key findings.
tools:
  - analyze_employee_data

πŸš€ Step 4: Import & Test Live in Terminal

Import tool and test conversationally:

orchestrate chat ask -n hr_data_analyst "Analyze this spreadsheet: /tmp/sample_employees.xlsx"

Live Response:

[hr_data_analyst]
Spreadsheet Analytics Summary:
---------------------------------
Total Headcount:    3
Detected Columns:   Employee_ID, Name, Department, Salary
Average Base Pay:   $78,333.33

Department Breakdown:
  β€’ Engineering: 1
  β€’ Sales: 1
  β€’ HR: 1

πŸ’‘ Pro Tips & Gotchas

πŸ—ΊοΈ Series Roadmap (15 Hands-On Labs)

This tutorial is Part 3 of 15 in the complete enterprise hands-on series:

Explore the interactive curriculum and code repository: