I want to compare two CSV files with the following format. They not have headers. I want to compare them by a specific column (in this case, the 2nd one).

The source CSV files around 4-5GB, so loading them into memory is won't work.

If there's no matching column in old.csv, than every new line written into out.csv.

This 2nd column will be a html link, for the shake of simplicity, here one word only.

My question is it possible to achieve the same result with sed, awk, join, or grep?






#!/usr/bin/env python3

Source: https://gist.github.com/davidrleonard/4dbeebf749248a956e44
Usage: $ ./csv-difference.py -d new.csv -s old.csv -o out.csv -c 1

import sys
import argparse
import csv

def main():
    parser = argparse.ArgumentParser(description='Output difference in CSVs.')
    parser.add_argument('-d', '--dataset', help='A CSV file of the full dataset', required=True)
    parser.add_argument('-s', '--subset', help='A CSV file that is a subset of the full dataset', required=True)
    parser.add_argument('-o', '--output', help='The CSV file we should write to (will be overwritten if it exists', required=True)
    parser.add_argument('-c', '--column', help='A number of the column to be compared (0 is column 1, 1 is column 2, etc.)', required=True, type=int)

    args = parser.parse_args()
    dataset_file = args.dataset
    subset_file = args.subset
    output_file = args.output
    column_num = args.column

    with open(dataset_file, 'r') as datafile, open(subset_file, 'r') as subsetfile, open(output_file, 'w') as outputfile:
        data = {row[column_num]: row for row in csv.reader(datafile, delimiter='|', quotechar='"')}
        subset = {row[column_num]: row for row in csv.reader(subsetfile, delimiter='|', quotechar='"')}

        data_keys = set(data.keys())
        subset_keys = set(subset.keys())
        output_keys = data_keys - subset_keys

        output = [data[key] for key in output_keys]
        output_csv = csv.writer(outputfile, delimiter='|', quotechar='"', quoting=csv.QUOTE_ALL)
        for row in output:

if __name__ == '__main__':


Which is generating out.csv

  • One a separate note, your code is somewhat overwrought output_keys should simply be data.keys() - subset.keys(). Or, even better, output = {v for k, v in data.items() if k not in subset}
    – iruvar
    Commented Nov 4, 2016 at 22:20

1 Answer 1


Super simple with awk:

$ awk -F'|' 'NR == FNR {old[$2]; next} !($2 in old)' old.csv new.csv

That stores the 2nd field of the old.csv file in the array named "old", and then for the new.csv file, it will print records where the 2nd field is not in the "old" array.

It is true that this will not respect any pipe character within quotes. For that, I like ruby's csv module:

ruby -rcsv  -e '
  old_col2 = []
  old_data = CSV.foreach("./old.csv", :col_sep => "|") do |row|
    old_col2 << row[1]

  CSV.foreach("./new.csv", :col_sep => "|") do |row|
    if not old_col2.include?(row[1])
      puts CSV.generate_line(row, :col_sep => "|", :force_quotes => true)
  • You say that awk thinks the pipes are within quotes when they actually doesn't? What problems it can cause?
    – Lanti
    Commented Nov 4, 2016 at 19:36
  • Imagine you have a record in your file like "hello"|"foo|bar|baz"|"world" -- awk will think that line has 5 fields; a proper CSV parser will see 3 fields. Commented Nov 4, 2016 at 19:37
  • Luckily the CSV sources formatted in a fashion that this will not happen (although bad formatting errors always can happen). So every column is inside double quotes. What I'm courious about is that the columns inside "" have html embed codes with lots of "". After the first run I will know it will work or not.
    – Lanti
    Commented Nov 4, 2016 at 19:41
  • 1
    The nice thing about awk is that your data is just data: only the field separator is special. awk won't care about quotes. Commented Nov 4, 2016 at 19:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .