Compare Two Rowwise Worksheets to Get Their Differences
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:
A 
B 

1 
A1 
AV1 
2 
A2 
AV2 
3 
A3 
AV3 
4 
A4 
AV4 
5 
A5 
AV5 
Sheet2:
A 
B 

1 
A1 
AV100 
2 
A2 
AV2 
3 
A3 
AV3 
4 
A4 
AV4 
5 
A6 
AV6 
We are trying to find differences between Sheet2 and Sheet1 and write them to new sheets. Sheet added stores all newlyadded data, as shown below:
A 
B 

1 
A6 
AV6 
And sheet removed stores all deleted data, as shown below:
A 
B 

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

1 
A1 
AV100 
The algorithm involves record merge and setoriented operations.
Solution & explanation
Write the following esProc script and execute it:
A 
B 
C 

1 
=file("050/data.xlsx") 

2 
=A1.xlsopen() 

3 
=A2.xlsimport(;"Sheet1") 
=A2.xlsimport(;"Sheet2") 

4 
=[B3,A3].merge@od(_1) 
=[A3,B3].merge@od(_1) 
=[[B3,A3].merge@od(), A4].merge@od() 
5 
=A2.xlsexport(A4;"added") 
=A2.xlsexport(B4;"removed") 
=A2.xlsexport(C4;"modified") 
6 
=A1.xlswrite(A2) 
A1B3,A5C6: Read data from and write data to Excel sheets.
A4: Perform difference between B3 and A3 by comparing their first columns to get the newlyadded 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 newlyadded 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.