Comparing Two Text Files - using Excel

This article is about comparing two text files, making a list of the differences between them and categorising each difference according to whether you care about it or not. An Excel file tool (available for download) is used, because this helps with making the final list.

The tool was made to support comparing two Blogger templates,as part of Seven Simple Steps to a Snazzy New-look Blog. But it can be used for many different types of file-comparisons where you need to think about the between the files, keep track of individual differences, and plan actions based on them.

In theory, you can compare two text-files line by line manually. But in practice, especially when the files are made from code like HTML and not English-language sentences, this is very hard to do: even a one-character difference may be significant, but hard to see.

There are many existing tools that will compare two files. But a spreadsheet works just as well - and it has other features that can be used to help with making a plans based on the differences found, and tracking progress with fixing them.

I've put some notes about where to put the comparison text files, and how to use the formulas into the spreadsheet, and some detailed pictures below. But the main point is that when you find a difference you need to:

Work out where the difference ends,
Insert some blank cells in the other column so that the code lines up again,
Re-copy the comparison formulas into column F (because the insert will have mis-aligned the existing ones),
Make notes about differences as they are found - this list is important for deciding what to do next.

Preparing to compare text files

Get the two template files that you want to compare, and open them with a text editor (eg Notepad).

They will look ugly and hard to work this - this is ok, the next step puts them in a better format.

A text file, viewed in Excel and looking ugly

Download the Excel tool for comparing text-files.

Paste the contents of the template file from your real blog in the left-hand column.

Paste the contents of the template from your test-blog in the middle column.

Paste the contents of the comparison formula cell (ie the red-shaded one) into every row where there are contents from either of source files.

NB the comparison formula is =IF(D8=A8, "", "Not the same")

In the spreadsheet the cell is shaded red, and you cannot see it unless you look in the formula bar (this makes the pictures easier to read).

Doing the Comparison

In Column F, look for the first row when "Not the same" is displayed.

Look at the code for that line, and work out what caused the difference, and whether it's is due to a customisation that you made and want to keep, or someone that you don't want to keep, or something else that Google have done in the meantime.

If the difference is due to a customisation that you want to keep, then make notes about this into the Comments and ToDo columns.

In the example shown, the first difference line is a meta-tag, which I do want to keep, but the second is just the tag-close command: I don't need to put it separately into my plan, so it's marked "no".

Re-match the lines of code:
If the difference was due to extra code, then

insert some extra rows in the other column so that the matching code lines up again, AND
re-copy the comparison forumula into the rows from there down.

Repeat these steps until all the differences are understood.

At the end, copy the comments and notes columns into another worksheet, and use Excel's sort functions to extract a list of the differences that you need to deal with.

(I like to use Excel to keep lists of planned changes too - but that's a whole different topic.)

Making a test-blog for testing template changes

Seven simple steps to a snazzy new-look blog.

Showing a PowerPoint presentation as a slideshow in your website

Planning changes to your blog - in private