1.3.1 Parse file name

The function filename can parse out different parts of the file name:

A1: File name with extension: test.splx
A2: Extension: splx
A3: File name without extension: test
A4: Path: D://file

Having known every part of the filename, you can use the concat function to piece together the full path, for example:

A1: Full path: D://file/test.spx

1.3.2 Traverse the files

There are many Excel files under a certain directory, and the first Sheet of these files stores the order data and has the same structure. Now we want to concatenate these orders into a new Excel.

SPL script:

A1: Search for all file names whose extension is xlsx in the directory. @p means returning the full path.

A2: Loop through the file names to read Excel, and then concatenate the data.

The function directory has more functions, such as using the @s option to search for subdirectories recursively, using the @d option to list subdirectories, using @r to delete directories, and using @m to create directories.

1.3.3 System directory

In the previous example, we use the full path to access the data file. If the main path in the system directory is configured, it can be used as the root directory. In this case, we can use the relative path to access the data file. See the figure below for the specific configuration interface:

For example, when the main path is not configured, the script is:

file("d:/data/p/orders.xlsx").xlsimport()

When configuring main path=d:\data, the script can be written as:

file("p/orders.xlsx").xlsimport()

If the main path is not configured, and a direct relative path is used, the actual main path is the directory where esProc is started. When esProc is started directly (or via shortcut), the directory is [esProc installation directory\bin]. When double-clicking the .splx file to start esProc indirectly, the directory is the directory where the script file is located. Using the following script can obtain the current main path:

filename@p("")

In addition to the main path, esProc has other important system directories.

temp: It’s the directory where the calculation engine stores temporary files. If it is not set, the operating system temporary directory will be used by default.

searching path: It’s the root directory of the script file, including the main path. Multiple directories can be set, and the directories are separated by semicolon.

esProc Desktop and Excel Processing
1.2 Excel file
1.4 General data table operations