An Easy Way to Merge and Summarize Excel Files

1. Background

  Do you often need to merge Excel worksheets with same headers for further analysis? If the answer is yes, then I figured that you find it an annoyance to do the combination, particularly when there are many worksheets or files. Even Excel VBA often fails if there is a huge amount of data. In this case, a professional stand-alone data computing tool will be helpful. esProc is one of its kind, an outstanding one. There will be no need to write complex and inefficient VBA code, but a few lines of SPL (abbreviation of Structured Process Language with innovative syntax that esProc uses) code will suffice instead. With esProc at hand, merging Excel worksheets will no longer be a nightmare.

2. Basic merges

A.Merge multiple sheets of one Excel file

  Below is an Excel file containing sales data. There are 3 worksheets of same structure in the file.

january_2013:

Customer ID Customer Name Invoice Number Sale Amount Purchase Date
1234 John Smith 100-0002 $1,200.00 2013/1/1
2345 Mary Harrison 100-0003 $1,425.00 2013/1/6
3456 Lucy Gomez 100-0004 $1,390.00 2013/1/11
4567 Rupert Jones 100-0005 $1,257.00 2013/1/18
5678 Jenny Walters 100-0006 $1,725.00 2013/1/24
6789 Samantha Donaldson 100-0007 $1,995.00 2013/1/31

february_2013:

Customer ID Customer Name Invoice Number Sale Amount Purchase Date
9876 Daniel Farber 100-0008 $1,115.00 2013/2/2
8765 Laney Stone 100-0009 $1,367.00 2013/2/8
7654 Roger Lipney 100-0010 $2,135.00 2013/2/15
6543 Thomas Haines 100-0011 $1,346.00 2013/2/17
5432 Anushka Vaz 100-0012 $1,560.00 2013/2/21
4321 Harriet Cooper 100-0013 $1,852.00 2013/2/25

march_2013:

Customer ID Customer Name Invoice Number Sale Amount Purchase Date
6634 Poop Smith 100-0014 $1,350.00 2013/3/4
8765 Tony Song 100-0015 $1,167.00 2013/3/8
2345 Mary Harrison 100-0016 $1,789.00 2013/3/17
6543 Rachel Paz 100-0017 $2,042.00 2013/3/22
3456 Lucy Gomez 100-0018 $1,511.00 2013/3/28
4321 Susan Wallace 100-0019 $2,280.00 2013/3/30


  The merging requires getting two fields, Customer Name & Sales Amount, from each worksheet. Below is the final merging result:

Customer Name Sale Amount
John Smith 1200
Mary Harrison 1425
Lucy Gomez 1390
Rupert Jones 1257
Jenny Walters 1725
....... ........
Susan Wallace 2280


esProc SPL script:

A
1 =file("D:/sales_2013.xlsx").xlsopen()
2 =A1.conj(A1.xlsimport@t('Customer Name','Sale Amount';~.stname))
3 >file("D:/result_2013.xlsx"). xlsexport@t(A2;"merge_sheets")

Explanation:
  A1: Open the Excel file and generate a sequence of 3 worksheets.
  A2: conj() function traverses the member worksheets in A1’s sequence to import the specified fields ‘Customer Name’ and ‘Sale Amount’ and merges them. xlsimport() function imports the fields, where a semicolon follows the last field and parameter ~.stname represents the current worksheet. @t option specifies that the first row in each worksheet will be the field names. The function is nested within conj() function’s loop traversal to import data from every worksheet.
  A3: Save A2’s table sequence as a new worksheet in the original Excel file with the name “merge_sheets”. @t option is used to make the first row the column headers.

  Only three lines of code, the script is short, concise yet clear and easy to understand.

B.Merge multiple sheets from different Excel files

  Here are several Excel files in which every worksheet containing a year’s sales data has the same structure as the worksheets in the previous instance:
001png

esProc SPL script:

A B
1 for directory@p("d:/excel/*.xlsx") =file(A1).xlsopen()
2 =B1.conj(B1.xlsimport@t('Customer Name','Sale Amount','Purchase Date';~.stname))
3 =@|B2
4 > file("d:/result.xlsx"). xlsexport@t(B3;"merge_data")

The desired merging effect:

Customer Name Sale Amount Purchase Date
John Smith 1200 2013-01-01
Mary Harrison 1425 2013-01-06
Lucy Gomez 1390 2013-01-11
Rupert Jones 1257 2013-01-18
...... ...... ......
Thomas Haines 1346 2013-02-17


