comparing CSV files in ubuntu

Question

I have two CSV files and I need to check for creations, updates and deletions. Take the following example files:

ORIGINAL FILE
sku1,A
sku2,B
sku3,C
sku4,D
sku5,E
sku6,F
sku7,G
sku8,H
sku9,I
sku10,J

UPDATED FILE
sku1,A
sku2,B-UPDATED
sku3,C
sku5,E
sku6,F
sku7,G-UPDATED
sku11, CREATED
sku8,H
sku9,I
sku4,D-UPDATED

I am using the linux comm command as follows:

comm -23 --nocheck-order updated_file.csv original_file > diff_file.csv

Which gives me all newly created and updated rows as follows

sku2,B-UPDATED
sku7,G-UPDATED
sku11, CREATED
sku4,D-UPDATED

Which is great but if you look closely "sku10,J" has been deleted and I'm not sure the best command/way to check for it. The data I have provided is merely demo, the text "sku" does not exist in the real data however column one of the CSV files are a unique 5 character indentifier. Any advice is appreciated.

Hi @prime, what you are looking for should be here -> google.fr/… — Andy K
– Andy K, Commented Jan 7, 2016 at 13:56

glenn jackman · Accepted Answer · 2016-01-07 14:11:58Z

1

I'd use join instead:

join -t, -a1 -a2 -eMISSING -o 0,1.2,2.2 <(sort file.orig) <(sort file.update)

sku1,A,A
sku10,J,MISSING
sku11,MISSING, CREATED
sku2,B,B-UPDATED
sku3,C,C
sku4,D,D-UPDATED
sku5,E,E
sku6,F,F
sku7,G,G-UPDATED
sku8,H,H
sku9,I,I

Then I'd pipe that into awk

join ... | awk -F, -v OFS=, '
    $3 == "MISSING" {print "deleted: " $1,$2; next}
    $2 == "MISSING" {print "added:   " $1,$3; next}
    $2 != $3        {print "updated: " $0}
'

deleted: sku10,J
added:   sku11, CREATED
updated: sku2,B,B-UPDATED
updated: sku4,D,D-UPDATED
updated: sku7,G,G-UPDATED

answered Jan 7, 2016 at 14:11

glenn jackman

249k42 gold badges233 silver badges362 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

econ · Accepted Answer · 2016-01-07 17:05:53Z

0

This might be a really crude way of doing it, but if you are certain that the values in each file do not repeat, then:

cat file1.txt file2.txt | sort | uniq -u

If each file contains repeating strings, then you can sort|uniq them before concatenation.

answered Jan 7, 2016 at 17:05

econ

5471 gold badge9 silver badges23 bronze badges

Collectives™ on Stack Overflow

comparing CSV files in ubuntu

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related