Query a Database for IDs Listed in an Excel Sheet

"Can you pull the records for these?" arrives as a spreadsheet with one column of IDs — a few dozen customer numbers, or several thousand order references exported from another system. The lookup itself is a one-line SQL query, but getting the IDs from the sheet into that query correctly takes care: the column is rarely clean, the list may be longer than a single statement allows, pasting values into SQL is how injection bugs and quoting errors happen, and the person asking wants to know which IDs came back empty. This page, part of Moving Data Between Excel and Databases, handles each of those in turn using sqlite3; the same pattern works with any database driver or SQLAlchemy.

Prerequisites

pip install pandas openpyxl

crm.db is a SQLite database with a customers table of 5,000 rows keyed by an integer customer_id. lookup_request.xlsx has 1,208 IDs in a "Customer ID" column, and the first eight show the usual problems: a duplicate, a trailing space, a blank, an ID that does not exist, a malformed value and a float, 1046.0.

Step 1 — Read and clean the IDs

Read the column as text so nothing is silently converted, then strip spaces, drop blanks and duplicates, and convert to the type of the database column. Keep anything that does not convert, so it can be reported rather than lost:

import pandas as pd

raw = pd.read_excel("lookup_request.xlsx", dtype={"Customer ID": str})["Customer ID"]
print(raw.head(8).tolist())

text = raw.str.strip().str.replace(r"\.0$", "", regex=True)
text = text[text.notna() & (text != "")].drop_duplicates()
is_number = text.str.fullmatch(r"\d+")
bad_ids = text[~is_number].tolist()
ids = text[is_number].astype(int).tolist()
print(len(raw), "rows ->", len(ids), "usable IDs; rejected:", bad_ids)

dtype=str keeps every value as text. The float 1046.0 was saved as the whole number 1046, so it already arrives as "1046"; the regular expression that trims a trailing .0 is a safeguard for sheets where IDs really were stored with decimals. The trailing space on "1043 " goes with strip(). The blank and the second 1042 disappear, and "10-45" is rejected as not a number — it goes in the report rather than into the query. Deduplicating keeps the first occurrence, so the list stays in the sheet's order.

Step 2 — Query with parameters, not string formatting

The tempting shortcut is to join the IDs into the SQL text: f"... IN ({', '.join(map(str, ids))})". It works for integers until the first ID is text — then it needs quotes, and a value containing a quote breaks the statement or, worse, changes what it does. Placeholders avoid all of it: the SQL has one ? per ID and the driver sends the values separately:

import sqlite3

con = sqlite3.connect("crm.db")
sample = ids[:5]
sql = f"SELECT customer_id, name, region, balance FROM customers WHERE customer_id IN ({', '.join(['?'] * len(sample))})"
print(sql)
print(pd.read_sql_query(sql, con, params=sample))

The f-string only builds the placeholders, never the values. 99999 has no customer, so five IDs return four rows — Step 5 reports the gap. The placeholder style depends on the driver: ? for sqlite3 and pyodbc, %s for psycopg and pymysql, :name for oracledb. With SQLAlchemy, text("... IN :ids") plus bindparam("ids", expanding=True) generates the placeholders for you.

Step 3 — Split long lists into chunks

Every ID is one parameter, and drivers limit how many a statement may carry: 999 in SQLite builds before 3.32, 32,766 after, 2,100 in SQL Server and 65,535 in PostgreSQL's protocol. A request for a few thousand IDs will hit one of those somewhere. Query in chunks below the smallest limit you might meet and stack the results:

def fetch_by_ids(con, ids, chunk_size=900):
    frames = []
    for start in range(0, len(ids), chunk_size):
        chunk = ids[start:start + chunk_size]
        sql = f"SELECT customer_id, name, region, balance FROM customers WHERE customer_id IN ({', '.join(['?'] * len(chunk))})"
        frames.append(pd.read_sql_query(sql, con, params=chunk))
    return pd.concat(frames, ignore_index=True)

