Use Excel to DIFF Your Query Outputs

When modifying SQL code or when rewriting SQL-based applications, it is a good idea to ensure that the new outputs from queries, views, stored procedures and user functions match the former outputs, except of course for intended changes. While coding you may even want to improvise queries to compare old algorithms to new algorithms, in order to avoid changing so much code so quickly that you cannot recover from bad changes, and also to verify that intended changes do indeed occur. One way to quickly make such comparisons is to use Excel as your comparison tool.

The "CompareSheets" Spreadsheet

It is fairly easy to build a comparison utility using Excel. You can create the utility yourself using the instructions here, or you can simply download the finished workbook from this page (refer to the section that relates to this article). To compare the outputs of two queries, it makes sense to have an Excel workbook containing two spreadsheets, one to hold each query output. We start by making a new Excel workbook containing two pages named Sheet1 and Sheet2:

I like to widen the cells somewhat from the default, as shown here. You can uniformly set the width (or height) of ALL cells in a sheet by highlighting the entire sheet (click on the corner box at the intersection of the two headers) then adjusting the width of any one column (or the height of any one row). Now save the workbook with the name CompareSheets.xls.

The only remaining thing you need to convert this workbook into a comparison utility is a small amount of VBA code. The Diff routine (second routine below) indicates the presence of discrepancies between Sheet1 and Sheet2 by setting either the foreground or background color of each cell in which a discrepancy is found. The ClearMarkers routine (just below) clears all such "markers" that may have been set in a previous comparison, allowing you to perform multiple data comparisons by repeatedly pasting new versions of one or both sets of data and then rerunning the Diff routine.

Using the Visual Basic editor, insert the ClearMarkersroutine into the ThisWorkbook module:

' Clear all the indicators that previous comparisons may have set.
Private Sub ClearMarkers()
    ' Clear Sheet1
    Sheet1.Select
    Sheet1.Cells.Select
    Selection.Interior.ColorIndex = xlNone
    Selection.Font.ColorIndex = 0
    Sheet1.Cells(1, 1).Select ' undo selection of entire sheet
    ' Clear Sheet2
    Sheet2.Select
    Sheet2.Cells.Select
    Selection.Interior.ColorIndex = xlNone
    Selection.Font.ColorIndex = 0
    Sheet2.Cells(1, 1).Select ' undo selection of entire sheet
End Sub

Here is the Diff routine, which you should also copy into ThisWorkbook::

' Walk through Sheet1 and Sheet2, setting markers wherever differences
' in cell contents are found.
Public Sub Diff_Sheet_1_and_2()
    On Error GoTo ErrHandle
    Call ClearMarkers
    
    ' Determine range of used cells, using the highest row and column counts
    ' found in either of the two sheets.
    Dim HighRow As Long
    HighRow = Sheet1.UsedRange.Rows.Count
    If Sheet2.UsedRange.Rows.Count > HighRow Then
        HighRow = Sheet2.UsedRange.Rows.Count
    End If
    Dim HighCol As Long
    HighCol = Sheet1.UsedRange.Columns.Count
    If Sheet2.UsedRange.Columns.Count > HighCol Then
        HighCol = Sheet2.UsedRange.Columns.Count
    End If
    
    ' Walk through the cells of both sheets, comparing and changing colors
    ' if differences are found.
    Dim RowIndex As Long
    Dim ColIndex As Long
    
    Dim RowFirst As Long
    Dim ColFirst As Long
    
    For RowIndex = 1 To HighRow
    
        For ColIndex = 1 To HighCol
        
            ' Compare formulas, not "text" or other formatting-affected attributes.
            If Sheet1.Cells(RowIndex, ColIndex).Formula <> Sheet2.Cells(RowIndex, ColIndex).Formula Then
            
                ' Determine how to indicate the difference on Sheet1.
                If Sheet1.Cells(RowIndex, ColIndex).Text = "" Then
                    ' If the cell contains no text, highlight the empty cell.
                    Sheet1.Select
                    Sheet1.Cells(RowIndex, ColIndex).Select
                    Selection.Interior.ColorIndex = 38
                Else
                    ' If the cell contains text, color the text.
                    Sheet1.Cells(RowIndex, ColIndex).Font.Color = &HFF
                End If
                
                ' Determine how to indicate the difference on Sheet2.
                If Sheet2.Cells(RowIndex, ColIndex).Text = "" Then
                    ' If the cell contains no text, highlight the empty cell.
                    Sheet2.Select
                    Sheet2.Cells(RowIndex, ColIndex).Select
                    Selection.Interior.ColorIndex = 38
                Else
                    ' If the cell contains text, color the text.
                    Sheet2.Cells(RowIndex, ColIndex).Font.Color = &HFF
                End If
                
                ' If this is the first row containing a difference, remember the cell
                ' in which the change occurred so we can return to it later.
                If RowFirst = 0 Then
                    RowFirst = RowIndex
                    ColFirst = ColIndex
                End If
                
            End If
        Next
    Next
    
    ' Either report no differences or focus on the first difference found.
    If RowFirst = 0 Then
        MsgBox "No differences!"
    Else
        If ThisWorkbook.ActiveSheet.Index = 1 Then
            Sheet1.Cells(RowFirst, ColFirst).Activate
        End If
        If ThisWorkbook.ActiveSheet.Index = 2 Then
            Sheet2.Cells(RowFirst, ColFirst).Activate
        End If
    End If
    
    Exit Sub
    
