Match supplier names to invoices without duplicating rows.

This is one kind of thing you could share. It’s a write-up of a problem solved with two free tools: the files, the settings, and the results. Yours might be a visualization or an app instead.

Open Join CSV files · Open CSV inspector

We made this example using invented data. The links open the tools; paste in the sample files below to follow along.

The problem: a match can still be wrong

An operations analyst needs to add supplier names to six invoices before an import. The supplier number is unique within a region, not across the whole business. The export also contains padded numbers, surrounding spaces, one unknown supplier, and one missing supplier number. Joining on supplier_id alone can duplicate invoices or assign the wrong regional supplier. These files are synthetic and contain no customer data. Amounts are illustrative currency units.

The two input files

Keep the header row in each file. The quoted spaces around 0024 in the invoice input are intentional.

invoices.csv

invoice_id,supplier_id,region,amount
I101,0017,US,120
I102,0017,CA,80
I103," 0024 ",US,50
I104,9999,US,30
I105,,US,20
I106,24,US,60

suppliers.csv

supplier_id,region,supplier_name
17,US,North Supply US
17,CA,North Supply Canada
24,US,Cedar Works
31,US,Atlas Parts
,US,Unassigned

1. Reproduce the failure

Open Join CSV files and paste invoices.csv into the main input and suppliers.csv into the lookup input. Use Comma for both separators. Choose supplier_id for Main key 1 and Lookup key 1. Keep all main-file rows. Start with the matching checkboxes off: only one of the six invoice rows matches.

Now turn on Ignore surrounding spaces and Ignore leading zeros in digit-only keys. For this example, the business rule says 0017 and 17 identify the same supplier within a region. Set Duplicate lookup keys to One output row per match. The result becomes eight rows, although only six invoices exist. Supplier 17 appears twice in the lookup, once for each region. Using the first match would hide that ambiguity, not resolve it.

Failure reproduced: six invoices become eight output rows. The next step fixes the matching rule and checks that no original invoice is lost.

“Matched invoices” counts original invoices with a match, not expanded output rows. In the second attempt, each invoice for supplier 17 finds both regional lookup records. That is why four matched invoices can produce six matched output rows plus the two unmatched rows.

2. Match both the supplier and the region

Choose Add another key column. Select region for Main key 2 and Lookup key 2; keep supplier_id as the first key in both files. Under the lookup columns to add, select supplier_name only. Keep the two normalization options on and leave Ignore case and Ignore hidden characters off. Keep all main-file rows and One output row per match.

Now the lookup has no duplicate composite keys. Four invoices match and two remain unresolved, with six output rows. I102 correctly receives North Supply Canada rather than the US supplier. Several invoices for Cedar Works are expected; repeated supplier keys in the main file don’t mean duplicate invoice identifiers.

Final join settings

The six-row joined output

Copy this output before switching to exception mode. The spaces around 0024 remain in the output field; matching normalization doesn’t rewrite the source value.

invoice_id,supplier_id,region,amount,supplier_name
I101,0017,US,120,North Supply US
I102,0017,CA,80,North Supply Canada
I103, 0024 ,US,50,Cedar Works
I104,9999,US,30,
I105,,US,20,
I106,24,US,60,Cedar Works

The result explained

The review notes in this table are annotations. The actual CSV has empty supplier_name fields for I104 and I105; the tool doesn’t insert those labels.

3. Set aside the invoices that didn’t match

With the six-row result saved, choose Only main-file rows with no match. The exception output contains I104, whose supplier 9999 is absent, and I105, whose supplier number is blank. A blank key doesn’t match the lookup’s blank Unassigned row. Keep these invoices for investigation; don’t invent a supplier or silently discard them. Two lookup rows also have no match: Atlas Parts, which has no invoice in this sample, and the blank-key placeholder.

Exception output

invoice_id,supplier_id,region,amount,supplier_name
I104,9999,US,30,
I105,,US,20,

4. Check the result before importing

Open CSV inspector and paste the saved six-row joined result, with its header row. It reports six data rows and five columns. Confirm that invoice_id has six distinct values and is unique, and that supplier_name has two blanks. The original supplier numbers are unchanged: 0017 keeps its zeros, and the spaces around 0024 remain. The inspector flags those zeros and spaces and reads supplier_id as text.

Separately, total the amount column: the full file is still 360, with 310 attached to matched suppliers and 50 held for review. Those totals are a check you perform; the join tool doesn’t calculate them.

Verification checklist

What this demonstrates, and what it doesn’t

The result is a reproducible reconciliation process, not merely a larger CSV: diagnose an ambiguous identity, choose an appropriate composite key, preserve all invoices, and create a review queue. The data doesn’t prove that the lookup is current or that an invoice is legitimate.

Normalization is safe here only because its meaning is defined. If 0017 and 17 are different real identifiers, leave Ignore leading zeros off. If the lookup later has two rows for the same supplier and region, investigate instead of accepting the first result.

Why this would qualify

It was made with MySafeInfo and says which tools. It’s public. It shows how, with the files, the settings and the results, so a reader can repeat it. And it’s original work. A real submission would also carry the one-line note about the discount.

It’s one example of one kind of project. A visualization, an app or a lesson looks nothing like this, and that’s fine. Please don’t copy it or swap the names and numbers.