Exporting schema comparison results to Excel (4219381)

ApexSQL Diff can compare different data source types and export their differences into different formats including Excel.

After the comparison between data source types is completed, the main grid of the ApexSQL Diff is populated with the comparison results. It will have the option to select the all result items by selecting the checkbox above the grid.

To select more specific comparison results, it can be done by individually selecting the objects from the main grid. Below are two images where can be seen how complete selection and partial selection is done:

1. In the example below, only 3 objects are as a part of the partial selection of the comparison results:

   

2. In this example below, all objects are selected as a part of the complete selection of results:

   

Exporting into Excel

When desired results are selected, export it into Excel format with a click on the arrow button below the Export button in the Home tab and choose the Excel report:

Export options

After the Excel report is chosen from the drop-down menu, the Excel report options window is shown, as in the screenshot below. The Excel report options window is divided into three sections: Type, Selection and Details.

Excel reports can be customized.

• Type options allow users to select the type of result that needs to be exported:
  • - Different which will include different object
  • - Equal will include equal objects
  • - Source only will include object that exists only in source
  • - Destination only will include object that exists only in destination

  

  

• Selection

  If the Checked only option is selected, only the results that have been selected in the main grid will be exported. If the Checked only is not selected, all results from the main grid, that correspond to the Type option selection will be exported.

• Details

  Include scripts option allows users to include DDL SQL script for selected objects in the report.

  

  

Exporting

After the customization is done, the report can be exported and the location for the export file can be chosen in a window dialog box when appears to save the schema difference. The default file name and the location can be set as required from the Options menu in the toolbar shown below:

Default export folder and encoding of the output file can also be set also as required:

The file name template can be set as required by clicking the switches:

Here, the file name template has been changed by setting the Date, Time, Database 1 & Database 2 accordingly.

The Excel report can be exported in two ways:

Export Menu: Choose the Export menu in the actions toolbar and select Excel report and then save:

Save Menu: In the menu in the Projects toolbar, select Save as batch file. Here, the primary output types and the secondary output type can be set. The results can also be customized using the batch script. When Excel is set as a primary output, the export option can be changed from the adjoining button and the same applies to the secondary output type too.

The batch file is saved by clicking the Save button. The Excel report then can be generated by running the batch file. An example showing the run of batch file is shown below:

A comparison Excel report will look like the one in the screenshot below:

At the Excel report options, Different, Equal, Source only & Destination only were selected, which is reflected back on the comparison result.

Similarly, since the Include script option was checked in the report options, scripts of all exported objects will be shown in the report:

FAQs

Q: Is there a limit to the number of result items that can be exported? A: Export size is only limited by Excel itself. See Excel row limits by version

Q: Do I have to have Excel installed? A: Yes, Excel needs to be installed to view the export result

Q: What versions of Excel are supported? A: Excel 2003 and higher versions are supported

Q: Can I export results via CLI? A: Yes, Results can be exported via CLI, check pictures bellow:

Here is a sample batch script from the screenshot above

" ApexSQLDiff" /backup1:" AdventureWorks2017.bak" /backup2:" AdventureWorks2012.bak" /ignore_general:iws ic iso idep iio /ignore_table_attributes:ico ids idc ifc /struct_sync_options:iddo ith sud /encoding:Unicode /output_elements:cmt ps /output_type:excel /excel_report_options:e d s t co is /output_name:" 2018052517-44-32AdventureWorks2017AdventureWorks2012.xml" /force /verbose

The different CLI switches used in this batch script are:

• /backup: Allows providing the path for the destination backup
• iws: ignores white space
• ic: ignores Case
• iso: Ignores System Objects
• idep: Ignores Database Extended Properties
• iio: Ignores invalid objects
• ico: Ignores column order
• ids: Ignores data spaces
• idc: Ignores data compression
• ifc: Ignores fill factor
• iddo: Includes Dependent Database objects
• ith: Includes Transaction handling
• sud: Script use database
• /excel report option: e: Equal, d: Different, s: Source, t: Destination, co: Checked only, is: Include scripts

These can also be changed at the beginning when selecting the data source type. Below is a screenshot which shows a variety of comparison options: