Using git diff Sensibly on a CSV File With Word Diff

CSV files live in version control more often than they should, because the data is small enough and the history is useful: reference tables, seed data, test fixtures, the output of a script you want to review. Git is a line-oriented tool, and a CSV record is a line, so the fit is better than it seems. The default diff is still unhelpful for the common change, one cell in one row, and it is useless when the file is reordered between commits. This article makes git diff readable for CSV with a few options, a file convention and a diff driver.

The default diff

Commit a.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

Change Cy's amount to 35.00 and run git diff:

-3,Cy,pending,30.00
+3,Cy,pending,35.00

Git marks the entire line as removed and the entire line as added, and you have to read both to find the cell. With four columns it is easy; with forty columns it is hard. The fix is to tell Git what a "word" is.

Word diff on commas

git diff --word-diff=color --word-diff-regex='[^,]+'

--word-diff shows changes inside a line; --word-diff-regex='[^,]+' defines a word as a run of characters that are not a comma, so each cell is a word. The plain-text form, --word-diff=plain, marks the changes with brackets:

3,Cy,pending,[-30.00-]{+35.00+}

Now the change is one cell. For several changes in one row, only the changed cells are marked. Use --word-diff=porcelain if a script has to read the changes. The regex is documented under git diff's word diff options. To make this the default for CSV files, set it in .gitattributes:

*.csv diff=csv

git config diff.csv.wordRegex '[^,]+'

With the attribute and the setting, git diff --word-diff on any .csv uses cell-sized words without the flag having to be repeated. (You still need to ask for word diff; the regex only defines what a word is.)

Aligned columns with textconv

A different approach is to change how the file is presented to the diff engine, not how the diff is computed. A textconv filter converts the file to text before diffing, without altering the stored data:

git config diff.csv.textconv 'column -s, -t'

With *.csv diff=csv in .gitattributes, git diff now runs both versions through column -s, -t and compares the aligned output:

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

The alignment makes the columns readable, and the diff is still line based. The cost: column is a text formatter, not a CSV parser, so a field with a quoted comma is split wrongly, and re-aligning can make a long cell change the width of the whole column and therefore every line in the output. It is a viewing aid for git diff and git log -p; it does not affect commits or merges.

Make the file diff-friendly

The settings above help only if the file itself behaves. Four conventions matter more than any option.

One record per line. If a field contains a line break, the record spans lines and Git's line model breaks it. Remove embedded newlines (replace with a space or \n text) in files you track, or keep that data in a different format.

A stable sort. Git compares lines in order. If a script writes the rows in a different order each time (database order, hash order), every commit shows a shuffle. Sort by a unique key before writing, so that a changed row is a changed line and an inserted row is an inserted line (keeping a CSV sorted).

Stable formatting. Always write numbers the same way (10.00, not 10.0 sometimes), the same quoting (quote when necessary and not otherwise), the same line endings (LF), the same encoding. A tool that re-saves the file with \r\n or with every field quoted creates a diff of the whole file. Settle these in the script that writes the file, and in .gitattributes with *.csv text eol=lf for line endings.

A final newline. If the last line lacks one, Git prints \ No newline at end of file. When you later append a row, the previous last line changes too (it gains a newline), so a one-row addition shows as a two-line change. Always end the file with a newline.

What the history is good for

With those conventions, Git gives a useful history of a small CSV: git log -p -- ref/countries.csv shows each change to the reference table as a few changed cells; git blame tells who last changed a row and when; git diff v1..v2 --stat shows how many rows changed between releases. For review, a pull request with a three-line CSV diff is easy to approve; with a sorted file and a cell-level diff, a reviewer can see that a price changed and nothing else changed.

To compare two commits by record rather than by line, export both versions and use the key-based comparison from the comparison article: git show HEAD~1:data.csv > old.csv. That also works when the file is not sorted, and gives you the counts of added, removed and changed rows.

What goes wrong

Quoted commas. "Smith, Jo" has a comma inside quotes, and the word regex splits it into "Smith and Jo". The diff is still correct in the sense that changed text is marked, but the marks land in odd places. If your data has many such fields, use the plain line diff, or a CSV-aware tool (csvdiff, daff, qsv) as an external diff driver.

Large files. A diff of a 200 MB file is slow, and its output is unreadable. Git is a poor home for data of that size: store the file elsewhere, keep a checksum or a manifest in the repository, and compare with SQL (diffing guide).

Binary-looking data. Files with NUL bytes or very long lines are treated as binary, and diffs are suppressed. Check with git diff --stat.

Conflicts. A merge of two branches that both edited the file can conflict on a line. For CSV, a conflict can be resolved by hand when cells differ, but a conflict in a sorted file often means two people added rows near each other and both belong. Three-way merging by key shows a more reliable method.

How to tell it worked

Change one cell, commit and run the diff: it should show that cell and nothing else. Add a row: one inserted line. Remove a row: one deleted line. Reorder the source and regenerate the file: no diff at all. If any of these fails, look at the writer, not at Git. Try the aligned diff with a wide cell to see how the columns behave.

When the CSV is too large to diff

The line diff scales with the size of the file, and a reviewer's attention does not. Beyond a few thousand rows, a diff stops being something a person reads, and it should become a summary: counts of added, removed and changed rows, and the changed rows themselves if they are few. Generate that summary with a script or a query and attach it to the change, while Git keeps the file's history. For the matching problem of two exports of the same report, see reconciling exports.

Further reading: Git attributes for diff drivers; git diff documentation.