Comparing two supplier feed versions: match products by key, not by line, and make the counts add up

Why a line comparison does not help

A text comparison tool treats a file as a list of lines and looks for matching blocks. Python's difflib documentation says its functions compare sequences, most often lists of text lines, notes that its default junk heuristic is asymmetric, so comparing A with B can differ from comparing B with A, and says that deltas made by its Differ class make no claim to be minimal diffs. For prose that is fine. For a product table it is the wrong unit. If the supplier re-sorts the file, every line looks moved. If a column's number format changes from 4.2 to 4.20, every row differs. Real changes are lost among thousands of false ones.

Compare by key, after normalising

A useful comparison reads each file into rows keyed by the product's identifier, as csv.DictReader does with header names, and compares the two sets of keys. Products in the new file but not the old are added. Products in the old but not the new are absent. Products in both are compared column by column, after the formatting rules you have agreed: trimmed spaces, a single number format, empty treated one way. Only then are differences real changes, and each is reported with the old and new value and the column.

The key must be agreed first. If two files write the same code as 00123 and 123, or with different case, they will not match; the existing guide on text SKUs explains why. If a key appears twice in one file, the report should list it as a duplicate with all its rows and neither pick one nor compare it, because choosing silently is how wrong prices are stored.

• Write the key columns, compared columns and normalisation rules in a short document.
• Treat blank, missing and zero as three different states and say how each is reported.

Absent does not mean discontinued

A product missing from the new file may be discontinued, out of stock or simply not in a partial file. Only the supplier knows. A report should say absent from this file and nothing more. The existing guide on full snapshots versus deltas covers how to decide what an absent product means for your store; the comparison only supplies the facts.

The arithmetic that proves the report is complete

A comparison is checkable, if you count distinct keys. The number of distinct keys in the old file, minus the keys absent from the new file, plus the keys added, must equal the number of distinct keys in the new file. That holds whatever the files contain, because a key is either in both files, only in the old one or only in the new one. Counting plain rows does not hold when a key is repeated a different number of times in the two files: one key listed twice in one file and once in the other breaks the row arithmetic although nothing was lost. So a good report prints the distinct-key equation and, on separate lines, the extra rows that repeat a key in each file. If the distinct-key equation does not balance, a key was lost or double counted. Add thresholds you choose, for example a price move over a set percentage or a stock figure that falls to zero, so the few changes you care about are at the top of the report. A file compared with itself must report no changes, and the same two files must always give the same report.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

                                  old file   new file
rows                                 1,003      1,019
extra rows repeating a key               3          1
distinct keys                        1,000      1,018

old distinct keys                    1,000
absent from new file                   -12
added in new file                      +30
expected new distinct keys           1,018
actual new distinct keys             1,018   -> reconciles

(plain rows: 1,003 - 12 + 30 = 1,021, but the new file has 1,019 rows;
 the repeated-key rows differ between the files, so the row arithmetic
 fails although no key was lost)
(synthetic figures to show the equation)

A safe first investigation

Sort both versions by the product code and pick two products. Compare every column by eye. Ask whether the differences are in values or only in spacing, formatting and order. Count the rows and the distinct product codes in each file, and check whether you can account for the difference. Keep real price lists out of anything you send us; column names, row counts and the thresholds you care about are enough to scope the job.

How the paid job is accepted

The fixed job feed-version-diff-report-before-import is £225 for one supplier layout and two file versions of up to 100,000 rows each. Acceptance uses a synthetic pair with ten planted differences, including a key that repeats in only one file, plus reordered rows and whitespace-only differences, which the report must list exactly and no others; a file compared with itself must give zero changes; the real pair's distinct keys must reconcile, with the extra rows that repeat a key counted for each file and every repeated key listed; and two runs must give byte-identical reports. The report changes nothing in your systems and does not decide whether you import. Prices are untested proposals, and payment follows the agreed checks and your sign-off. Nothing is booked or charged by an enquiry.