Part of the data in the orders table orders.xlsx is shown in the figure below, in which the first row is the column header, and the data start from the second row with one record per row.

1. Split by category

Classify the data in the file, save each type of data separately as a file, and name the file with the classification name.

Example: Now, we need to classify the order data by Area and save the data of each area as an Excel file separately.

The esProc SPL script is as follows:

A1 Read the data in orders.xlsx 

B1 Group the data by Area

A2 Loop through each Area group

B2 Use the Area name as the file name and write the grouped data in A2 to the file

2. Split by number of rows

Split a relatively large row-based Excel file into small files according to the specified number of rows.

Example: Split the data in the order table into small files with 10,000 rows each.

The esProc SPL script is as follows:

A1 Read the data in orders.xlsx

A2 Loop through the data in A1, and fetch 10,000 records each time

B2 Use orders plus the loop number as the file name and write the data in A2 to the file