Task description

In contact information file address.xlsx, Sheet1 stores data at an earlier time and Sheet2 contains data at an later time, as shown below:

We want to compare the two tables to find newly-added records, deleted records, and modified records and store them respectively in Sheet Added, Deleted and Changes, as shown below:

 

Directions:

1. Start esProc

Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time

2. Write script in esProc:

(1) To get the newly-added:

Here’s the code:

	A
1	=file("E:/txl/address.xlsx")
2	=A1.xlsimport@t(;"Sheet1").keys(ID)
3	=A1.xlsimport@t(;"Sheet2").keys(ID)
4	=[A3,A2].merge@d(ID)
5	=A1.xlsexport@ta(A4;"Added")

A1   Define data file address.xlsx.

A2   Import data of Sheet1, and set ID as the primary key; @t option enables reading the first row as column headers.

A3   Import data of Sheet2, and set ID as the primary key; @t option enables reading the first row as column headers.

A4   @d option enables removing records whose IDs are also included in A2 from A3 to get the newly-added records.

A5   Export the newly-added records to Sheet Added.

 

(2)To get the deleted:

esProc script:

	A
1	=file("E:/txl/address.xlsx")
2	=A1.xlsimport@t(;"Sheet1").keys(ID)
3	=A1.xlsimport@t(;"Sheet2").keys(ID)
4	=[A2,A3].merge@d(ID)
5	=A1.xlsexport@ta(A4;"Deleted")

A4   @d option enables removing records whose IDs are also included in A3 from A2 to get the deleted records.

A5   Export the deleted records to Sheet Deleted.

 

(3) To get the modified

We have the following script:

	A
1	=file("E:/txl/address.xlsx")
2	=A1.xlsimport@t(;"Sheet1").keys(ID)
3	=A1.xlsimport@t(;"Sheet2").keys(ID)
4	=join(A2:old,ID;A3:new,ID)
5	=A4.select(old.array()!=new.array()).conj(new)
6	=A1.xlsexport@ta(A5.switch(ID);"Changes")

A4   Join A2 and A3 through their primary keys to get records having common IDs. The new columns in the joined table are old and new.

A5   Select from A4 the records where the sequence of old column values and the sequence of new values are not equal. And concatenate them with those with the new column values to get the modified records in A3.

A6   Remove the association from the modified records and store them in Sheet Changes.

   

3、 Press F9 to execute the script. You can open address.xlsx to view the three newly generated sheets after execution.

 

 

【Reference】 address.zip