No announcement yet.

Ability to compare multiple Excel worksheets

  • Filter
  • Time
  • Show
Clear All
new posts

  • Ability to compare multiple Excel worksheets

    I am a happy many-year, many-version Beyond Compare Professional user (woo hoo! Folder merge!), and I normally use it to compare text files (mostly source code).

    Today for the first time I needed to compare Excel files (which I had created using Java POI from SQL data). Excited about this new opportunity to use a favorite tool in a new way, I selected left and right file to compare, and tada! There was the first worksheet of the workbooks, nicely compared. Great!

    Now I look for how to go to another worksheet. Hmm...Can't find it.

    Google leads me to

    Apparently I need to load each workbook in Excel, go to another worksheet, save the workbook, and recompare. These workbooks have five worksheets. This is a nonstarter.

    Please fix this. Claiming to support Excel while not supporting comparison of multisheet workbooks is not acceptable.

    Let me reiterate my love of Beyond Compare and my hope for many more years of support and enhancements.


  • #2
    Thanks for the feedback. This is something we want to tackle, but have not had the ability to solve this problem yet. It's definitely on our radar, but is also not a small task that we will be able to quickly fix.
    Aaron P Scooter Software


    • #3

      Did you read the second (Using the text compare session) and third (Advanced Technique) entries in the multi-sheet Excel page you linked to? They do provide instructions to display a multi-sheet Excel file in a single window in the Text Compare as CSV or in the Table Compare.
      Chris K Scooter Software


      • #4

        Thanks. I'm glad you're looking at it.


        I did look at the other solutions. Unfortunately, in my case each sheet has different column names and data, so I don't think they would be applicable. Each sheet is the results of a query from a different SQL table, or the processed results of such a query, except for the first sheet, which gives information on the overall run of the tool.



        • #5

          If you have any sample files you would like us to add to our test cases, I can add those to our entry on the subject. You can email us at [email protected] with a link back to this forum thread for our reference.
          Aaron P Scooter Software


          • #6
            Originally posted by ludinom View Post
            Apparently I need to load each workbook in Excel, go to another worksheet, save the workbook, and recompare.
            Beyond compare 4.1 has improved table comparison. Is it possible to improve support for multisheet workbooks?

            To illustrate my concerns
            Copy a multisheet work book
            Open the copy, change to a new work sheet, add text to one cell and save.
            Use Beyond compare 4.1 table compare

            The comparison will not show the one changed cell. It will show the difference between the worksheet last looked at in the original workbook and the worksheet last looked at in the copy of the workbook. The resultant display is rarely useful.

            1) Within workbooks compare worksheets with the same name (or order position if that is easer).
            2) Allow user to select which worksheets are compared (on both sides if option 1 is not implemented).

            The above would mean when a workbook is changed, Beyond compare can be used to display the changes in a meaningful manner. Enabling the user to determine which version they want to keep or other implications of the edits. Without going to and changing the documents the user is trying to compare.
            Last edited by patch; 20-Jun-2015, 02:26 AM.


            • #7
              Thanks for the feedback. We do not support multi sheet Excel files out of the box, but it's something we are looking into. It's unlikely we'll have drastic changes to our 4.1 support; we're currently beta testing and getting it ready for a full release.
              Aaron P Scooter Software


              • #8
                In the meantime, until Excel spreadsheets are fully supported, I have found a pretty useful way to compare multi-sheet files by creating a .zip archive. I am using VB script to extract each sheet into a tab-separated-values file, and then roll them into a single file. Since the Folder view in Beyond Compare allows you to drill into a .zip archive, it quickly shows which sheets have been changed:

                Click image for larger version

Name:	ZipCompare1.jpg
Views:	1
Size:	93.0 KB
ID:	76390

                A side-effect of the script is that the new .zip files are created next to the original .xls files, so a quick refresh is needed to see the archive files in the folder structure. In the image above the archive is expanded, and the three changed sheets are highlighted. Comparing one of them brings up the normal data view:

                Click image for larger version

Name:	ZipCompare2.jpg
Views:	1
Size:	99.3 KB
ID:	76391

                One challenge was to keep the data from each sheet "grouped" together. By default, the data view sets the first column to be a Key column, so I chose to put the sheet name in that column, preceded by the sheet number. This way, the sheets stay in their original order and rows within each sheet can be sorted within the sheet. Notice the first column in this view of the entire Excel file:

                Click image for larger version

Name:	ZipCompare0.jpg
Views:	1
Size:	104.7 KB
ID:	76392

                Obviously, this approach assumes many things that may not apply to your situation (uniquely named tabs, column headers in the first row, no more than 99 tabs, writeable folders, Windows OS with Excel installed, etc.). But it may be a good starting point to roll your own scripts, or you can contact me if you want more details.


                • #9
                  We appreciate the feedback. If you would like to submit any of the specific script, you can post or email us at [email protected] (with a link back to this forum thread for our reference). We wouldn't be able to implement this exact solution, extracting to a Folder Compare, but there may be useful information for our developers when they are able to research this project.
                  Aaron P Scooter Software