Joining Two CSV Files on a Messy Key Without Losing Rows

Joining two CSV files is a one-line operation when the keys are clean and a week of work when they are not. The file from sales has customer ids written 002 with a trailing space; the file from the CRM has them as 002; a third system dropped the zeros altogether. An inner join on the raw text quietly returns the few rows that happen to match, and the report built on it is wrong in a way no error message points to. This article joins two small files with a messy key, counts what each approach keeps, and builds the checks that make a join trustworthy.

The files

customers.csv:

customer_id,name
001,Ana
002,Bo
003,Cy

orders.csv:

order_id,customer_id,amount
10,1,15.00
11,002 ,24.00
12,003,7.50
13,4,12.00

The customers' ids are three-digit text with leading zeros. The orders use 1 (no zeros), 002 (a trailing space) and 003 (clean), and 4, a customer who is not in the file. We want each order with its customer's name, and to know which orders did not match.

Read the keys as text

First, make sure the reader does not decide for you. With default type inference, 001 becomes the integer 1 and the leading zeros vanish silently. Read both keys as text:

CREATE VIEW customers AS SELECT * FROM read_csv('customers.csv', types={'customer_id': 'VARCHAR'});
CREATE VIEW orders    AS SELECT * FROM read_csv('orders.csv',    types={'customer_id': 'VARCHAR'});

Reading as text shows you the real values, including the trailing space, which a numeric read would have hidden by trimming. You can normalize deliberately afterwards; you cannot recover what the reader discarded.

The strict join

SELECT o.order_id, o.customer_id, c.name
FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

This returns one row: order 12 with Cy. Order 10 has 1 against 001; order 11 has 002 against 002; order 13 has 4, which really has no match. Three of four orders are gone, and one of the three should have matched. The query succeeded, produced a plausible-looking table and did not warn. Always compare the row count of an inner join with the row count of the table you meant to keep. Here 1 of 4 is a red flag.

Normalize both sides the same way

The keys are numbers written in different ways, so the normalization is a trim and an integer cast, applied to both sides:

SELECT o.order_id, o.customer_id AS raw_key, c.name
FROM orders o
LEFT JOIN customers c
  ON try_cast(trim(o.customer_id) AS INTEGER) = try_cast(trim(c.customer_id) AS INTEGER)
ORDER BY o.order_id;

Three of the four now match, and the LEFT JOIN keeps order 13 with a NULL name, which is correct: it has no customer. try_cast returns NULL for a value that is not a number (such as C-003) instead of failing, so those rows go to the unmatched pile and you can look at them. The key to doing this safely is that the same function runs on both sides: if you trim one table and not the other, you have swapped one mismatch for another.

List what did not match

The anti-join answers "which rows did not find a partner":

SELECT o.*
FROM orders o
LEFT JOIN customers c
  ON try_cast(trim(o.customer_id) AS INTEGER) = try_cast(trim(c.customer_id) AS INTEGER)
WHERE c.customer_id IS NULL;

It returns order 13. In a real file this list is the work: read it. Each unmatched key is either a customer missing from the lookup, a key with a formatting variant you have not handled (a prefix, a hyphen, a typo) or junk. Do the same in the other direction (customers with no orders) if that matters. The validating against a lookup table article has the NOT IN trap with NULLs that you must avoid here too.

Duplicates in the lookup

If the lookup has a key twice, every order with that key is matched twice, and totals double:

SELECT customer_id, count(*) FROM customers GROUP BY 1 HAVING count(*) > 1;

Run this before any join, and require count(*) = count(DISTINCT key) on the side that should be unique. After a normalization the check matters more: 001 and 1 are different rows before it and the same key after, so a lookup that was unique can become duplicated. Finding duplicate keys covers resolving them. A good habit is to join, then compare count(*) of the result with the left table's count: for a left join to a unique lookup they must be equal. Here: 4 and 4.

When normalization is wrong

Trimming is almost always safe. Casting to integers is safe when the ids are numbers and unsafe when leading zeros distinguish them: in a product catalog, 0012 and 12 can be different items, and a zip code 02134 is not 2134. Lowercasing is safe for emails and unsafe for case-sensitive codes. Removing punctuation from phone numbers or SKUs is safe if the format is consistent. Before normalizing, check that it creates no collisions: count the distinct raw keys and the distinct normalized keys in each table; if the counts differ, two different things have become one.

When no deterministic rule gets the keys to agree, you are in fuzzy matching territory: names spelled differently, addresses, company suffixes. That is a different problem, and a risky one. Match on a rule you can explain, keep the matches separate from the fuzzy candidates, and have a person look at the candidates.

In pandas

o["k"] = pd.to_numeric(o["customer_id"].str.strip(), errors="coerce")
c["k"] = pd.to_numeric(c["customer_id"].str.strip(), errors="coerce")
m = o.merge(c[["k", "name"]], on="k", how="left", indicator=True)
m["_merge"].value_counts()

indicator=True adds a column saying whether each row matched, which is the anti-join in one line. Read the keys with dtype=str, or pandas will turn 001 into 1 before you see it, and use validate="many_to_one" in merge to make pandas raise on a duplicate in the lookup.

How to tell it worked

Compare the result count with the input count. Count the unmatched and read them. Pick three matched rows and verify them against the source by eye. Check that no key matched more than one partner. For the join in joining CSV files in SQL, the same four checks apply.

When the key is not a key

Sometimes there is no clean key: the same customer under three spellings, a code reused for different things across years, an id that restarts each month. No amount of trimming fixes that. Say so, and ask the owner of the data for a real identifier, or accept a lower match rate and report it. A join that matches 94% of rows is a result with a stated limit; one that appears to match 100% because of a fan-out is a mistake. The analysis guide puts joins in the order of work.

Further reading: DuckDB joins; pandas merge.