Chat with CSV & Excel Files Using Local AI

Published on April 11, 2026 — 18 min read

Short answer: you can query a CSV or Excel file in plain English on your own machine by pairing a local code model (via Ollama) with a data engine. Three stacks do this well — PandasAI for quick exploration, DuckDB text-to-SQL for anything that has to scale, and a LangChain CSV agent for multi-step questions. DuckDB is the one most people should start with, because the LLM only ever sees your column names and three sample rows, never the data itself.

The rest of this guide is the setup for all three, the failure modes each one has, and how to work out what response times your own hardware will produce.

---

Why should spreadsheet data stay on your machine? {#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, "we uploaded our entire customer database to an AI chatbot" does not read well in a breach notification.

There is a cost argument too, and it is worth doing the arithmetic yourself rather than trusting a headline figure:

cloud cost per session = rows x tokens per row x price per input token

A CSV row with a dozen short columns lands somewhere around 30-60 tokens once tokenised, so 100,000 rows is a few million tokens per pass. Multiply by your provider's current published input rate — the number moves, so look it up — and by the number of questions you ask. Locally, that whole term collapses: the marginal cost of the tenth question is the electricity used to answer it.

The local AI privacy guide covers the broader privacy argument. This article is the practical setup.

---

Which local model should write your 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. It never sees the rows. That means the model only has to be good at one narrow thing — turning a question plus a schema into valid code — and it means a mid-size coder model is usually enough.

What will actually fit on your card. Quantised weights are close to linear in parameter count. At Q4_K_M the rule of thumb is:

VRAM for weights (GB) ~= 0.6 x parameters in billions
total VRAM ~= weights + 1-2 GB for KV cache and context

Applying that formula to the models people usually shortlist for text-to-SQL:

Note what the formula says about the common claim that "a 14B fits in 8 GB at Q4": it does not. 14 x 0.6 = 8.4 GB of weights before you have allocated a single token of context. On an 8 GB card you either drop to a 7-8B model or accept CPU offload for the overflow layers, which is much slower.

Which one is most accurate? Do not take a number from a blog post — including this one. Text-to-SQL is one of the few LLM tasks with a serious public leaderboard, and it is re-run as models ship. Check the BIRD text-to-SQL benchmark for the current standings on realistic, dirty databases, and Spider for the classic cross-domain set. The general pattern those leaderboards show is stable even as the names change: coder-tuned models beat general chat models of the same size at this task, and the gap between a 7B and a 14B is largest on joins and window functions, not on simple aggregations.

Start with a coder-tuned 14B if you have 12 GB, a coder-tuned 7B if you do not:

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 — the 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

PandasAI with a local model has four recognisable failure modes. None of them are rare, and the fourth is the dangerous one:

1. Syntactically invalid code — the model calls a pandas method that does not exist. Loud, obvious, harmless.
2. Wrong column reference — with many columns it picks the wrong one for ambiguous names like value or date.
3. Silent wrong answers — the code runs cleanly and returns a plausible number that is not the answer to your question. This is the one that ends up in a board deck.
4. Context blowout on wide files — PandasAI builds its prompt from the frame's structure, so very wide or very large frames push you into slow, truncated, unreliable territory.

Because failure mode 3 exists, PandasAI is for interactive exploration where you can eyeball the result against something you already know. For anything that gets forwarded to someone else, use the DuckDB approach below, where the generated SQL is printed alongside the answer and you can read it.

---

Approach 2: DuckDB text-to-SQL — the one that scales {#duckdb}

DuckDB is an embedded columnar engine that reads CSV and Parquet files directly, without an import step. Pair it with an LLM that writes SQL and you get something a spreadsheet cannot do: the model works from the schema alone, so dataset size has almost no effect on the AI half of the pipeline.

Why DuckDB over pandas or SQLite

Three structural reasons, all of which hold regardless of what any benchmark says today:

1. No import step. Point it at a CSV file and query it immediately. No schema definition, no load wait.
2. Columnar storage. Analytical queries touch a handful of columns out of dozens. A column store reads only those columns off disk; a row store (SQLite) reads every row in full. The advantage grows with table width.
3. Native Parquet. For genuinely large files DuckDB reads Parquet directly, with predicate and projection pushdown, so it never materialises the whole file in memory.

For engine-versus-engine timings, use the source rather than a table in a blog post: DuckDB Labs maintains the H2O.ai db-benchmark, which re-runs groupby and join workloads across DuckDB, pandas, Polars, data.table and others at several data sizes. It is re-published as versions change, which is exactly why hard-coding a number here would be misleading within a release or two.

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 — for multi-step questions {#langchain}

LangChain's CSV agent wraps pandas in a reasoning loop. It generates code, executes it, inspects the result, and can correct itself before answering. That loop is what lets it handle questions a single SQL statement cannot express cleanly — and it is also the reason it is the slowest of the three.

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"},
)

What the reasoning loop buys you

Take a question that needs two dependent lookups. Here is the shape of the trace the agent prints with verbose=True (illustrative — your column names and numbers will differ):

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).

A single-shot approach has to get that entire chain right in one generation. The agent does not — it can look at the intermediate number and decide what to do next.

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's three costs

• Latency multiplies by the number of steps. Every Thought/Action/Observation cycle is a full LLM round trip. A 4-step chain costs roughly four times a single-shot query, plus the time to execute the intermediate pandas. There is no way to make this as fast as one generation — it is doing more work on purpose.
• Unpredictable step count. One question resolves in two steps, another wanders through six. You cannot promise anyone a response time.
• It executes arbitrary Python. allow_dangerous_code=True is not a formality. Never put this behind an input box that untrusted users can reach without sandboxing it (Docker with no network, gVisor, or a dedicated VM).

---

How the three approaches actually differ {#comparison}

There is no single winner, and any table that assigns them accuracy percentages is inventing them. What is stable is the structural trade-off, because it follows from how each one works:

The row that decides it for most people is "can you read what it did". DuckDB prints the SQL next to the answer, so a wrong answer is visible as wrong SQL. That is the difference between a tool you can trust with a number and one you cannot.

---

How do you give non-technical teammates a 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.

---

How fast will this be on your hardware? {#performance}

Do not take a stranger's seconds-per-query figure. Work out your own, because the two halves of this pipeline scale on completely different axes.

The LLM half is roughly constant. The prompt is your schema plus three sample rows — a few hundred tokens whether the table has a thousand rows or ten million. The output is one SQL statement, typically 40-120 tokens. So:

generation time ~= output tokens / tokens-per-second

Get your own tokens-per-second in one command:

# --verbose prints eval rate (tokens/s) after the response
ollama run qwen2.5-coder:14b --verbose "Write a SQL query that sums revenue by region."

Multiply the eval rate into the formula. If your machine reports 30 tokens/s, a 90-token query is about 3 seconds of generation; at 60 tokens/s it is about 1.5. Add prompt-processing time, which is small at these prompt lengths.

The arithmetic ceiling. If you want to know whether a machine is even capable of a given speed before you buy it, generation is memory-bandwidth bound:

upper bound tokens/s = memory bandwidth (GB/s) / model size on disk (GB)

That is a ceiling, not a prediction — real output lands well below it, because of attention over the KV cache, sampling overhead and imperfect memory access patterns. It is still the fastest way to sanity-check a claim. Our VRAM and model-size calculator applies the same arithmetic across cards.

The DuckDB half scales with rows, but from a very low base. Query time grows with data volume; generation time does not. Past a few million rows the engine starts to matter — which is where the Parquet conversion above earns its place — but for the sizes most business spreadsheets reach, the wait a user perceives is almost entirely the model writing SQL.

---

How do you handle 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"
})

Renaming is the single highest-leverage fix available to you, and it is worth understanding why: the model has no rows to disambiguate from. A column called value could be revenue, quantity, a score, or a discount rate. order_revenue_usd cannot be anything else. You are not making the model smarter — you are removing the guess.

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}

