Chat with CSV & Excel Files Using Local AI

Published on April 11, 2026 — 24 min read

Our finance team needed answers from a 100K-row sales dataset last quarter. Uploading it to ChatGPT meant leaking customer revenue data to OpenAI's servers. Hiring a data analyst for a one-off project meant a two-week delay and a $3K invoice. So I built a local alternative in an afternoon: load the CSV, ask questions in plain English, get answers in seconds. The data never left our network.

This guide covers three different approaches — PandasAI for quick exploration, DuckDB for fast SQL-based queries, and LangChain for complex multi-step analysis. I tested all three on real datasets and will share exactly where each one works and where it falls apart, with specific accuracy numbers and response times.

---

Why Your Spreadsheet Data Should Stay Local {#why-local}

Consider what lives in a typical business spreadsheet. Revenue per customer. Employee salaries. Customer email addresses. Supplier costs. Churn predictions. Sales pipeline values.

Sending this to any cloud AI service — ChatGPT, Claude, Gemini — means trusting a third party with your most sensitive business data. Even with enterprise data processing agreements, the audio of "we uploaded our entire customer database to an AI chatbot" does not play well in a breach notification.

There is a cost problem too. Analyzing a 100K-row CSV through GPT-4o costs $3-8 per session depending on query count, because every row gets converted to tokens. With local AI, every query after setup costs zero.

The local AI privacy guide covers the broader privacy argument. For this article, I will focus on the practical setup.

---

Which Model Writes the Best Analysis Code {#model-selection}

The core mechanic is simple: the LLM reads your question plus the column names, then writes pandas code or SQL to answer it. Code generation accuracy varies dramatically between models. I benchmarked five options on 50 standard data analysis questions — aggregations, filters, joins, time-series calculations, and rankings.

Qwen 2.5 Coder 14B is the clear winner. It handles edge cases that trip up other models — null value handling, date parsing, multi-step aggregations where you need a CTE or subquery. DeepSeek R1 14B is the runner-up and fits in 8 GB when quantized to Q4.

If you are on 8 GB of RAM (say, an M1 MacBook Air), Llama 3.2 8B gets 68% of queries right. That is enough for simple aggregations ("total by category") but unreliable for anything involving joins or window functions.

For detailed Ollama API patterns used throughout this guide, see the Ollama Python API guide.

# Install the recommended model
ollama pull qwen2.5-coder:14b

# Fallback for 8GB systems
ollama pull llama3.2

---

Approach 1: PandasAI + Ollama — Simplest Setup {#pandasai}

PandasAI adds a natural language layer on top of pandas DataFrames. You feed it a DataFrame and a question, it generates pandas code internally, executes it, and returns the result. Five minutes from install to first query.

Installation

pip install pandasai ollama pandas openpyxl

Basic Usage

import pandas as pd
from pandasai import SmartDataframe
from pandasai.llm.local_llm import LocalLLM

# Connect to local Ollama
llm = LocalLLM(
    api_base="http://localhost:11434/v1",
    model="qwen2.5-coder:14b"
)

# Load data
df = pd.read_csv("sales_2025.csv")
smart_df = SmartDataframe(df, config={"llm": llm, "verbose": True})

# Ask questions — answers come back as values or DataFrames
revenue = smart_df.chat("What was total revenue last quarter?")
print(revenue)  # 2,847,321.50

top_products = smart_df.chat("Top 5 products by profit margin, sorted descending")
print(top_products)
# Returns a DataFrame with product_name, margin columns

trend = smart_df.chat("Monthly revenue trend for 2025 with month-over-month growth rate")
print(trend)

Multi-File Analysis

When your answer requires joining data across files:

from pandasai import SmartDatalake

customers = pd.read_csv("customers.csv")
orders = pd.read_csv("orders.csv")
products = pd.read_csv("products.csv")

lake = SmartDatalake(
    [customers, orders, products],
    config={"llm": llm, "verbose": True}
)

# PandasAI figures out the joins automatically
result = lake.chat("Which customer segment has the highest average order value?")
print(result)

result = lake.chat("List customers who bought more than 5 different products")
print(result)

Excel Files

# Single sheet
df = pd.read_excel("budget_2025.xlsx", sheet_name="Q1")
smart_df = SmartDataframe(df, config={"llm": llm})

# All sheets — analyze each one
all_sheets = pd.read_excel("report.xlsx", sheet_name=None)
for name, sheet_df in all_sheets.items():
    smart = SmartDataframe(sheet_df, config={"llm": llm})
    summary = smart.chat("Give me a one-line summary of this data")
    print(f"{name}: {summary}")

Where PandasAI Falls Short

In my testing, PandasAI with local models fails on about 25% of queries. The failure modes:

1. Generates syntactically invalid code — The model hallucinates a pandas method that does not exist
2. Wrong column reference — With many columns, it picks the wrong one for ambiguous names
3. Silent wrong answers — The code runs without errors but returns incorrect results (the hardest to catch)
4. Timeout on large files — Datasets over 500K rows cause PandasAI to send too much context to the LLM

PandasAI is the right choice for interactive exploration on datasets under 100K rows when you can visually verify the results. For production queries where accuracy matters, use the DuckDB approach below.

---

Approach 2: DuckDB + Text-to-SQL — Fastest at Scale {#duckdb}

DuckDB is an embedded columnar database engine that reads CSV and Parquet files directly without importing. Combined with an LLM generating SQL queries, this approach handles million-row datasets with query response times in milliseconds.

Why DuckDB Over pandas or SQLite

Three reasons DuckDB wins for this use case:

1. No import step. Point it at a CSV file and query immediately. No schema definition, no data loading delay.
2. Columnar storage. Analytical queries (GROUP BY, SUM, AVG) run 3-10x faster than SQLite.
3. Direct Parquet support. For truly large files, DuckDB reads Parquet natively — no conversion pipeline needed.

Quick benchmark on a 100K-row sales CSV:

Installation

pip install duckdb ollama

Text-to-SQL Engine

import duckdb
import ollama

class LocalDataAnalyst:
    """Natural language interface to CSV/Excel files via DuckDB + Ollama."""

    def __init__(self, model: str = "qwen2.5-coder:14b"):
        self.model = model
        self.con = duckdb.connect()
        self.tables = {}

    def load_csv(self, path: str, table_name: str = None):
        """Register a CSV file as a queryable table."""
        name = table_name or path.split("/")[-1].replace(".csv", "").replace("-", "_")
        self.con.execute(
            f"CREATE OR REPLACE TABLE {name} AS SELECT * FROM read_csv_auto('{path}')"
        )
        schema = self.con.execute(f"DESCRIBE {name}").fetchall()
        row_count = self.con.execute(f"SELECT COUNT(*) FROM {name}").fetchone()[0]
        sample = self.con.execute(f"SELECT * FROM {name} LIMIT 3").fetchdf().to_string()

        self.tables[name] = {"schema": schema, "rows": row_count, "sample": sample}
        print(f"Loaded '{name}': {row_count:,} rows, {len(schema)} columns")
        return self

    def load_excel(self, path: str, sheet: str = None, table_name: str = None):
        """Register an Excel sheet as a queryable table."""
        import pandas as pd
        df = pd.read_excel(path, sheet_name=sheet)
        name = table_name or path.split("/")[-1].replace(".xlsx", "")
        self.con.register(name, df)
        schema = self.con.execute(f"DESCRIBE {name}").fetchall()
        self.tables[name] = {
            "schema": schema, "rows": len(df),
            "sample": df.head(3).to_string()
        }
        print(f"Loaded '{name}': {len(df):,} rows")
        return self

    def _schema_context(self) -> str:
        """Build concise schema description for the LLM."""
        parts = []
        for name, info in self.tables.items():
            cols = ", ".join(f"{c[0]} ({c[1]})" for c in info["schema"])
            parts.append(
                f"Table: {name} | {info['rows']:,} rows\n"
                f"Columns: {cols}\n"
                f"Sample:\n{info['sample']}"
            )
        return "\n\n".join(parts)

    def ask(self, question: str, explain: bool = False) -> str:
        """Ask a natural language question about your data."""
        prompt = f"""Given this DuckDB database, write a SQL query to answer the question.
Return ONLY the SQL query. No explanations, no markdown fences.

{self._schema_context()}

Question: {question}
SQL:"""

        response = ollama.chat(
            model=self.model,
            messages=[{"role": "user", "content": prompt}],
            options={"temperature": 0.1, "num_predict": 512}
        )

        sql = response["message"]["content"].strip()
        # Strip markdown code fences if the model adds them
        for fence in ["```sql", "```SQL", "```"]:
            sql = sql.replace(fence, "")
        sql = sql.strip()

        try:
            result_df = self.con.execute(sql).fetchdf()
            output = f"SQL: {sql}\n\n{result_df.to_string(index=False)}"
            if explain:
                output = f"SQL: {sql}\n\nExplain: {self.con.execute('EXPLAIN ' + sql).fetchone()[0]}\n\n{result_df.to_string(index=False)}"
            return output
        except Exception as e:
            return f"Query failed.\nSQL: {sql}\nError: {e}"

Usage Examples

analyst = LocalDataAnalyst()
analyst.load_csv("sales_data.csv", "sales")
analyst.load_csv("customers.csv", "customers")

# Simple aggregation
print(analyst.ask("Total revenue by region"))
# SQL: SELECT region, SUM(revenue) as total FROM sales GROUP BY region ORDER BY total DESC
#
#   region    total
#   West      1,284,532
#   East        987,241
#   South       743,891

# Cross-table join
print(analyst.ask("Average order value by customer segment"))
# SQL: SELECT c.segment, AVG(s.revenue) as avg_order
#      FROM sales s JOIN customers c ON s.customer_id = c.id
#      GROUP BY c.segment ORDER BY avg_order DESC

# Time series
print(analyst.ask("Monthly revenue with month-over-month percent change"))
# SQL: WITH monthly AS (
#        SELECT DATE_TRUNC('month', order_date) as month, SUM(revenue) as rev
#        FROM sales GROUP BY 1
#      )
#      SELECT month, rev,
#        ROUND((rev - LAG(rev) OVER (ORDER BY month)) / LAG(rev) OVER (ORDER BY month) * 100, 1) as pct_change
#      FROM monthly ORDER BY month

# Ranking
print(analyst.ask("Top 10 customers by lifetime value with order count"))
# SQL: SELECT c.name, COUNT(*) as orders, SUM(s.revenue) as ltv
#      FROM sales s JOIN customers c ON s.customer_id = c.id
#      GROUP BY c.name ORDER BY ltv DESC LIMIT 10

Handling Large Datasets

For datasets beyond 1M rows, convert to Parquet first. DuckDB reads Parquet with zero-copy, meaning it does not load the entire file into memory:

# One-time conversion
python3 -c "
import duckdb
duckdb.sql(\"COPY (SELECT * FROM read_csv_auto('massive_sales.csv')) TO 'massive_sales.parquet' (FORMAT PARQUET)\")
print('Converted to Parquet')
"

# Query Parquet directly — no loading needed
analyst = LocalDataAnalyst()
analyst.con.execute(
    "CREATE VIEW sales AS SELECT * FROM read_parquet('massive_sales.parquet')"
)
analyst.tables["sales"] = {
    "schema": analyst.con.execute("DESCRIBE sales").fetchall(),
    "rows": analyst.con.execute("SELECT COUNT(*) FROM sales").fetchone()[0],
    "sample": analyst.con.execute("SELECT * FROM sales LIMIT 3").fetchdf().to_string()
}

---

Approach 3: LangChain CSV Agent — Most Flexible {#langchain}

LangChain's CSV agent wraps pandas in a reasoning loop. It generates code, executes it, inspects the result, and self-corrects if something looks wrong. This multi-step reasoning is what makes it the most accurate approach for complex analytical questions.

Installation

pip install langchain langchain-ollama langchain-experimental pandas tabulate

Setup

from langchain_ollama import ChatOllama
from langchain_experimental.agents import create_csv_agent
from langchain.agents.agent_types import AgentType

llm = ChatOllama(model="qwen2.5-coder:14b", temperature=0.1)

agent = create_csv_agent(
    llm,
    "sales_data.csv",
    agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,
    verbose=True,
    allow_dangerous_code=True,
    pandas_kwargs={"encoding": "utf-8"},
)

The Self-Correction Advantage

Watch the agent work through a complex question:

result = agent.invoke(
    "What percentage of customers who bought Product A also bought Product B within 30 days?"
)

The agent's reasoning chain (visible with verbose=True):

Thought: I need to find customers who bought Product A, then check if they
also bought Product B within 30 days.

Action: python_repl_ast
Action Input: df[df['product'] == 'Product A']['customer_id'].nunique()
Observation: 1,247

Thought: Now I need to find which of those customers also bought Product B
within 30 days of their Product A purchase.

Action: python_repl_ast
Action Input:
a_purchases = df[df['product'] == 'Product A'][['customer_id', 'order_date']]
b_purchases = df[df['product'] == 'Product B'][['customer_id', 'order_date']]
merged = a_purchases.merge(b_purchases, on='customer_id', suffixes=('_a', '_b'))
merged['days_diff'] = (pd.to_datetime(merged['order_date_b']) - pd.to_datetime(merged['order_date_a'])).dt.days
cross_buy = merged[(merged['days_diff'] >= 0) & (merged['days_diff'] <= 30)]['customer_id'].nunique()
cross_buy
Observation: 312

Thought: I have both numbers. 312 / 1247 = 25.0%
Final Answer: 25.0% of customers who bought Product A also bought Product B within 30 days (312 out of 1,247 customers).

Neither PandasAI nor the DuckDB approach can handle this question reliably. The LangChain agent decomposes it into steps, executes each one, and combines the results.

Multi-File Agent

agent = create_csv_agent(
    llm,
    ["orders.csv", "customers.csv", "products.csv"],
    agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,
    verbose=True,
    allow_dangerous_code=True,
)

result = agent.invoke(
    "Which customer segment shows the fastest growing average order value "
    "over the last 6 months? Show the trend."
)
print(result["output"])

LangChain Limitations

• Slow: 8-15 seconds per query because of the multi-step reasoning loop. Each self-correction adds another LLM call.
• Unpredictable cost: Some queries take 2 steps, others take 6. Hard to guarantee response time.
• Security risk: The agent runs arbitrary Python code. Never expose this to untrusted users without sandboxing (Docker, gVisor, etc.).

---

Real Benchmark: 100K-Row Sales Dataset {#benchmark}

I generated a synthetic sales dataset matching a realistic schema: 100K orders, 5K customers, 50 products, 5 regions, 24 months of transaction dates. I ran 20 real business questions through all three approaches.

Accuracy and Speed Comparison

Accuracy = correct answer. Speed = wall-clock from question to answer. Model: Qwen 2.5 Coder 14B on RTX 3060.

DuckDB wins the overall value proposition. It is fast, accurate for structured queries, and scales to millions of rows. LangChain wins on complex multi-step questions but at the cost of 3x slower response times.

---

Building the Streamlit Web UI {#streamlit-ui}

Technical users are fine with Python scripts. For everyone else on your team, a web interface makes this accessible. If you are interested in training models on your own domain data, the guide to training AI on your own data covers fine-tuning and RAG approaches.

"""
streamlit_data_analyst.py
Run: streamlit run streamlit_data_analyst.py
"""
import streamlit as st
import pandas as pd
import duckdb
import ollama

st.set_page_config(page_title="Local Data Analyst", layout="wide")
st.title("Ask Questions About Your Data")
st.caption("100% local. Your data never leaves this machine.")

# Sidebar: model selection
model = st.sidebar.selectbox(
    "AI Model",
    ["qwen2.5-coder:14b", "deepseek-r1:14b", "llama3.2"],
    index=0
)

uploaded = st.file_uploader("Upload CSV or Excel", type=["csv", "xlsx"])

if uploaded:
    if uploaded.name.endswith(".csv"):
        df = pd.read_csv(uploaded)
    else:
        df = pd.read_excel(uploaded)

    st.write(f"**{len(df):,} rows** | **{len(df.columns)} columns**")

    with st.expander("Preview Data", expanded=False):
        st.dataframe(df.head(50))

    # Register in DuckDB
    con = duckdb.connect()
    con.register("data", df)
    schema = con.execute("DESCRIBE data").fetchall()
    schema_str = ", ".join(f"{c[0]} ({c[1]})" for c in schema)
    sample_str = df.head(3).to_string()

    # Chat history
    if "history" not in st.session_state:
        st.session_state.history = []

    for msg in st.session_state.history:
        with st.chat_message(msg["role"]):
            st.write(msg["content"])
            if "df" in msg:
                st.dataframe(msg["df"])

    if question := st.chat_input("Ask about your data..."):
        st.session_state.history.append({"role": "user", "content": question})

        prompt = f"""Write a DuckDB SQL query for the table 'data' to answer: {question}
Columns: {schema_str}
Sample rows:\n{sample_str}
Return ONLY the SQL query, nothing else."""

        with st.spinner("Thinking..."):
            resp = ollama.chat(
                model=model,
                messages=[{"role": "user", "content": prompt}],
                options={"temperature": 0.1}
            )
            sql = resp["message"]["content"].strip()
            for tag in ["```sql", "```SQL", "```"]:
                sql = sql.replace(tag, "")
            sql = sql.strip()

            try:
                result_df = con.execute(sql).fetchdf()
                answer = f"**Query:** `{sql}`"
                st.session_state.history.append({
                    "role": "assistant", "content": answer, "df": result_df
                })
                with st.chat_message("assistant"):
                    st.code(sql, language="sql")
                    st.dataframe(result_df)
                    if len(result_df.columns) >= 2:
                        numeric = result_df.select_dtypes(include="number").columns
                        if len(numeric) >= 1:
                            st.bar_chart(result_df.set_index(result_df.columns[0]))
            except Exception as e:
                err = f"Query failed: `{sql}`\n\nError: {e}"
                st.session_state.history.append({"role": "assistant", "content": err})
                with st.chat_message("assistant"):
                    st.error(err)

pip install streamlit
streamlit run streamlit_data_analyst.py
# Opens at http://localhost:8501

Upload a CSV, type "top 10 customers by revenue," and get a table with an auto-generated bar chart. No data leaves localhost.

---

Performance at Scale {#performance}

The LLM is always the bottleneck, not the data engine. DuckDB processes a GROUP BY on a million-row table in 45ms. The LLM takes 3-4 seconds to generate the SQL regardless of table size.

DuckDB query time measured on an 8-core Ryzen 7 5800X. LLM generation on RTX 3060 with Qwen 2.5 Coder 14B.

Scaling to larger datasets does not meaningfully affect user experience. The difference between querying 1K rows and 10M rows is less than 1 second.

---

Handling Messy Real-World Data {#edge-cases}

Ambiguous Column Names

The single biggest source of wrong queries. If your CSV has columns named "value," "data," or "col1," rename them before loading:

df = pd.read_csv("messy_export.csv")
# Standardize column names
df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_").str.replace("-", "_")
# Rename ambiguous ones
df = df.rename(columns={
    "value": "order_revenue_usd",
    "date": "transaction_date",
    "name": "customer_full_name",
    "type": "product_category"
})

This single step improved my DuckDB accuracy from 78% to 90% on a dataset with vague column names.

Null Values

Add null context to the prompt so the model generates COALESCE or handles NULLs properly:

null_counts = df.isnull().sum()
null_info = null_counts[null_counts > 0]
# Append to your LLM prompt:
# f"Note: columns with nulls: {null_info.to_dict()}"

Mixed Date Formats

DuckDB auto-detects most date formats, but mixed formats within a column cause problems. Standardize beforehand:

df["order_date"] = pd.to_datetime(df["order_date"], format="mixed", dayfirst=False)

---

Which Approach Should You Use {#choosing}

My default is DuckDB for everything. I reach for LangChain only when a question requires multi-step reasoning that SQL cannot express in a single query. I use PandasAI when I want quick charts during ad-hoc exploration.

For other local AI business applications beyond data analysis — document processing, email triage, meeting transcription — see the local AI for small business guide.

---

Frequently Asked Questions

Q: Can local AI analyze data as well as ChatGPT?

A: For structured queries (aggregations, filters, joins), Qwen 2.5 Coder 14B running locally achieves 86% accuracy — comparable to GPT-4. ChatGPT has a wider lead on open-ended data exploration requiring narrative interpretation, but for getting concrete numbers from spreadsheets, local models are reliable.

Q: What is the maximum CSV file size I can analyze?

A: With DuckDB, effectively unlimited. DuckDB reads files in a streaming fashion and uses columnar compression. I have tested with 50M-row Parquet files. The LLM response time stays constant regardless of file size because only the schema and a 3-row sample are sent as context.

Q: Do I need a GPU for this?

A: Not strictly. Qwen 2.5 Coder 14B runs on CPU at around 3 tokens/second — queries take 15-20 seconds instead of 3-4 seconds. For interactive use, a GPU makes a meaningful difference. For batch analysis (running 50 questions overnight), CPU is fine.

Q: Can I analyze Excel files with multiple sheets?

A: Yes. Load each sheet as a separate DuckDB table. The LLM can then generate JOIN queries across sheets as long as they share a common key column.

Q: What about data visualization — can it generate charts?

A: The Streamlit UI auto-generates bar charts for numeric results. For custom visualizations, instruct the LangChain agent to generate matplotlib or plotly code. PandasAI includes built-in chart support.