Problem description & analysis

data.xlsx has two rowwise sheets of same structure, where column A is the logical primary key. Sheet2 contains new data and Sheet1 contains old data, as shown below:

Sheet1:

Sheet2:

We are trying to find differences between Sheet2 and Sheet1 and write them to new sheets. Sheet added stores all newly-added data, as shown below:

And sheet removed stores all deleted data, as shown below:

sheet modified stores all modified data (primary key values remain the same while values of other columns are changed), as shown below:

The algorithm involves record merge and set-oriented operations.

Solution & explanation

Write the following esProc script and execute it:

A1-B3,A5-C6: Read data from and write data to Excel sheets.

A4: Perform difference between B3 and A3 by comparing their first columns to get the newly-added data. merge function performs merge operation, where @o option is used to disable sorting on memory data and @d option enables getting differences during merge.

B4: Get the deleted data.

C4: Compare the whole rows in two tables to get the newly-added or modified data, which is then compared with A4 by rows to get the modified data. Since the second comparison is on the same table sequence, we can do it directly through getting the differences, which is =[B3,A3].merge@od() \ A4.

Q & A Collection

https://stackoverflow.com/questions/63841318/comparing-column-a-to-b-and-b-to-a-and-copy-entire-row-of-missing-and-added-to-n