I believe many friends have encountered situations where it is necessary to split a large Excel table into multiple small tables. Obviously, copying and pasting is not advisable, and VBA is too complex. Here is a simpler and more user-friendly method to use esProc SPL, which can help you solve problems in minutes.

Let’s directly look at some examples.

1. Split by number of rows

Some data of the order table file Orders.xls is shown in the following figure. The first row is the column header, and starting from the second row is the data, with one piece of data per row.

Now we need to split this Excel file into small files with the specified number of rows, with every 300 rows into a small file.

Copy the following statements to esProc SPL:

      A
1    =T@c("Orders.xlsx")
2    for A1,300             =T("Orders"/#A2/".xlsx",A2)

A1 reads the Orders.xlsx file, with the @c option indicating that the file data is read as cursor, which will be read in batches.

A2 loops A1 data, getting 300 rows of data each time.

B2 uses Orders followed by a loop number as the file name, and writes 300 pieces of data from A2 to the file.

Split into multiple files:

2. Split by data group

The order data from the previous example is divided into groups by Shippers, with each group having a separate sheet named after the group name. The split result is as follows:

The code is as follows:

      A                         B
1    =T("orders.xlsx")       =A1.group(Shippers)
2    forB1                   =file("Ordersm.xlsx").xlsex
                             port@kt(A2;A2.Shippers)

A1 reads data from orders.xlsx.

B1 groups by Shippers.

A2 loops through each Shippers group.

B2 uses the Shippers name as the sheet name and writes the grouped data from A2 into the sheet.

Of course, you can also split the grouped content into multiple files, just change the code in B2 to “=T(A2.Shippers+“.xlsx”,A2)”. Meaning: write the grouped data in A2 into different files using the Shippers name as the file name.

Generate multiple files.

3. Split by segments according to conditions

The order detail data file OrderDetailExtended.xlsx is as follows:

Divide into three sections based on the values in the ExtendedPrice column, using <500, from 500 to 2000, and >2000, and save them into three Excel files. The split result is as follows:

lt500.xlsx:

Mt2000.xlsx:

500-2000.xlsx:

Implementation code:

      A                                     B
1    =T("OrderDetailsExtended.xlsx")    
2    =A1.group(if(ExtendedPrice<500:"lt
     500.xlsx",ExtendedPrice>2000:"mt20
     00.xlsx";"500-2000.xlsx"):fileName;
     ~:data)    
3    for A2                                =T(A3.file
                                           Name,A3.data)

A1: Read data from the OrderDetailsExtended.xlsx file.

A2: Group A1 according to the value of the ExtendedPrice column, less than 500, greater than 2000, and between 500 and 2000, into three groups, and name the corresponding groups with file names.

A3: Loop according to A2.

B3: Write the grouped data in each row into the corresponding file.

Split completed!!!

Of course, esProc SPL also has many cool features. If you need it, you can refer to this book, Desktop and Excel Data Processing Cases. 90% of Excel problems in the workplace can be solved in this book. The code in the book is basically copied and can be 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, just double-click to install on the desktop.

Download address: esProc Desktop Download. It's always FREE & EASY to download and apply.