Explanation:
  A1: The for loop traverses every Excel file under the given directory to perform a series of operations over every worksheet in B1~B3.
  B1: Open an Excel file and generate a sequence.
  B2: Import the specified fields, ‘Customer Name’, ‘Sale Amount’ and ‘Purchase Date’, from every worksheet of the current file and merge them. This is similar to the operations in A2 in the previous instance.
  B3: Combine B2’s table sequence with the current value of B3.
  A4: Save B3’s table sequence as a worksheet, named merge_data, in file result.xlsx.

  The program achieves multiple Excel file merging with only two rounds loop. The outer loop traverses each of the Excel files under the given directory while the inner loop B1.conj() function merges data from every worksheet in an Excel file.

C.File merging with big result set

  Both A2 in the first instance and B3 in the second instance receive all merged data in the memory and then write it out at once. But a big merged result set can use up too much memory space or the available memory space won’t be enough to hold the data. In that case, stream-style appending can be used to generate a big file.

esProc SPL script:

A B
1 =file("D:/out.xlsx")
2 for directory@p("d:/excel/*.xlsx") =file(A2).xlsopen()
3 =if(A1.exists(),B2.xlsimport@t(),B2.xlsimport())
4 >A1.xlsexport@s(B3;"merger")

The desired merging effect:

Customer ID Customer Name Invoice Number Sale Amount Purchase Date
1234 John Smith 100-0002 1200 2013-01-01
2345 Mary Harrison 100-0003 1425 2013-01-06
3456 Lucy Gomez 100-0004 1390 2013-01-11
4567 Rupert Jones 100-0005 1257 2013-01-18
...... ...... ...... ...... ......
6789 Thomas Haines 100-0002 1346 2013-02-17


Explanation:
  A1: Open a specified file into which data will be output.
  A2: Traverse all to-be-merged Excel files under the given directory.
  B2: Open a to-be-merged Excel file.
  B3: If the file into which data will be output exists, import all rows, including the column headers, of a worksheet; if the file exists, use @t option to make the first row the column headers and import data from the second row.
  B4: Import data in a stream style in B3 and append it to the worksheet named merger in A1’s specified file.

  The stream-style importing and appending is suited to combine many small excel files into a big one.

3. Grouping & aggregation

  We continue to use the previous Excel file containing the sales data maker further analysis.

A. Group data by one or more fields

  Here’s an eProc SPL script for grouping data by one or multiple fields:

A
1 =file("D:/sales_2013.xlsx").xlsopen()
2 =A1.conj(A1.xlsimport@t(;~.stname))
3 =A2.groups('Customer ID';sum('Sale Amount'):Total,avg('Sale Amount'):Average)
4 =A2.groups('Customer ID','Purchase Date';sum('Sale Amount'):Total)


A3’s grouping result:

Customer ID Total Average
1234 2550 1275.0
2345 3214 1607.0
3456 2901 1450.5
4321 4132 2066.0
…… …… ……
4567 1257 1257.0


A4’s grouping result:

Customer ID Purchase Date Total
1234 2013-01-01 1200
1234 2013-03-04 1350
2345 2013-01-11 1425
2345 2013-03-17 1789
…… …… ……
9876 2013-02-02 1115


Explanation:
  A1: Open the specified Excel file.
  A2: Import all data from the worksheets in the file and perform merging.
  A3: Group the merged data by ‘Customer ID’ and calculate total and average for each group.
  A4: Group the merged data by ‘Customer ID’ and ‘Purchase Date’, and calculate total for each group.

B. Group data in a certain order

  esProc supports comparing neighboring records during grouping and aggregation. If the data is already ordered, then a re-sorting will be unnecessary. Now suppose the original data is ordered by date, but we need to summarize data by month.

esProc SPL script:

A B
1 for directory@p("d:/excel/*.xlsx") =file(A1).xlsopen()
2 =B1.conj(B1.xlsimport@t(;~.stname))
3 =@|B2
4 =B3.derive(year('Purchase Date'):Year,month('Purchase Date'):Month)
5 =A4.groups (month('Purchase Date'):Month;sum('Sale Amount'):Total,avg('Sale Amount'):Average)
6 =A4.groups@o (month('Purchase Date'):Month;sum('Sale Amount'):Total,avg('Sale Amount'):Average)


