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:

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.

esProc is the file processor that can conveniently handle data loading, database export and mixed computations over various types of files, including TXT, Excel, XML, JSON, CSV and INI. The desktop tool is ready to use, simple to configure and convenient to debug. It allows setting a breakpoint and step-by-step execution during which you can view the result of each step. Based on powerful yet simple syntax that agrees with human way of thinking, esProc is more convenient to use compared with high-level languages. See Data File Processor.

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.