From Excel to SQL: A Practical Workflow for Analysts Who Inherit Spreadsheet Chaos

Many analyst jobs do not start with a clean warehouse, a perfect semantic layer, and a tidy backlog. They start with spreadsheet tabs, CSV exports, copied formulas, manual status updates, and one person who knows which column is safe to trust.

If you are trying to move that work into SQL, the hard part is usually not syntax. The hard part is designing a workflow that keeps the business moving while reducing manual work, duplicate files, and silent errors. This guide gives you a practical path from spreadsheet chaos to a reliable SQL workflow without pretending every small team needs a large data platform on day one.

Start by mapping the current workflow, not the tables

Before you create a database table, write down what actually happens today. A useful map includes four things:

• Inputs: CSV exports, Excel workbooks, form submissions, vendor reports, manual entries, or API downloads.
• Decisions: where someone reviews rows, fixes statuses, removes duplicates, or chooses which records need follow-up.
• Outputs: dashboards, customer reports, updated trackers, invoices, dispatch lists, or files sent to another system.
• Failure points: formulas copied down incorrectly, overwritten files, inconsistent names, dropped leading zeros, date parsing issues, and unclear ownership.

This map tells you which parts belong in SQL first. A good first project is not "replace every spreadsheet." A good first project is "stop rebuilding the same status summary every morning" or "make the weekly import reproducible."

Use a staging table before you clean anything

The safest SQL workflow keeps the raw import separate from the cleaned version. This lets you reload a file, audit what changed, and explain why a row was excluded.

CREATE TABLE raw_inspections_import (
    import_id        integer,
    source_file      varchar(255),
    imported_at      timestamp,
    address_id       varchar(100),
    address_text     varchar(500),
    inspection_date  varchar(100),
    status_text      varchar(100),
    crew_notes       varchar(2000)
);

CREATE TABLE inspection_status_clean (
    import_id        integer,
    address_id       varchar(100),
    inspection_date  date,
    status           varchar(50),
    needs_follow_up  boolean,
    crew_notes       varchar(2000)
);

The raw table stores what came in. The clean table stores what the business means after validation. Do not overwrite raw values with "fixed" values unless you also keep a trace of the original.

Normalize statuses with a lookup table

Spreadsheets often contain status values like Done, complete, Completed, COMP, Skipped, and N/A. Do not solve that by writing one giant CASE expression everywhere. Put the mapping in a small table.

CREATE TABLE status_map (
    source_status varchar(100) primary key,
    clean_status  varchar(50) not null,
    needs_follow_up boolean not null
);

INSERT INTO status_map (source_status, clean_status, needs_follow_up) VALUES
    ('Done', 'completed', false),
    ('Complete', 'completed', false),
    ('Completed', 'completed', false),
    ('Skipped', 'skipped', true),
    ('No Access', 'blocked', true);

Then your cleaning query becomes simple and auditable:

INSERT INTO inspection_status_clean (
    import_id,
    address_id,
    inspection_date,
    status,
    needs_follow_up,
    crew_notes
)
SELECT
    r.import_id,
    r.address_id,
    CAST(r.inspection_date AS date),
    COALESCE(m.clean_status, 'unknown') AS status,
    COALESCE(m.needs_follow_up, true) AS needs_follow_up,
    r.crew_notes
FROM raw_inspections_import r
LEFT JOIN status_map m
    ON LOWER(TRIM(r.status_text)) = LOWER(TRIM(m.source_status));

The key habit is this: unknown values should stay visible. If a new vendor export introduces Unable to inspect, you want the workflow to flag it rather than silently treating it as complete.

Define the grain before joining files together

Most spreadsheet-to-SQL mistakes come from unclear grain. Grain means what one row represents. Is one row an address, one inspection attempt, one workday, one crew assignment, or one final status per address?

Write the grain in plain English before writing the query:

• Address master: one row per address per project.
• Inspection events: one row per address per inspection attempt.
• Current status: one row per address showing the latest trusted status.
• Daily crew summary: one row per crew per workday.