A5’s grouping result:

Month Total Average
1 272414 15134.111111111111
2 168038 9335.444444444445
3 357693 19871.833333333332


A6’s grouping result:

Month Total Average
1 8992 1498.6666666666667
2 9375 1562.5
3 10139 1689.8333333333333
1 260221 43370.166666666664
2 103656 17276.0
3 101509 16918.166666666668
1 3201 533.5
2 55007 9167.833333333334
3 246045 41007.5


Explanation:
  A1~B3: Merge all worksheets in the Excel files of same structure under the specified directory.
  A4: Generate a new table sequence base on B3’s table sequence by splitting the Purchase Date field into a new Year field and a new Month field.
  A5: groups() function groups data by month and calculates total and average for each group.
  A6: groups@o groups data in each year by month, calculates total and average for each group, and, with @o present, merges the summarized results.

  A4 contains detailed sales records; A5 summarizes data only by month, ignoring the year; A6 summarizes data by both year and month. The three cells shows summarized data of different levels.

C. Group data by segment

  This type of grouping divides data into multiple segments according to specified conditions and summarizes each group of data.

esProc SPL script:

A B
1 for directory@p("d:/excel/*.xlsx") =file(A1).xlsopen()
2 =B1.conj(B1.xlsimport@t(;~.stname))
3 =@|B2
4 =B3.groups(if ('Sale Amount'<1000,"1::<1000",
if ('Sale Amount'<1500,"2::1000~~1500",
if ('Sale Amount'<2000,"3::1500~~2000",
if ('Sale Amount'<2500,"4::2000~~2500",
"5::>=2500")))):Segment; count(1):Number,sum('Sale Amount'):Total)

Grouping result:

Segment Number Total
1::<1000 22 8280
2::1000~~1500 9 11617
3::1500~~2000 6 10432
4::2000~~2500 4 8810
5::>=2500 13 759006


Explanation:
  About the code block A1~B3, refer to the explanations of previous instances.
  A4: It defines 5 intervals for the value of ‘Sale Amount’ field and calculates number and total for within each range.

  But the above script is still not convenient-to-use enough. When we want to change the way of dividing the data, we need to modify the parameter expressions in groups() function. This is a little complicated. Another function, pseg(), helps to achieve an easier modification. Here’s the SPL code:

A B
1 [0,1000,1500,2000,2500]
2 for directory@p("d:/excel/*.xlsx") =file(A1).xlsopen()
3 =B1.conj(B1.xlsimport@t(;~.stname))
4 =@|B2
5 =B4.groups(A1.pseg(~.'Sale Amount'):Segment; count(1):Number,sum('Sale Amount'):Total)

  We can also group data by multiple fields according to a set of conditions and then summarize each group. For example, we divide the scores of subjects into 5 grades – Excellent, Good, Satisfactory, Poor, Passing, and summarize each group. For more uses of groups() function, refer to esProc Function Reference.

D. Group big data

  In the above instances, the Excel file(s) is/are fairly small to be imported into the memory at a time. If the total data amount is huge, trying to retrieve all data into the memory in either esProc or VBA could fail. An alternative is the stream retrieval that reads a part of data into the memory and summarizes it at one time.

esProc SPL script:

A B
1 =file("d:/tdata.xlsx").xlsopen@r()
2 for A1.count() =A1.xlsimport@ct(;A1(A2). stname)
3 =@|B2
4 = B3.conjx() =A4.groups('Customer ID';sum('Sale Amount'):SaleTotal)
5 >file(“d:/out.xlsx”).exportxls@bt(B4;"Customer&Sales")


The result of filtering and then grouping data:

Customer ID SaleTotal
1234 107721792
2345 139041639
3456 137985543
4321 96170742
... ...
9876 37590417


Explanation:
  A1: Use @r option to open an Excel file in a stream style.
  A2: Traverse every worksheet in the Excel file.
  B2: Use @c option to import data with cursor.
  B3: Union B2’s cursor into B3.
  A4: Union members of B3’s cursor sequence into a new cursor.
  B4: Group A4’s sequence by Customer ID and calculate Sale Amount for each group.
  A5: Export and save B4’s result set into an Excel file.

  Each time, the stream retrieval reads a part of data with cursor and groups and summarizes it.

