Summarize an XLS File Every N Rows

Below is part of data.xls:

Computing goal: Perform aggregate every 4 rows to calculate the group number and the sum. Below is part of the result table:

The key point here is to summarize table every N rows, rather than group it by a specific column and then do the summarization.

Just handle it according to the following steps:

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 a script and execute it.

In this script, groups()function is used to group the table and perform aggregate. The number sign (#) represents a row number. #2 is column 2. (#-1)\4 means that the current row number subtracts 1 and then divided by 4 to get the quotient. The table is thus grouped according to whether or not the quotient is the same.

If the xls file is too large to fit into the memory, we can use the following script:

Note: Sequence number = batch number * 1000 + relative sequence number of the current batch.



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store