If you join an address master to inspection events and then count addresses, you may inflate counts because each address can have multiple attempts. Reduce the event table to one row per address before joining when you need current status.

WITH ranked_events AS (
    SELECT
        e.*,
        ROW_NUMBER() OVER (
            PARTITION BY e.address_id
            ORDER BY e.inspection_date DESC, e.import_id DESC
        ) AS rn
    FROM inspection_status_clean e
)
SELECT
    a.project_id,
    a.address_id,
    a.address_text,
    e.status,
    e.needs_follow_up,
    e.inspection_date
FROM address_master a
LEFT JOIN ranked_events e
    ON a.address_id = e.address_id
   AND e.rn = 1;

Keep Excel or Power Query where it still helps

Moving to SQL does not mean banning spreadsheets. Excel and Power Query are useful for review, lightweight input, and handoff. The problem is using them as the hidden source of truth.

A practical division of labor looks like this:

• SQL: store raw imports, validate rows, join reference data, calculate repeatable metrics, and produce stable outputs.
• Power Query: pull approved SQL outputs into Excel, refresh reports, and do lightweight reshaping for business users.
• Excel: review exceptions, annotate follow-up decisions, and present small operational lists.

If the spreadsheet needs to send corrections back, make that explicit. Create a review table with controlled fields instead of letting people edit a downloaded report and email it around.

Add validation queries before dashboards

Do not wait until the dashboard looks wrong to validate the pipeline. Add small checks after each import.

-- 1. Did row volume change unexpectedly?
SELECT import_id, COUNT(*) AS row_count
FROM raw_inspections_import
GROUP BY import_id
ORDER BY import_id DESC;

-- 2. Which statuses are unmapped?
SELECT status_text, COUNT(*) AS rows
FROM raw_inspections_import r
LEFT JOIN status_map m
    ON LOWER(TRIM(r.status_text)) = LOWER(TRIM(m.source_status))
WHERE m.source_status IS NULL
GROUP BY status_text
ORDER BY rows DESC;

-- 3. Are there duplicate address rows in the master file?
SELECT project_id, address_id, COUNT(*) AS rows
FROM address_master
GROUP BY project_id, address_id
HAVING COUNT(*) > 1;

These checks are not busywork. They are what let you trust the automation when the source files change.

Choose a first production workflow

If you are overwhelmed, do not start with a full warehouse design. Start with one repeatable workflow:

1. Pick one recurring spreadsheet report or CSV export.
2. Load it into a raw table without changing values.
3. Create one clean table or view with typed dates, normalized statuses, and documented assumptions.
4. Add three validation queries: row count, unknown categories, and duplicate keys.
5. Publish one output view that Excel, Power BI, or a dashboard can refresh.
6. Run it for two cycles while comparing against the old manual process.

The goal is not to prove that SQL is more sophisticated. The goal is to remove manual repetition while making mistakes easier to find.

Common traps to avoid

• Cleaning before storing raw data: you lose the audit trail and cannot explain differences later.
• Using DISTINCT to fix duplicate joins: it hides the grain problem instead of solving it.
• Letting Excel auto-format IDs: leading zeros, long numbers, and date-like strings can change before SQL ever sees them.
• Mixing manual and automated truth: if a spreadsheet correction matters, store it in a controlled correction table.
• Skipping ownership: someone must own status mappings, validation failures, and reload decisions.

FAQ

Should analysts learn SQL before Power Query?

Learn enough SQL to understand tables, joins, filters, grouping, and validation. Power Query is useful, but SQL gives you a stronger foundation for shared data logic and repeatable workflows.

Do I need a data warehouse for a small business workflow?

Not necessarily. A single relational database with raw tables, clean views, and documented refresh steps can be enough. Add warehouse tooling when volume, access control, or team size justifies it.

What is the best first SQL automation project?

Choose a recurring report with stable inputs and obvious manual pain. Avoid the most politically sensitive workflow until you have one smaller success and a validation pattern people trust.