The defensible default is DuckDB, for the reason in the comparison table: it is the only one of the three that shows its working. Reach for LangChain when a question genuinely cannot be expressed as one query, and PandasAI when you are poking around and want a chart in one line.

For other local AI business workflows — meeting transcription, email triage, document processing — see the local AI for small business guide.

---

Frequently asked questions

Can local AI analyse data as well as ChatGPT?

For the narrow task this pipeline uses a model for — turning a question plus a schema into SQL — capable local coder models are genuinely competitive, and the public BIRD leaderboard is the place to check the current gap rather than any single blog's claim. The larger frontier models keep a clearer lead on open-ended exploration that requires narrative interpretation of what the numbers mean.

What is the largest CSV file I can analyse?

With DuckDB, far larger than the file will comfortably sit on disk as CSV — convert to Parquet and DuckDB reads it with projection and predicate pushdown rather than loading it whole. The LLM half is unaffected by size at any scale, because only the schema and a three-row sample ever enter the prompt.

Do I need a GPU?

No, but it changes the feel. CPU inference on a quantised 7-14B model is typically an order of magnitude slower than a GPU with the model fully resident in VRAM, because CPU memory bandwidth is roughly an order of magnitude lower than GPU memory bandwidth — and generation speed is bounded by bandwidth divided by model size. Measure your own rate with ollama run --verbose. For overnight batch runs, CPU is perfectly fine.

Can I analyse Excel files with multiple sheets?

Yes. Load each sheet as a separate DuckDB table with load_excel(path, sheet="Q1", table_name="q1"). The model can then write JOINs across sheets as long as they share a key column — and it will only do that reliably if the key columns are named consistently.

What about charts?

The Streamlit UI above auto-plots numeric results as a bar chart. For anything custom, ask the LangChain agent for matplotlib or plotly code; PandasAI has chart generation built in.

Is the generated SQL safe to run?

The DuckDB path executes whatever SQL the model produces, so treat it exactly like SQL from an untrusted source: connect read-only where you can, and never point it at a production database with write credentials. The LangChain path is stricter still — it executes Python, so sandbox it before exposing it to anyone.