4.Deduplication

  Duplicate data affects the analysis results. As duplication is common in real-world cases, let’s look at how to remove duplicates in esProc SPL.

A. Via primary key

  Set ‘Invoice Number’ as the primary key of sales_2013 to remove duplicates according to it.

A
1 =file("d:/sales_2013.xlsx").xlsopen()
2 =A1.conj(A1.xlsimport@t('Customer Name', 'Invoice Number', 'Sale Amount';~. stname))
3 =A2.group@1('Invoice Number')
4 >file(“d:/out.xlsx”). xlsexport@t(A3;"result")


Concatenated deduplicated data:

Customer Name Invoice Number Sale Amount
John Smith 100-0002 1200
Mary Harrison 100-0003 1425
Lucy Gomez 100-0004 1390
Rupert Jones 100-0005 1257
Jenny Walters 100-0006 1725
…… …… ……
Susan Wallace 100-0019 2280


Explanation:
  A1: Open a certain Excel file.
  A2: Import specified columns from every worksheet.
  A3: Group A2’s table sequence by primary key ‘Invoice Number’ and deduplicate each group.
  A4: Export and save A3’s result set into an Excel file.

  Data is distinct in each worksheet, but probably not once the worksheets are concatenated. Primary key is one of the ways of deduplicating data.

B. Via a certain field

  Deduplicate data in sales_2013 according to a certain field to look through employee records of different names.

A
1 =file("d:/sales_2013.xlsx").xlsopen()
2 =A1.conj(A1.xlsimport@t('Customer ID', 'Customer Name';~. stname))
3 =A2.id('Customer Name')
4 =A2.group@1('Customer Name')
5 >file("d:/out.xlsx"). xlsexport@t(A4;"result")

Explanation:
  A1: Open a certain Excel file.
  A2: Import specified columns from every worksheet.
  A3: Get a sequence of distinct names from A2’s table sequence.
  A4: Get a table sequence of distinct names from A2’s table sequence.
  A5: Export and save data of A4’s table sequence into an Excel file, with the first row as the column headers.

A3’s deduplication result:

Member
Anushka Vaz
Daniel Farber
Harriet Cooper
……
Tony Song


A4’s deduplication result:

Customer ID Customer Name
5432 Anushka Vaz
9876 Daniel Farber
4321 Harriet Cooper
…… ……
8765 Tony Song

C. Via multiple fields

  Sometimes we need several other fields, instead of the primary key, to determine whether records are duplicates or not.

A
1 =file("d:/sales_2013.xlsx").importxls@t()
2 =file("d:/sales_2014.xlsx").importxls@t()
3 =[A1,A2].merge('Customer ID', 'Purchase Date')
4 =A3.group@1('Customer ID', 'Purchase Date')
5 >file("d:/out.xlsx"). xlsexport@t(A4;"result")


Explanation:
  A1: Import a specified Excel file.
  A2: Import another specified Excel file.
  A3: Merge A1’s table sequence and A2’s table sequence by fields ‘Customer ID’ and ‘Purchase Date’ and return a new table sequence.
  A4: Group and deduplicate A3’s table sequence by ‘Customer ID’ and ‘Purchase Date’.
  A5: Export and save 43’s result set into an Excel file.
  We can perform the merge and grouping and deduplication by more fields as needed.

D. Deduplicate records

  Records in each file are distinct, but the concatenated file could contain duplicate records.

A B
1 =file("d:/sales_2013.xlsx").importxls@t() =A1.group@1('Invoice Number')
2 =file("d:/sales_2014.xlsx").importxls@t() =A2.group@1('Invoice Number')
3 =[B1,B2].merge@u() =A3.count()


Explanation:
  A1: Import a specified Excel file.
  B1: Deduplicate A1’s table sequence by ‘Invoice Number’.
  A2&B2: Same as A1&B1.
  A3: Merge B1’s table sequence and B2’s table sequence, deduplicate data, and return a table sequence. @u option is used to merge multiple table sequences in order, remove duplicates, and generate a new table sequence.
  B3: Count the records in the merged table sequence. merge@u() function merges multiple ordered, deduplicated table sequences.

  In the above, we explain how to handle merging, grouping and summarization, and deduplication over same-structure Excel files in esProc SPL. For Excel files of different structures, we just need to read to-be-merged fields as a sequence object and then process it in same ways. It is similar to handle other types of text files.

5. Appendix

slaes.rar