Compare Two CSV Files (Find Differences)

You have data_v1.csv and data_v2.csv. You need to know:

• What rows were added?
• What rows were deleted?
• Which specific cells changed?

Staring at them side-by-side is impossible. Here is how to automate the comparison.

Method 1: Online Diff Tool (Easiest)

For quick visual comparison.

1. Upload File A (Old).
2. Upload File B (New).
3. The tool highlights changes: Green (Added), Red (Deleted), Yellow (Changed).

-> Compare CSV Files

Method 2: Excel "Inquire" or Conditional Formatting

Quick Visual Check:

1. Open both files.
2. Copy data from File B into a new sheet in File A.
3. Select all data in both sheets.
4. Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
5. Switch to "Unique" values.
6. Unique values (changes) will be highlighted.

Formula Method (True/False): If Sheet1 and Sheet2 are sorted identically: In Sheet3 cell A1: =IF(Sheet1!A1=Sheet2!A1, "Match", "CHANGE") Drag this formula across all rows/cols.

Pros: Built-in. Cons: Fails if rows are inserted/deleted (misaligns everything).

Method 3: Command Line (diff)

If you are on Mac or Linux, the diff command is built-in.

diff file1.csv file2.csv

Output:

< 1,John,Manager
---
> 1,John,Senior Manager

< means line from file 1. > means line from file 2.

Better Command: vimdiff

vimdiff file1.csv file2.csv

Opens a side-by-side view in the terminal.

Pros: Fast, free. Cons: Hard to read for non-developers; sensitive to row sorting.

Method 4: Python (Pandas)

For detailed analysis (e.g., "Give me a list of all new IDs").

import pandas as pd

df1 = pd.read_csv('old.csv')
df2 = pd.read_csv('new.csv')

# Find rows in df2 that are not in df1 (Added)
new_rows = df2[~df2['id'].isin(df1['id'])]

# Find rows in df1 that are not in df2 (Deleted)
deleted_rows = df1[~df1['id'].isin(df2['id'])]

print("New Rows:", len(new_rows))
print("Deleted Rows:", len(deleted_rows))

Tips for Accurate Comparison

1. Choose a stable key

Sorting entire rows is not enough when records have been reordered or updated. Use an order ID, customer ID, SKU or another unique key to align the two versions. First confirm that the key is present, non-empty and unique in each file. Duplicate keys turn a one-to-one comparison into an ambiguous many-to-many problem.

1. Sort Your Data First

If File A is sorted by ID and File B is sorted by Name, every line will look different to a simple diff tool. Always sort both files by a unique key (ID, Email) before comparing.

2. Keep headers out of the data

Ensure you aren't comparing the header row as data.

3. Watch for Formatting

$100 vs 100. 2024-01-01 vs 1/1/24. These are "changes" to a computer, even if the value is the same. Clean/standardize data before comparing.

4. Floating Point Numbers

10.000001 vs 10.0. Computers might see these as different. Use a tool that allows "fuzzy" numeric comparison (tolerance).

Summary

• Visual/Small files: Use Excel Conditional Formatting or an Online Tool.
• Quick Check: Use diff command.
• Data Analysis: Use Python/Pandas.
• Crucial Step: Sort both files before starting!

Classify differences, do not just count them

A useful comparison separates:

• keys present only in the old file;
• keys present only in the new file;
• matched keys with changed values;
• unchanged records;
• duplicate or missing keys that prevent a reliable comparison.

For changed records, output the key, column name, old value and new value. This makes the result reviewable and allows someone to filter by sensitive columns such as status or price. Decide whether blank and missing mean the same thing, whether case matters, and what numeric tolerance is acceptable before running the comparison.

Finally, reconcile the counts. Old-only, new-only, changed and unchanged groups should account for the source keys after duplicates have been handled. Save the comparison rules beside the result so the same process can be repeated later.

Preserve evidence for important comparisons

When the comparison supports an audit, migration or financial review, keep the two untouched source files with the result. Record the key column, normalization rules, ignored columns, numeric tolerance and the time the comparison was run. A result without its rules is difficult to reproduce.

Be careful with personal data in difference reports. A compact report containing the record key and changed fields is easier to review and safer to share than two complete customer exports. Restrict access to the same level as the source data, and delete temporary copies when the review is finished.

---

Need to see the difference? HappyCSV's Compare Tool visualizes changes locally in your browser.