CODEWITHSUNDEEP
csvcommand-linecomparisoncommand-line-interface
Argyrios Tzakas
Argyrios Tzakas

Reputation: 325

Compare CSV files

I am currently using a windows utility called TableTexCompare

This tool can take 2 CSV files and compare them. The nice thing about it is that it can make the comparison even if the records of the 2 files are not sorted in the same order or the fields are not positioned in the same order.

As such, the following 2 files would result in a successful comparison

(File1.csv)

FirstName,LastName,Age
Mona,Sax,30
Max,Payne,43
Jack,Lupino,50

(File2.csv)

FirstName,Age,LastName
Max,43,Payne
Jack,50,Lupino
Mona,30,Sax

What I am looking for is to do the same thing from the command-line with just 1 difference: I would like the comparison to be performed in one direction only, i.e. if File2.csv is as follows (a subset of File1.csv), the comparison should pass

(File2.csv)

FirstName,Age,LastName
Jack,50,Lupino

I do not particularly care if it's going to be in some programming language, a dedicated cli tool or a shell script (e.g. using awk). I have some experience with Java and Groovy but would like to be pointed to some initial direction.

Upvotes: 4

Views: 7755

Answers (3)

Harel Ben Attia
Harel Ben Attia

Reputation: 494

You can take a look at q - Text as a Database , which allows executing SQL directly on csv files, including joins. This will allow doing a compare easily, and much more, such as matching specific columns for equality, and getting specific columns from rows that don't match etc.

Full disclosure - It's my own open source tool.

Harel Ben-Attia

Upvotes: 1

dbenham
dbenham

Reputation: 130819

If you can afford to do a case insensitive comparison, and if there are no duplicates within File2.csv that must be matched within File1.csv, and if File1.csv does not contain \\ or \", then all you need is a simple FINDSTR command.

The following will list lines in File2.csv that do not appear in File1.csv:

findstr /vxig:"File1.csv" "File2.csv"

If all you want is an indication whether File1.csv is a superset of File2.csv, then

findstr /vxig:"File1.csv" "File2.csv" >nul && (echo File1 is NOT a superset of File2) || (echo File1 IS a superset of File2)

The search should not have to be case insensitive, except there is a nasty FINDSTR bug: it may fail to find matches when there are multiple case sensitive literal search strings of varying size. The case insensitive option avoids the bug. See Why doesn't this FINDSTR example with multiple literal search strings find a match? for more info.

The search will not work properly if File2.csv contains \\ or \" because FINDSTR will treat them as \ and " respectively. See What are the undocumented features and limitations of the Windows FINDSTR command? for more info. The accepted answer has sections describing FINDSTR escape sequences about half way down.

Upvotes: 1

Tim Pietzcker
Tim Pietzcker

Reputation: 336108

I can offer a Python solution:

import csv
with open("file1.csv") as f1, open("file2.csv") as f2:
    r1 = list(csv.DictReader(f1))
    r2 = csv.DictReader(f2)
    for item in r2:
        if not item in r1: 
            print("r2 is not a subset of r1!")
            break

This is actually a bit more verbose than necessary in Python (but easier to understand); I personally would have used a generator expression:

import csv
with open("file1.csv") as f1, open("file2.csv") as f2:
    r1 = list(csv.DictReader(f1))
    r2 = csv.DictReader(f2)
    if all(item in r1 for item in r2):
        print("r2 is a subset of r1")

Upvotes: 5

Related Questions