SQLServerCentral / Discuss Content Posted by David Ziffer / Article Discussions / Article Discussions by Author / Use Excel to DIFF Your Query Outputs / Latest Posts

RE: Use Excel to DIFF Your Query Outputs

Nick Thompson-356919 — Fri, 02 Jan 2009 05:25:45 GMT

Excel is a very versatile tool, as your article demonstrates.A couple of mods I'd like to see in your code are:1. Show the value from the other worksheet as a comment (saves skipping back and forth between worksheets to see what the difference that's been identified actually is). You can do this by adding these lines:[code] Sheet1.Cells(RowIndex, ColIndex).AddComment "Value in worksheet " & Sheet2.Name & " is " & Sheet2.Cells(RowIndex, ColIndex).Formula Sheet2.Cells(RowIndex, ColIndex).AddComment "Value in worksheet " & Sheet1.Name & " is " & Sheet1.Cells(RowIndex, ColIndex).Formula[/code]insert the above code just after:[code] ' Compare formulas, not "text" or other formatting-affected attributes. If Sheet1.Cells(RowIndex, ColIndex).Formula <> Sheet2.Cells(RowIndex, ColIndex).Formula Then[/code]NB: If you're running this multiple times on the same data you need to clear the existing comments first.2. If a row in one sheet doesn't exist in the other sheet, this has the effect of labelling every subsequent row as being different. It would be useful to have the option to look ahead at the next x number of rows in the dataset and use them to determine whether a row's missing, and if it is then to flag that, rather than considering every subsequent row as changed.Re the earlier post suggesting modifying the code to deal with different sheet names, the code in the article does already do this.Nick

RE: Use Excel to DIFF Your Query Outputs

Chad Crawford — Wed, 31 Dec 2008 13:17:29 GMT

Another tool to put in your belt is EXCEPT:[code]SELECT * FROM table1EXCEPTSELECT * FROM table2[/code]will give you all the records in table1 that do not exactly match a record in table2. It doesn't display them side-by-side, but does tell you which record(s) are different. Reverse it to get the records in table2 that do not exactly match a record in table1.Another quick shortcut I have used is:[code]SELECT COUNT(*) FROM table1SELECT COUNT(*) FROM table2SELECT COUNT(*)FROM (SELECT * FROM table1 UNION SELECT * FROM table2) Combined[/code]If all three counts are the same, the tables are identical. Again, if there are differences it doesn't tell you where they are, but it is very easy to see [i]if[/i] there are differences.Chad

RE: Use Excel to DIFF Your Query Outputs

GSquared — Wed, 31 Dec 2008 07:29:24 GMT

My usual method for comparing data sets from SQL databases is just simply do an outer join between the two. For procs, that means inserting them into temp tables and then joining the temp tables.For example:[code]create table #T1 (CountryName varchar(100) primary key,ISO2 char(2),ISO3 char(3),ContinentName varchar(100));insert into #T1 (CountryName, ISO2, ISO3, ContinentName)exec dbo.MyProc1;create table #T2 (CountryName varchar(100) primary key,ISO2 char(2),ISO3 char(3),ContinentName varchar(100));insert into #T2 (CountryName, ISO2, ISO3, ContinentName)exec dbo.MyProc2;select *from #T1 t1full outer join #T2 t2 on t1.CountryName = t2.CountryName and t1.ISO2 = t2.ISO2 and t1.ISO3 = t2.ISO3 and t1.ContinentName = t2.ContinentNamewhere t2.CountryName is nullor t1.CountryName is null;[/code]That will give you a full set of discrepancies between the two record sets.If you want to change the comparison, change the join. For example, you might want to just know which countries have exact matches, but where the continent is different. So change the join to:[code] on t1.CountryName = t2.CountryName and t1.ContinentName != t2.ContinentName[/code](And get rid of the where clause.)You'll immediately have those differences. And so on, whatever comparisons you want to do.Make a change to one of the procs, truncate the table its results are in, rerun the insert statement, and rerun your comparisons, till you get matching result sets.If the result sets are large enough and the comparisons are complex enough, you can even add indexes to the temp tables to make the queries faster.Also, if it matters, this doesn't run into the max rows issues that Excel has (though 2007 vastly increased the number of rows allowed).Not saying this method is better, just saying it's what I use.

