Task description

book1.xlsx consists of two columns – Weeks and Amount, as shown below:

As the following pic shows, we want to transform the file into a format by entering the Amount value in a column of the corresponding week and listing the missing weeks.

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 the script:

Here’s the esProc code:

A1   Import the book1.xlsx file; @t option enables reading the first row as column headers.

A2   Add a new column ws to A1. Its values are Weeks.

A3   Group A2’s rows by Weeks and perform row-to-column transposition. ws values will be converted to new column names and Amount values will be the new column values. The new column names are listed in the order of "w1","w2","w3","w4","w5","w6".

A4   Export A3’s result to book2.xlsx; @t option enables exporting the first row as column headers.

3、 Press F9 to execute the script. You can view the target Excel table in book2.xlsx after execution is finished.

【Reference】 pivot_align.zip