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.