Merge Excel Lists with SQL Extracts Without Losing Rows

When to use this skill

• You have a list in Excel (account numbers, vendor IDs, employee IDs) and need to enrich it with fields pulled from a SQL database.
• You’ve been burned by a VLOOKUP or merge that silently dropped rows, silently duplicated rows, or matched nothing because one side stored the key as text and the other as a number.
• Someone on the team is building SQL strings by pasting IDs into the query text. Stop them — that’s an injection risk and a formatting time bomb.

The pattern: pull from the database with a parameterized query, then merge with how='left', indicator=True, and validate= so every row is accounted for and any fan-out is caught immediately.

Inputs it expects

• Excel list: one row per item you care about, with a clean key column (e.g., account_id).
• Database connection: pyodbc/SQLAlchemy connection string to the source system.
• Expected relationship: know before you merge whether the database should return one row per key (one_to_one) or many (one_to_many). This becomes the validate= argument.
• Keys on both sides in the same dtype — cast both to string (and strip whitespace/leading zeros consistently) before merging.

Steps

1. Load the Excel list and record its row count. This is your control total — the output must have at least this many rows, and exactly this many if the relationship is one-to-one.
2. Build a parameterized IN (...) query with ? placeholders — never f-string the values into the SQL text. For 1,000+ keys, query in batches.
3. Pull the extract and record its row count too.
4. Normalize the key columns on both sides (dtype, strip, case).
5. Merge with how='left' (keep every Excel row), indicator=True (label each row’s match status), and validate= (blow up loudly if the shape isn’t what you expected).
6. Tabulate _merge: matched + unmatched must equal the Excel total. Export the left_only rows as the exception list.

Code

import pandas as pd
import pyodbc

# 1. Load the Excel list — this row count is the control total
excel_df = pd.read_excel('accounts.xlsx')          # account_id, account_desc, gl_balance
rows_in = len(excel_df)
excel_df['account_id'] = excel_df['account_id'].astype(str).str.strip()
account_list = excel_df['account_id'].unique().tolist()

# 2. Parameterized query — placeholders, never string-pasted values
conn = pyodbc.connect('Driver={SQL Server};Server=MyServer;Database=MyDB;Trusted_Connection=yes;')

def query_in_batches(conn, values, batch_size=1000):
    """IN-list queries in safe batches (SQL Server caps parameters)."""
    frames = []
    for i in range(0, len(values), batch_size):
        batch = values[i:i + batch_size]
        placeholders = ','.join(['?'] * len(batch))
        sql = f"""
            SELECT Account_ID AS account_id, Account_Name, Current_Balance
            FROM Accounts
            WHERE Account_ID IN ({placeholders})
        """
        frames.append(pd.read_sql_query(sql, conn, params=batch))
    return pd.concat(frames, ignore_index=True)

db_df = query_in_batches(conn, account_list)
db_df['account_id'] = db_df['account_id'].astype(str).str.strip()
print(f"Excel rows: {rows_in:,}  |  DB rows returned: {len(db_df):,}")

# 3. Safe merge: keep every Excel row, label match status, enforce shape
merged = pd.merge(
    excel_df,
    db_df,
    on='account_id',
    how='left',
    indicator=True,
    validate='one_to_one'   # use 'one_to_many' only if fan-out is genuinely expected
)

# 4. Account for every row
counts = merged['_merge'].value_counts()
matched = counts.get('both', 0)
unmatched = counts.get('left_only', 0)
print(f"Matched: {matched:,}  |  Unmatched: {unmatched:,}  |  Total: {len(merged):,}")

assert matched + unmatched == rows_in, "Rows gained or lost in merge — investigate before using output!"

merged[merged['_merge'] == 'left_only'].drop(columns='_merge') \
    .to_excel('EXCEPTIONS_not_in_database.xlsx', index=False)
merged.drop(columns='_merge').to_excel('accounts_with_data.xlsx', index=False)

Validation (control totals)

• Rows in vs. rows out: with how='left' and validate='one_to_one', len(merged) must equal rows_in exactly. If you allowed one_to_many, rows out will exceed rows in — reconcile the difference to the expected fan-out, don’t shrug at it.
• Matched + unmatched = total: counts['both'] + counts['left_only'] == rows_in. Put all three numbers in the workpaper.
• Amount tie-out: excel_df['gl_balance'].sum() must equal merged['gl_balance'].sum() — a left merge never changes left-side totals; if it did, rows duplicated.
• validate= as tripwire: if pd.merge raises MergeError, the database has duplicate keys. That’s a finding, not a nuisance — do not “fix” it by dropping duplicates without understanding why they exist.

Exceptions to surface

• Every row in EXCEPTIONS_not_in_database.xlsx (_merge == 'left_only'): accounts on the Excel list the database doesn’t know about — typos, closed accounts, wrong system, or something worse.
• Duplicate keys in the database extract (caught by validate= or db_df['account_id'].duplicated()): a reviewer must decide which record is authoritative.
• Keys that only matched after normalization (dtype/whitespace/leading-zero fixes): note them so the source-system formatting issue gets fixed upstream.
• DB rows returned that exceed the distinct keys requested — a sign the key isn’t unique in the source table.