found = fetch_by_ids(con, ids)
print(len(ids), "IDs in", -(-len(ids) // 900), "queries ->", len(found), "rows")

A chunk size of 900 is safe everywhere. Each chunk is an independent query, so a long list costs a few round trips rather than one — negligible for thousands of IDs, but noticeable for hundreds of thousands, where the next approach is better.

Step 4 — Join against a temporary table for very long lists

For tens of thousands of IDs, or when the lookup needs more than one key column, load the IDs into a temporary table and let the database join. One insert and one query replace dozens of chunks, and the database can use its indexes for the join:

pd.DataFrame({"customer_id": ids}).to_sql("wanted_ids", con, if_exists="replace", index=False)
con.execute("CREATE INDEX IF NOT EXISTS ix_wanted ON wanted_ids (customer_id)")
joined = pd.read_sql_query(
    "SELECT c.customer_id, c.name, c.region, c.balance FROM customers c JOIN wanted_ids w USING (customer_id)", con)
con.execute("DROP TABLE wanted_ids")
print(len(joined), "rows; same as chunked:", sorted(joined.customer_id) == sorted(found.customer_id))

The join returns exactly the rows the chunked query did. On a shared production database, use a real temporary table (CREATE TEMP TABLE in SQLite and PostgreSQL, a #name table in SQL Server) so it vanishes with the connection and cannot collide with another user's lookup; you may need permission to create one. Load an Excel File into a SQL Database with pandas covers to_sql in more depth.

Step 5 — Keep the sheet's order and report what was missing

SQL returns rows in no guaranteed order and silently omits IDs it could not find. Merge the results back onto the cleaned ID list with a left merge: the sheet's order is preserved, and missing IDs show up as rows with no data. indicator=True adds a column saying which is which:

result = pd.DataFrame({"customer_id": ids}).merge(found, on="customer_id", how="left", indicator=True)
result["found"] = result.pop("_merge").eq("both")
missing = result.loc[~result.found, "customer_id"].tolist()
print(result.head(6).to_string(index=False))
print("not found:", missing, "| rejected:", bad_ids)

Of 1,205 usable IDs, 1,204 were found; 99999 was not, and "10-45" was rejected before querying. Report both — they need different fixes from the person who sent the list.

Step 6 — Write the answer back to Excel

Send back one workbook with the results in the sheet's order and a second sheet listing every ID that could not be matched, with the reason:

problems = pd.DataFrame({"Customer ID": [str(m) for m in missing] + bad_ids,
                         "Problem": ["not in database"] * len(missing) + ["not a valid ID"] * len(bad_ids)})
with pd.ExcelWriter("lookup_result.xlsx") as writer:
    result.to_excel(writer, sheet_name="Results", index=False)
    problems.to_excel(writer, sheet_name="Not found", index=False)
print(pd.read_excel("lookup_result.xlsx", sheet_name=None)["Not found"])
con.close()

Common pitfalls

• Formatting values into SQL. Use placeholders; build only the ? list with string operations.
• Reading IDs as numbers. Leading zeros vanish and 1046.0 appears; read as text, clean, then convert.
• Mismatched types. Compare integers with an integer column and text with a text column.
• Forgetting the parameter limit. Chunk below 900, or use a temporary table.
• Trusting SQL order. Merge back onto the ID list to keep the requester's order and see gaps.
• Dropping bad IDs silently. Report malformed and missing IDs separately.

Performance notes

Make sure the lookup column is indexed — a primary key is. Chunked IN queries handle a few thousand IDs in well under a second; beyond tens of thousands, the temporary-table join is faster and puts less load on the driver. Select only the columns the requester needs rather than SELECT *.

Conclusion

Read the ID column as text, strip, deduplicate and convert it to the database column's type, keeping malformed values aside. Query with one placeholder per ID, in chunks of 900 or fewer, or load the IDs into a temporary table and join when the list is long. Merge the results back onto the ID list to keep the sheet's order, and send back the matches together with a list of IDs that were missing or invalid.

Frequently asked questions

How do I pass a list of IDs from Excel into a SQL IN clause? Build one placeholder per ID — ", ".join("?" * len(ids)) for sqlite3, or %s for psycopg — put them inside IN ( ), and pass the list as the parameters. Never paste the values into the SQL string yourself.

Why does my query fail with "too many SQL variables" or a parameter limit error? Every ID is a separate parameter and drivers cap how many one statement may have — 999 in older SQLite builds, 2,100 in SQL Server. Split the list into chunks below the limit and concatenate the results, or load the IDs into a temporary table and join.

Why do some IDs from the sheet not match even though they exist? Usually a type or formatting difference. Excel may store 1043 as the text "1043 " with a space, or as the float 1046.0, while the column is an integer. Read the IDs as text, strip them, and convert them to the column's type before querying.

How do I keep the results in the same order as the Excel list? SQL returns rows in whatever order it likes. Merge the results back onto the cleaned ID list with a left merge — that keeps the sheet's order and leaves blank rows for IDs that were not found.

Related

• Up one level: Moving Data Between Excel and Databases — every direction data moves.
• Export SQL Query Results to Excel with Python — formatted query output.
• Upsert Excel Rows into a Database Table — writing sheet rows back.
• Fix Long ID Numbers Losing Digits in Excel — keeping IDs intact.