Hi, how can we help you?

Compare output results to an Excel .xlsx file.  Depending on the options chosen, there will be two or more sheets in the file. These are:

• Compare – the main analysis output containing all records or only detected differences, depending on the output option selected.
• Warnings  – summary of warnings and errors detected from the analysis (only displays if errors were detected in the analysis).
• Stats – file names and statistics from the analysis.
• First File – the content of the first file analyzed (if the output original data sheet was selected).
• Second File – the content of the second file analyzed (if the output original data sheet was selected).

This sheet contains the details of the comparison between the two files. This sheet will either contain all records or just those that are different between the files, depending on the chosen option.

The first column contains the key field(s) used as the basis for the comparison. If you selected a single key, the value of that key will be shown for each record. If you selected multiple keys, each key will be included in the column separated by a “#” symbol. The second column indicates whether the record matches between the files (with the word MATCH) or has differences (with the word DIFF). This can be handy, especially when working with wide files with many fields.

Subsequent columns (fields) are presented in three-column structures: [Column Heading]: Field Name. Flagged as a MATCH if the data is the same in this field for each record, or DIFF if the data is different [1-Column Heading]: The field data in the first file for each record. [2-Column Heading]: The field data in the second file for each record.

Note: you may need to expand the column widths to see the entire field names. If you have selected all records as an output option, you can still filter the excel sheet to show only the differences by clicking the dropdown under Column B and selecting only DIFF’s. If the data matches in a row, the Full Record column indicates MATCH. If the data is different, the fields are highlighted, and the first column indicates DIFF.

When you find differences in records, you can indicate whether to accept them or fix them (in the source system) by selecting the DIFF cell next to the field to be corrected and changing the flag to ACCEPT or FIX. This is a handy way to create a worklist of records to correct as an outcome of this analysis.  Note: there may be more than one field that is different in a record. If you accept all the differences, the entire record will be flagged as ACCEPT in column B.  If there are one or more fields to be fixed, column B will show FIX.  At the end of your analysis, you may want to filter Column B to just show the records to be Fixed, which will create the worklist of records to update in the source system. 

The Warnings Sheet contains any errors or warnings detected during the analysis. If there were no warnings detected, this tab would not be included in the output sheet. The most common warning is if duplicate records are detected in a sheet. This can happen if the key(s) selected are insufficient to define unique records. If duplicates are detected, the second and subsequent records are logged in the Warnings sheet, and the record is not used in the analysis. You may wish to re-run the Compare using additional or new keys if duplicate records are detected. If you believe the keys are correct, you can use the output in the Warnings tab to inspect the source system to cleanse duplicate records. 

The Stats sheet contains a summary of the statistics of the analysis. This includes a record of the file names and paths compared, a summary of the record counts for both files, the total number of unique records, total different records, and any warnings generated.

If selected as an output option, Compare will write the source data of each file to separate sheets in the output file. You may choose to do this if you would like a single excel file to contain the source and output of the analysis. 

Note: writing these files in Excel can lengthen the duration of the analysis substantially, depending on the size and structure of the files. We recommend not using this option if the files are large.