0018 Compare two files

Compare two files and keep only new or changed records

This example shows how to compare records from two files and write only the rows that are new or changed. It is useful when you receive repeated extracts and do not want to reload the same records every time. Reloading everything is simple. Explaining duplicate downstream reports is less simple.

Comparison logic

The transformation reads the current file, checks each record against a second file, and uses validation logic to decide whether the row should be written to the output.

Run the example

• Download and install Advanced ETL Processor [Link]
• Download and unzip the example [Link]
• Create a new transformation and open the .ats file.
• Double-click the Reader object and amend the source file path.
• Double-click the Writer object and amend the target file path, leaving the calculation intact.
• Double-click the transformation, then double-click the lookup and amend the comparison file path.
• Press the green arrow to run the transformation.

Transformation overview

The workflow uses a lookup to find matching records in the comparison file, then validation rules decide whether the current row is new, changed, or unchanged.

Lookup settings

The lookup points to the file used as the comparison baseline. Check the file path and key fields before running the transformation against real data.

Validation rules

The validation step controls which records continue to the writer. Configure it to pass records that are missing from the lookup result or where important field values have changed.

More help

There are other ways to compare and transform the data. Contact us if you need help choosing the right approach.

Visit ETL Tools Forum