CompareExcelFiles Activity

Purpose: This activity compares two Excel files and returns the differences. The comparison uses identifier columns to match rows between the two files and identifies added, removed, and modified rows. This is useful for data validation, change tracking, and quality assurance processes.

Input Properties

FilePath1

The first Excel file path to compare. This is typically the reference or baseline file.

Type: string

Required: Yes

Example: "C:\Data\Baseline.xlsx"

FilePath2

The second Excel file path to compare. This is typically the file to compare against the baseline.

Type: string

Required: Yes

Example: "C:\Data\Current.xlsx"

IdentifierColumns

Comma-separated list of column names to use as identifiers for matching rows between the two files (e.g., 'Item Number,Part Number').

Type: string

Default: "Item Number,Part Number"

Example: "Item Number,Part Number" or "ID" or "SKU,Product Code"

Note: These columns are used to match rows between the two files. Rows with matching identifier values are compared for differences.

Output Properties

ComparisonResultsJson

The comparison results as a JSON string. Contains detailed information about differences between the two files, including added rows, removed rows, and modified rows.

Type: string

Note: Returns a JSON string with comparison results if successful, or an empty JSON object "{}" if the file paths are empty or if an error occurs.

AreEqual

True if the files are equal (no differences found), false otherwise.

Type: bool

Note: Returns true if no differences are found between the two files, false if differences exist or if an error occurs.

Success

True if the comparison was successful, false otherwise.

Type: bool

Note: Returns false if either file path is null or empty, if the files cannot be read, or if an error occurs during comparison.

Usage Example

Scenario: Compare two Excel files

Configuration:
- FilePath1: "C:\Data\Baseline.xlsx"
- FilePath2: "C:\Data\Current.xlsx"
- IdentifierColumns: "Item Number,Part Number"

Result:
- ComparisonResultsJson: "{\"AreEqual\": false, \"AddedRows\": [...], \"RemovedRows\": [...], \"ModifiedRows\": [...]}"
- AreEqual: false
- Success: true

Scenario: Compare identical files

Configuration:
- FilePath1: "C:\Data\File1.xlsx"
- FilePath2: "C:\Data\File1_Copy.xlsx"
- IdentifierColumns: "ID"

Result:
- ComparisonResultsJson: "{\"AreEqual\": true, ...}"
- AreEqual: true
- Success: true

Error Handling

Important: The activity handles errors gracefully:

  • If FilePath1 or FilePath2 is null or empty, ComparisonResultsJson will be set to "{}", AreEqual will be false, and Success will be false
  • If either file does not exist or cannot be read, ComparisonResultsJson will be set to "{}", AreEqual will be false, and Success will be false
  • If any exception occurs during comparison, ComparisonResultsJson will be set to "{}", AreEqual will be false, and Success will be false
  • The activity does not throw exceptions - errors are indicated by the Success output

Always check the Success output to verify if the comparison was successful before processing the ComparisonResultsJson data.

Important Notes

  • Both FilePath1 and FilePath2 must point to existing Excel files that are accessible and readable
  • The IdentifierColumns must exist in both files and should uniquely identify rows
  • Rows are matched based on the identifier columns - rows with matching identifier values are compared
  • The comparison results include added rows (in FilePath2 but not in FilePath1), removed rows (in FilePath1 but not in FilePath2), and modified rows (same identifier but different values)
  • Use meaningful identifier columns that uniquely identify rows (e.g., primary keys, part numbers)
  • Large files may take time to compare - consider file size when using this activity

Related Activities