* Combine Excel Files into One

Many times we need to combine Excel files of same format under a directory into one. How can we do?

You can write a program. Though many programming languages provides API to read data from Excel files, you still need to write code to open each file, traverse cells to read desired data, input all data into memory and store it in one data set object, and finally, write data to a new Excel file. This needs a lot of coding work. When the Excel format changes, the program becomes useless and a new round of coding job begins.

It would have been much simpler if you had used esProc SPL to do this.

Below is SPL script:


A

B

C

1

>dir="E:/work/excel/"

=file(dir+"merged.xlsx")


2

=directory(dir+"*.xlsx")



3

for A2

=file(dir+A3).xlsimport@t()


4


If #A3==1

=B1.xlsexport@t(B3)

5


else

=B1.xlsexport(B3)

The SPL solution also applies in combining worksheets in a same Excel file and performing the group and aggregate operations after it. You can found more examples in An Easy Way to Merge and Summarize Excel Files.

It’s also convenient to handle various Excel data query scenarios with SPL:

1. Query Excel files of all formats, such as free format spreadsheet, crosstab, main table & subtable.

2. Export and append Excel data to another Excel file to output an Excel file with a sophisticated format.

3. Read and write an Excel file containing a huge volume of data.

4. Combine and split an Excel file.

More information can be found in Parsing and Exporting Excel Data in SPL.

SPL is integration-friendly with a Java program. Read How to Call an SPL Script in Java to learn more.

To begin to work with esProc, see Getting Started with esProc.