...

Group & Summarize an XLS file without Using Excel

 
excel(95)

Below is the source Excel table:

A

B

1

A

1

2

A

1

3

A

9

4

B

2

5

B

2

6

B

3

7

C

5

8

D

4

9

E

7

10

E

1

Task: Process the xls file outside Excel (not using Excel). Specific requirements are like these: group rows by column A and then sum column for each group.

Below is the expected result:

A

B

1

A

11

2

B

7

3

C

5

4

D

4

5

E

8

 

To get this done, you can follow these 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.

A

1

=file("D:/data.xlsx").xlsimport()

2

=A1.groups(_1;sum(_2))

3

=file("D:/result.xlsx").xlsexport(A2)

In the above script, group() function is used to group rows and do the summarization. In it the first parameter is the grouping expression, and the second parameter is the aggregate expression. You can use a column number or a column name in an expression.


excel(95)
 
 •  329 View  •