ErrHandle:
    MsgBox Err.Description
End Sub

The Diff routine first calls ClearMarkers to clear all the "markers" on both sheets that may have been set in a previous comparison. It then effectively computes the "used range" for both sheets simultaneously (i.e. the used range of the union of the data on both sheets) by using the largest "UsedRange" value from either sheet in each dimension. This ensures that if either sheet contains more rows or columns than the other, the Diff routine will flag all such data as differences.

Next, the routine walks through every populated cell on either sheet, comparing the sheets cell-for-cell to see if their formulas differ. Note that the routine uses the cells' formulas, not their text or other properties that might be influenced by formatting. (However even the formulas might be modified by Excel when pasting in your data. For example if you have numeric strings such as zip codes that might have leading zeroes, you'll need to convert the data types of the appropriate columns on both sheets to "text" before pasting in order to prevent unwanted stripping of leading zeroes).

The Diff routine does one more thing: it remembers which cell contained the first discrepancy, so that at the end of the comparison it can move you to that cell in whichever sheet is active. This is very important with very large sets of data, where you might otherwise have to eyeball thousands of rows and columns in order to locate any of the discrepancies that the macro highlighted.

If you are building your own CompareSheets.xls rather than simply downloading it, then at this point you should save the workbook, close Excel and make the file read-only. This will prevent you from inadvertently saving the workbook with any data in it. After making the file read-only, reopen it with Excel.

Now let's run a test case. Let's assume we have a query that produces a list of country names, their corresponding ISO codes, and their respective continents. We present the entire list here in text form so that you can copy and paste it into your own version of CompareSheets.xls and then modify it to match the examples below:

To do our first comparison, paste the above table into Sheet1 of CompareSheets.xls. Then paste the same data onto Sheet2, and let's see what the macro does when we compare two identical sets of data:

Dismiss the dialog box by clicking the "OK" button. Now let's suppose we have modified the query that produced this output so that it mistakenly truncates every ContinentName to 10 characters. You can simulate the output of such a query by changing every occurrence of "America" to "Amer" in column "D" of Sheet2. Running our macro again we get:

Note two things:

• Every discrepancy is indicated by red-colored text.
   
• The macro has indicated the location of the first discrepancy by making it the current cell. This may seem irrelevant in this small example, but it's very important in very large examples..

If you switch over to Sheet1, you'll notice that the same corresponding cells are marked in the same way (and of course the data in them is different).

Now let's suppose that we have modified our query to inadvertently emit an empty string instead of the ContinentName "North America". You can simulate this problem by copying the data from Sheet1 to Sheet2, then substituting an empty string for "North America" in the ContinentName column. Let's run our macro again:

Sheet 1

Sheet 2

Here the results are somewhat different. On Sheet2there is no text to highlight in the differing cells, so the macro highlights the background instead. In the corresponding cells on Sheet1, the text is highlighted as before.

Summary

I've used this macro for years on every database project on which I've ever worked and it has probably saved me hundreds of hours, not to mention the headaches that would have resulted from unknowingly implementing code changes that produced unexpected output differences. This technique is far faster than any reporting-based solution because it allows you to instantly adapt to the schemas of any new queries you might dream up in the course of doing your coding.

System Requirements

This Excel macro was implemented using Excel 2003; it has been tested in both Excel 2003 and Excel 2007.

Author

You can contact me (Dave Ziffer) via the "Contact" link on my company's web site at www.ProjectPro.com. I'm also on LinkedIn. To keep abreast of my upcoming articles and projects, join the LinkedIn "Rapid Application Prototype" group.