Doing a Three Way Merge of CSV Edits by Key and by Cell

Two people edit a copy of the same CSV. One corrects some statuses, the other updates some amounts. When they bring their copies back together, a two-way comparison cannot tell who changed what: a differing cell could be either person's edit. What you need is the version both started from, the base, and a three-way merge: take each side's changes relative to the base and combine them, flagging only the cells where both sides disagree. Git does this for lines. For CSV it is better done for cells. This article shows both, with the same edits, and the difference in the outcome.

Base and two edits

base.csv:

id,name,status,amount
1,Ana,active,10.00
2,Bo,active,20.00
3,Cy,pending,30.00
4,Di,active,40.00

ours.csv (one person) changes Bo's status and Cy's amount:

id,name,status,amount
1,Ana,active,10.00
2,Bo,inactive,20.00
3,Cy,pending,35.00
4,Di,active,40.00

theirs.csv (the other) changes Bo's amount, Cy's amount differently, and adds a row:

id,name,status,amount
1,Ana,active,10.00
2,Bo,active,25.00
3,Cy,pending,33.00
4,Di,active,40.00
5,Ed,active,50.00

Per cell, relative to the base: Bo's status (ours only), Bo's amount (theirs only), Cy's amount (both, to different values: a real conflict) and Ed (theirs added).

The line-based merge

git merge-file -p ours.csv base.csv theirs.csv

The three arguments are ours, base and theirs, and -p prints the result. Git compares by line. Bo's line was changed by both sides (status by ours, amount by theirs), so it counts as changed on both sides, and Cy's line was changed by both. Two conflicts:

id,name,status,amount
1,Ana,active,10.00
<<<<<<< ours.csv
2,Bo,inactive,20.00
=======
2,Bo,active,25.00
>>>>>>> theirs.csv
<<<<<<< ours.csv
3,Cy,pending,35.00
=======
3,Cy,pending,33.00
>>>>>>> theirs.csv
4,Di,active,40.00
5,Ed,active,50.00

The added row merges cleanly; Ed is appended at the end. The conflict on Bo is false: the two edits are to different cells and do not clash, but Git sees one line. The conflict on Cy is real. You can resolve Bo by hand (2,Bo,inactive,25.00) and must decide Cy. For a small file that is fine; for a file where many rows have independent edits, the false conflicts bury the true ones, and the markers inside a CSV must be removed before the file can be read again. The exit status of git merge-file is the number of conflicts (2 here), and a file with markers left in it is not valid CSV.

The cell-based merge

To merge by cell, compare each side with the base by key and column. In SQL, with all three read as text:

CREATE VIEW base AS SELECT * FROM read_csv('base.csv', all_varchar=true);
CREATE VIEW ours AS SELECT * FROM read_csv('ours.csv', all_varchar=true);
CREATE VIEW theirs AS SELECT * FROM read_csv('theirs.csv', all_varchar=true);

SELECT id,
  CASE WHEN o.status IS NOT DISTINCT FROM t.status THEN o.status
       WHEN o.status IS NOT DISTINCT FROM b.status THEN t.status
       WHEN t.status IS NOT DISTINCT FROM b.status THEN o.status
       ELSE '<<CONFLICT>>' END AS status,
  CASE WHEN o.amount IS NOT DISTINCT FROM t.amount THEN o.amount
       WHEN o.amount IS NOT DISTINCT FROM b.amount THEN t.amount
       WHEN t.amount IS NOT DISTINCT FROM b.amount THEN o.amount
       ELSE '<<CONFLICT>>' END AS amount
FROM base b JOIN ours o USING (id) JOIN theirs t USING (id)
ORDER BY id;

The rule for each cell: if both sides have the same value, take it; if ours equals the base, theirs changed it, so take theirs; if theirs equals the base, take ours; otherwise both changed it differently, which is a conflict. The result for the shared rows:

Bo's two independent edits merged: inactive and 25.00. Only Cy's amount is a conflict (35.00 against 33.00 against a base of 30.00). The name column follows the same pattern, omitted here for brevity; generate the repeated CASE for every non-key column with a small script rather than by hand. Rows added on one side only appear through an outer join on the key, and rows deleted on one side are deleted unless the other side changed them, in which case it is a conflict between a deletion and an edit. With a full outer join of the three relations on id, the same rules apply with NULL meaning "absent".

Added rows, deleted rows

The cell rule extends to rows by treating absence as a value. For each key, look at the row in base, ours and theirs:

• absent in base, present in one side: added, keep it;

• absent in base, present in both: added by both, keep it if the cells agree, otherwise conflict;

• present in base, absent in one side and unchanged in the other: deleted, remove it;

• present in base, absent in one side and edited in the other: conflict (an edit to a row that the other side deleted).

Ed (id 5) exists only in theirs, so he is added. If ours had also added id 5 with different content, the cells would be compared as above. Keys must be unique in each file, or the join multiplies rows; check first (duplicate keys).

Resolve conflicts, then verify

A merge is not finished at the conflicts. Decide each one, by asking the people involved or by a rule written beforehand (latest edit wins, a named owner of each column wins). Record the decision. Then write the merged file in the sorted, stable form (keeping a CSV sorted), and check it: the header is intact, every row has the same number of fields, no <<CONFLICT>> or <<<<<<< remains, the keys are unique and the row count is the base's count plus the added rows minus the deleted ones (here 4 + 1 = 5). Compare the merged file with each side to confirm that every edit made by either side that did not conflict is present.

In Git

Git can do the line merge on a branch merge, and you can give it a better driver for CSV. A merge driver set in .gitattributes (*.csv merge=csvmerge) with a command that runs your cell-based script receives the three versions and writes the result, returning non-zero for a conflict. That makes git merge use the cell logic for those files. The mechanics are in the Git documentation on defining a custom merge driver. The simpler habit is to keep CSVs sorted and let the textual merge do most of the work, accepting conflicts on rows both sides touched (Git diff for CSV).

How to tell it worked

Merge with the roles swapped (ours and theirs exchanged): the non-conflicting result must be identical, and the conflicts the same. Merge a file with itself and the base: no changes. Check that each side's isolated edits are present: Bo's status inactive (ours), Bo's amount 25.00 and Ed (theirs). Count conflicts against the cells you know both sides changed. Open the file in a strict parser.

When both sides changed the same cell

No algorithm decides that for you. The data may be right on either side, or neither: 35.00 and 33.00 might both be wrong, and the source system may say 34.00. Keep the base and both candidates side by side, ask, and record who decided. If the same cells keep conflicting, the process is the problem: two people should not edit the same column of the same file in parallel, and a shared system with locking or a database with row versions is the better tool. For comparing exports rather than merging edits, see reconciling two exports, and for the overview, the diff and merge guide.

Further reading: git merge-file; DuckDB full outer join.