Compare two CSV files for differences: in Excel, Python and PowerShell

#First, make them comparable

Four things make two files with the same data look different. Deal with them before comparing anything.

1. Leading zeros. Open a CSV straight in Excel and store 014 becomes 14, SKU 00123 becomes 123. Import it instead (Data › From Text/CSV) and set those columns to Text, or compare outside Excel.
2. Delimiters and decimal commas. Many European exports use ; between fields and 1.234,56 for numbers. Tell your tool which, or every row fails to parse.
3. Encoding and line endings. UTF-8 with and without a byte-order mark, Windows and Unix line endings: a text diff flags every line, though nothing changed.
4. Order. Two systems rarely write rows in the same order. Sort both by the key, or better, match on the key and ignore order altogether.

A word on online comparison sites: many of them work by uploading both files to their server. Check before you drop in a day of sales. The three methods below never send your files anywhere.

#1. In Excel, with Power Query

1. Data › Get Data › From File › From Text/CSV for the first file, then Load To… › Only Create Connection. Do the same for the second.
2. Data › Get Data › Combine Queries › Merge. Choose both queries, and click the key column, or columns, in each.
3. Set Join Kind to Full Outer (all rows from both) and press OK.
4. Expand the second table's columns, then Add Column › Custom Column to compare the values, for example [net] <> [erp.net].

A row where the second file's key is empty is missing from the second file, and the other way round. For a quick check without Power Query, a lookup on the key does the same job: see how to compare two Excel files.

#2. In Python, with pandas

import pandas as pd

key = ["store", "business_date"]
tills = pd.read_csv("tills.csv", sep=None, engine="python", dtype={"store": str})
erp = pd.read_csv("erp.csv", sep=None, engine="python", dtype={"store": str})

merged = tills.merge(erp, on=key, how="outer", suffixes=("_tills", "_erp"), indicator=True)

missing = merged[merged["_merge"] != "both"]
both = merged[merged["_merge"] == "both"]
differs = both[(both["net_tills"] - both["net_erp"]).abs() > 0.005]

print(missing[key + ["_merge"]])
print(differs[key + ["net_tills", "net_erp"]])

sep=None works out the delimiter, and reading store as text keeps its leading zeros. For decimal commas, add decimal=",". The _merge column says left_only or right_only for rows found in one file only.

pandas also has DataFrame.compare(), but it needs both tables to have the same rows in the same order, which is exactly what two exports from two systems do not have. Merge on the key first.

#3. In PowerShell

On a Windows machine with nothing else installed:

$tills = Import-Csv .\tills.csv
$erp = Import-Csv .\erp.csv
Compare-Object $tills $erp -Property store, business_date, net |
  Sort-Object store, business_date

<= marks a row found only in the first file and => a row only in the second. A row whose net differs appears twice, once with each sign. Add -Delimiter ';' to Import-Csv for semicolon files. Everything is compared as text, so 12.40 and 12.4 count as different. Fine for exports from one system, worth knowing for two.

#What a comparison should show

Whichever way you do it, the result should look like this:

Invented figures. Four rows out of 120 stores, and four different things to look at, which is the point of matching on a key instead of reading a diff of every line.

The techniques behind all three methods, and when each one is enough, are in data reconciliation: techniques, and a worked retail example.

#Questions people ask

#How do I compare two CSV files for differences?

Match the rows on a key that names each row in both files, then compare the values. A line-by-line diff only works when both files have the same order and format.

#Can I compare two CSV files in Excel?

Yes. Import both with Data › From Text/CSV so leading zeros survive, then merge them in Power Query with a full outer join, or look each key up in the other file.

#How do I compare two large CSV files?

Use a tool that matches on keys rather than loading both files into a spreadsheet: pandas or PowerShell on your machine, or load both into a database and join them there.

#Why does my CSV comparison show every row as different?

Usually the line endings, a byte-order mark, the delimiter or the row order differ between the two files, not the data. Fix those first, or compare on a key so they stop mattering.