How to merge multiple Excel files? How to merge Excel with the same type? What if there is duplicate data during the merge? How to summarize data from multiple files? I believe many colleagues will encounter similar problems in their work, and a large number of them will be at a loss when facing these problems. There are too many tables, copying and pasting is not advisable, PQ is not enough, and Python is too cumbersome. Is there a simpler and more automated way? Today, we will introduce a sharp tool SPL that can handle various complex Excel scenarios, and help you solve Excel problems in minutes.

esProc SPL is a tool that excels in structured data processing, it has rich functions, and many complex Excel tasks can be easily solved in SPL. The key is that although it is a programming language, it is very simple to use. Many problems that require a lot of code to solve in Python or SQL can be solved with just a few statements in SPL, even if you don’t have a programming basis, you can quickly get started. Moreover, it also comes with a large number of sample codes, and often you can just copy the sample code to solve your problems.

Without further ado, let’s go straight to the code examples.

Example 1: Excel vertical merge with the same column name and number

Firstly, let’s take a simple example. There are two Excel tables with the same column names and numbers, as shown in the following figure

Before merging:

Fruits.xlsx

and

Meats.xlsx

It is now necessary to merge them vertically, as shown in the figure

After merging:

For tables with completely consistent fields, SPL can solve the problem by directly using A|B|C

Implementation code:

     A
1    =file("Fruits.xlsx").xlsimport@t()
2    =file("Meats.xlsx").xlsimport@t()
3    =A1|A2
4    =file("Foods.xlsx").xlsexport@t(A3)

A1 and A2 represent importing Excel files

A3 A1|A2 represents merging two tables vertically

A4 indicates that the merged result A3 is saved as an xlsx file to the specified path.

The programming method of SPL is very friendly, just like writing formulas in Excel, where table names A1, A2… can be used to reference the data in the table for calculation.

Merging two tables is easy, so how about N tables?

It’s also very simple.

Example 2: Vertical merge of N tables with same columns

First, place the files to be merged in the same path, such as placing all 50 files in the Fruits folder.

Then run the code:

     A
1    =directory@p("D:/Fruits/*.xlsx")
2    =A1.conj(T(~))

A1 directory is a function here, meaning to list all .xlsx file names and directories in the Fruits folder

A2 represents looping through the files in the A1 path and then merging them.

The T()function is equivalent to file().xlsimport@t()in Example 1, and is its upgrade function, which represents the opening of files in the specified path, and the T() function can also open files in various formats such as txt, CSV, xls, etc.

A.()is a loop function, where A1.(T(~)) represents looping through each file in A1 path and opening the file using the T() function, and the ~ symbol represents the object of each loop.

After opening all the files in a loop, we also need to merge them, so we add a conj() function, meaning conjunction.

Therefore, the code A1.conj(T(~)) in A2 represents opening 50 files in A1 and merging them into one.

As long as Excel files with the same fields are merged, no matter how many, these two lines of code can be applied.

Then, add another requirement, we not only need to merge, but also want to know which file each piece of data in the merged file comes from.

Of course, it’s also possible.

Example 3: Merge multiple files vertically, with file names converted to column values

We would like to add a new column named “Category” after the merge, indicating which category (file) each merged product comes from.

After merging:

Just make modifications to the code in Example 2

     A
1    =directory@p("D:/Fruits/*.xlsx")
2    =A1.conj((fn=filename@n(~),T(~).derive(fn:Category)))

filename@n() represents obtaining the file names in the A1 path, such as Fruits, Meats

derive()means adding a derived variable “Category” to each table opened by the T() function, with the value of fn, which is the name of each Excel file.

conj() is to merge all tables after adding the derived variable “Category”.

A2 returns the following results

Implemented merging multiple files and converting file names to column values.

Two lines of code can merge multiple files in minutes, isn’t it very simple and efficient!

Of course, SPL can also implement more complex Excel file operations, such as summarizing data during merging, merging files with different column names, horizontal merging, one-to-many merging, inserting summary sheet, and so on. If you need it, you can refer to this book Desktop and Excel Data Processing Cases. Ninety per cent of Excel problems in the workplace can be found in this book. The code in the book can basically be copied and used with slight modifications.

In addition, the installation of SPL is also very simple, and there is no need to configure the environment like languages such as Python. After downloading, double-click to install on the desktop.

Download address: esProc Desktop Download