Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 1 down vote favorite

Each month, I receive a CSV file of our client base - email, job title, phone number etc. I need to identify changes between the two sheets, including changes to existing data as well as identifying new people added.

I want to compare the new sheet with the old one, highlight the changes, and proceed from there. Email address is the most constant line item, which we often use to find people. The two sheets will always have the same columns, but the rows may be out of order making an automated side-by-side comparison hard. Is it possible to use the email address as the starting point and then work along the row highlighting data which differs?

I know VLOOKUP could be used, but surely this would mean manually scanning over the pulled through data to see if it's the same in both sheets. Perhaps I am missing something here! All software solutions I have found compare the two line by line, meaning it goes wrong the second a new person is added in/removed and the sheets become out of sync.

Any help would be appreciated.

share|improve this question
About how many records are in a typical CSV file? If only a few thousand then maybe a small app will suffice. – FreudianSlip Jan 29 at 12:14
Whats the column name of your email column? Just "email"? – nixda Jan 29 at 17:21
I'd probably sort the rows (eg, by email address) and then use a compare tool. Sorting can be accomplished with the UNIX-style sort command, running on Cygwin, if you can't find a native Windows tool that does it. Once sorted, something like (free) BeyondCompare should show you the differences. – Daniel R Hicks Jan 30 at 1:54

3 Answers

up vote 1 down vote

If the contents can change and the rows may end up out of order, you need a way to put them in a determinate order. It sounds like you can use the email address to match records between the files, right? Sort each CSV file by the email column (or whatever other field can serve as the "key" for your data), then compare them using a diff tool or whatever.

How to do all that depends on the tools you've got in your toolbox. I'd use python and the csv module, but I'm sure your favorite scripting language is up to the task.

share|improve this answer
up vote 1 down vote

You'll need to sort them to use most diff tools. Try loading them both into Excel, sort them by email address, export them back out again as CSV and then use WinMerge to compare them.

share|improve this answer
It depends. If the changes are in the first columns, you'll see lots and lots of irrelevant changes due to the reordering. So I'd try with and without sorting, and perhaps sorting on known-unchanging columns (or columns that are what database people would call "primary keys"). – vonbrand Jan 29 at 17:17
up vote 1 down vote
  1. Make copies of your two csv files.
  2. Rename the extensions of the two copies of the csv files that you just made, replacing the ".csv" extension with ".txt".
  3. Install WinMerge and then open the two new ".txt" files that you just made in WinMerge. Now you can view all the differences between the two csv files graphically in WinMerge.

This link shows what differences between the two files look like in WinMerge.

share|improve this answer
it would be a good idea to sort the rows in both files first – AnonymousLurker Jan 30 at 3:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.