RE: Use Excel to DIFF Your Query Outputs

Christopher Stobbs — Wed, 31 Dec 2008 05:33:56 GMT

Great tool.I'm already started using it and it's a charm.I made some changes for mine so that it could work in any workbook regardless of the names of the sheets. It will do the campare on the first 2 sheets in the active workbook.I've pasted my code under my personal xls modules so that my macro becomes global.Here is the modified code if anyone is interested:[code]' Clear all the indicators that previous comparisons may have set.Private Sub ClearMarkers() ' Clear Sheet1 'ThisWorkbook.Sheets.Select ActiveWorkbook.Sheets(1).Activate ActiveWorkbook.ActiveSheet.Cells.Select Selection.Interior.ColorIndex = xlNone Selection.Font.ColorIndex = 0 ActiveWorkbook.ActiveSheet.Cells(1, 1).Select ' undo selection of entire sheet ' Clear Sheet2 ActiveWorkbook.Sheets(2).Activate ActiveWorkbook.ActiveSheet.Cells.Select Selection.Interior.ColorIndex = xlNone Selection.Font.ColorIndex = 0 ActiveWorkbook.ActiveSheet.Cells(1, 1).Select ' undo selection of entire sheetEnd Sub' 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 = ActiveWorkbook.Sheets(1).UsedRange.Rows.Count If ActiveWorkbook.Sheets(2).UsedRange.Rows.Count > HighRow Then HighRow = ActiveWorkbook.Sheets(2).UsedRange.Rows.Count End If Dim HighCol As Long HighCol = ActiveWorkbook.Sheets(1).UsedRange.Columns.Count If ActiveWorkbook.Sheets(2).UsedRange.Columns.Count > HighCol Then HighCol = ActiveWorkbook.Sheets(2).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 ActiveWorkbook.Sheets(1).Cells(RowIndex, ColIndex).Formula <> ActiveWorkbook.Sheets(2).Cells(RowIndex, ColIndex).Formula Then ' Determine how to indicate the difference on Sheet1. If ActiveWorkbook.Sheets(1).Cells(RowIndex, ColIndex).Text = "" Then ' If the cell contains no text, highlight the empty cell. ActiveWorkbook.Sheets(1).Select ActiveWorkbook.Sheets(1).Cells(RowIndex, ColIndex).Select Selection.Interior.ColorIndex = 38 Else ' If the cell contains text, color the text. ActiveWorkbook.Sheets(1).Cells(RowIndex, ColIndex).Font.Color = &HFF End If ' Determine how to indicate the difference on Sheet2. If ActiveWorkbook.Sheets(2).Cells(RowIndex, ColIndex).Text = "" Then ' If the cell contains no text, highlight the empty cell. ActiveWorkbook.Sheets(2).Select ActiveWorkbook.Sheets(2).Cells(RowIndex, ColIndex).Select Selection.Interior.ColorIndex = 38 Else ' If the cell contains text, color the text. ActiveWorkbook.Sheets(2).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 ActiveWorkbook.ActiveSheet.Index = 1 Then ActiveWorkbook.Sheets(1).Cells(RowFirst, ColFirst).Activate End If If ActiveWorkbook.ActiveSheet.Index = 2 Then ActiveWorkbook.Sheets(2).Cells(RowFirst, ColFirst).Activate End If End If Exit Sub ErrHandle: MsgBox Err.DescriptionEnd Sub[/code]

RE: Use Excel to DIFF Your Query Outputs

Gregor Borosa — Wed, 31 Dec 2008 03:25:17 GMT

Hi... interesting idea, until now I was using Excel mainly for cross-checking query outputs by pivot tables, while using [url=http://kdiff3.sourceforge.net/]KDiff[/url] for diffing as it is specialized for the job.Happy NewYear from me, too ;)

RE: Use Excel to DIFF Your Query Outputs

Anipaul — Wed, 31 Dec 2008 00:41:53 GMT

Nice article...Happy New Year to you all...

Use Excel to DIFF Your Query Outputs

David Ziffer — Wed, 31 Dec 2008 00:38:07 GMT

Comments posted to this topic are about the item [B]Use Excel to DIFF Your Query Outputs[/B]