Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Use Excel to DIFF Your Query Outputs

By David Ziffer,

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:

CountryName ISO2 ISO3 ContinentName
Afghanistan AF AFG Asia
Albania AL ALB Europe
Algeria DZ DZA Africa
American Samoa AS ASM Oceania
Andorra AD AND Europe
Angola AO AGO Africa
Anguilla AI AIA North America
Antarctica AQ ATA Antarctica
Antigua And Barbuda AG ATG North America
Argentina AR ARG South America
Armenia AM ARM Asia
Aruba AW ABW North America
Australia AU AUS Oceania
Austria AT AUT Europe
Azerbaijan AZ AZE Asia
Bahamas BS BHS North America
Bahrain BH BHR Asia
Bangladesh BD BGD Asia
Barbados BB BRB North America
Belarus BY BLR Europe
Belgium BE BEL Europe
Belize BZ BLZ North America
Benin BJ BEN Africa
Bermuda BM BMU North America
Bhutan BT BTN Asia
Bolivia BO BOL South America
Bosnia And Herzegovina BA BIH Europe
Botswana BW BWA Africa
Bouvet Island BV BVT Antarctica
Brazil BR BRA South America
British Indian Ocean Territory IO IOT Asia
Brunei Darussalam BN BRN Asia
Bulgaria BG BGR Europe
Burkina Faso BF BFA Africa
Burundi BI BDI Africa
Cambodia KH KHM Asia
Cameroon CM CMR Africa
Canada CA CAN North America
Cape Verde CV CPV Africa
Cayman Islands KY CYM North America
Central African Republic CF CAF Africa
Chad TD TCD Africa
Chile CL CHL South America
China CN CHN Asia
Christmas Island CX CXR Asia
Cocos (Keeling) Islands CC CCK Asia
Colombia CO COL South America
Comoros KM COM Africa
Congo CG COG Africa
Cook Islands CK COK Oceania
Costa Rica CR CRI North America
Cote D'ivoire CI CIV Africa
Croatia (Hrvatska) HR HRV Europe
Cuba CU CUB North America
Cyprus CY CYP Asia
Czech Republic CZ CZE Europe
Denmark DK DNK Europe
Djibouti DJ DJI Africa
Dominica DM DMA North America
Dominican Republic DO DOM North America
East Timor TL TLS Asia
Ecuador EC ECU South America
Egypt EG EGY Africa
El Salvador SV SLV North America
Equatorial Guinea GQ GNQ Africa
Eritrea ER ERI Africa
Estonia EE EST Europe
Ethiopia ET ETH Africa
Falkland Islands (Malvinas) FK FLK South America
Faroe Islands FO FRO Europe
Fiji FJ FJI Oceania
Finland FI FIN Europe
France FR FRA Europe
French Guiana GF GUF South America
French Polynesia PF PYF Oceania
French Southern Territories TF ATF Antarctica
Gabon GA GAB Africa
Gambia GM GMB Africa
Georgia GE GEO Asia
Germany DE DEU Europe
Ghana GH GHA Africa
Gibraltar GI GIB Europe
Greece GR GRC Europe
Greenland GL GRL North America
Grenada GD GRD North America
Guadeloupe GP GLP North America
Guam GU GUM Oceania
Guatemala GT GTM North America
Guinea GN GIN Africa
Guinea-Bissau GW GNB Africa
Guyana GY GUY South America
Haiti HT HTI North America
Heard Island & McDonald Islands HM HMD Antarctica
Honduras HN HND North America
Hong Kong HK HKG Asia
Hungary HU HUN Europe
Iceland IS ISL Europe
India IN IND Asia
Indonesia ID IDN Asia
Iran, Islamic Republic Of IR IRN Asia
Iraq IQ IRQ Asia
Ireland IE IRL Europe
Israel IL ISR Asia
Italy IT ITA Europe
Jamaica JM JAM North America
Japan JP JPN Asia
Jordan JO JOR Asia
Kazakhstan KZ KAZ Asia
Kenya KE KEN Africa
Kiribati KI KIR Oceania
Korea, Democratic People's Republic Of KP PRK Asia
Korea, Republic Of KR KOR Asia
Kuwait KW KWT Asia
Kyrgyzstan KG KGZ Asia
Laos, People's Democratic Republic LA LAO Asia
Latvia LV LVA Europe
Lebanon LB LBN Asia
Lesotho LS LSO Africa
Liberia LR LBR Africa
Libyan Arab Jamahiriya LY LBY Africa
Liechtenstein LI LIE Europe
Lithuania LT LTU Europe
Luxembourg LU LUX Europe
Macau MO MAC Asia
Macedonia MK MKD Europe
Madagascar MG MDG Africa
Malawi MW MWI Africa
Malaysia MY MYS Asia
Maldives MV MDV Asia
Mali ML MLI Africa
Malta MT MLT Europe
Marshall Islands MH MHL Oceania
Martinique MQ MTQ North America
Mauritania MR MRT Africa
Mauritius MU MUS Africa
Mayotte YT MYT Africa
Mexico MX MEX North America
Micronesia, Federated States Of FM FSM Oceania
Moldova, Republic Of MD MDA Europe
Monaco MC MCO Europe
Mongolia MN MNG Asia
Montserrat MS MSR North America
Morocco MA MAR Africa
Mozambique MZ MOZ Africa
Myanmar MM MMR Asia
Namibia NA NAM Africa
Nauru NR NRU Oceania
Nepal NP NPL Asia
Netherlands NL NLD Europe
Netherlands Antilles AN ANT North America
New Caledonia NC NCL Oceania
New Zealand NZ NZL Oceania
Nicaragua NI NIC North America
Niger NE NER Africa
Nigeria NG NGA Africa
Niue NU NIU Oceania
Norfolk Island NF NFK Oceania
Northern Mariana Islands MP MNP Oceania
Norway NO NOR Europe
Oman OM OMN Asia
Pakistan PK PAK Asia
Palau PW PLW Oceania
Panama PA PAN North America
Papua New Guinea PG PNG Oceania
Paraguay PY PRY South America
Peru PE PER South America
Philippines PH PHL Asia
Pitcairn PN PCN Oceania
Poland PL POL Europe
Portugal PT PRT Europe
Puerto Rico PR PRI North America
Qatar QA QAT Asia
Reunion RE REU Africa
Romania RO ROU Europe
Russian Federation RU RUS Europe
Rwanda RW RWA Africa
Saint Kitts And Nevis KN KNA North America
Saint Lucia LC LCA North America
Saint Vincent And The Grenadines VC VCT North America
Samoa WS WSM Oceania
San Marino SM SMR Europe
Sao Tome And Principe ST STP Africa
Saudi Arabia SA SAU Asia
Senegal SN SEN Africa
Serbia CS SCG Europe
Seychelles SC SYC Africa
Sierra Leone SL SLE Africa
Singapore SG SGP Asia
Slovakia (Slovak Republic) SK SVK Europe
Slovenia SI SVN Europe
Solomon Islands SB SLB Oceania
Somalia SO SOM Africa
South Africa ZA ZAF Africa
Spain ES ESP Europe
Sri Lanka LK LKA Asia
Saint Helena SH SHN Africa
Saint Pierre And Miquelon PM SPM North America
Sudan SD SDN Africa
Suriname SR SUR South America
Svalbard And Jan Mayen Islands SJ SJM Europe
Swaziland SZ SWZ Africa
Sweden SE SWE Europe
Switzerland CH CHE Europe
Syrian Arab Republic SY SYR Asia
Taiwan, Province Of China TW TWN Asia
Tajikistan TJ TJK Asia
Tanzania, United Republic Of TZ TZA Africa
Thailand TH THA Asia
Togo TG TGO Africa
Tokelau TK TKL Oceania
Tonga TO TON Oceania
Trinidad And Tobago TT TTO North America
Tunisia TN TUN Africa
Turkey TR TUR Asia
Turkmenistan TM TKM Asia
Turks And Caicos Islands TC TCA North America
Tuvalu TV TUV Oceania
Uganda UG UGA Africa
Ukraine UA UKR Europe
United Arab Emirates AE ARE Asia
United Kingdom UK GBR Europe
United States US USA North America
United States Minor Outlying Islands UM UMI North America
Uruguay UY URY South America
Uzbekistan UZ UZB Asia
Vanuatu VU VUT Oceania
Vatican City State (Holy See) VA VAT Europe
Venezuela VE VEN South America
Viet Nam VN VNM Asia
Virgin Islands (British) VG VGB North America
Virgin Islands (U.S.) VI VIR North America
Wallis And Futuna Islands WF WLF Oceania
Western Sahara EH ESH Africa
Yemen YE YEM Asia
Zaire ZR ZAR Africa
Zambia ZM ZMB Africa
Zimbabwe ZW ZWE Africa

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.

Total article views: 1308 | Views in the last 30 days: 3
 
Related Articles
FORUM

multi queries result to different excel sheets

multi queries result to different excel sheets

FORUM

Issue looping through multiple excel files with different sheet names

looping through multiple excel files with different sheet names

FORUM

extract data from oledb source to excel work sheets

extract differant data from a sql tble to excel work sheet wise

FORUM

Delete Excel sheets in DTS

Delete Excel sheets in DTS

FORUM

Read from Excel write to Excel named sheet

Read from Excel write to Excel named sheet

Tags
excel    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones