A toolkit intended to analyze and process desktop data files
SPL WINAn interactive interface for computing file data(txt\csv\xls)
SPL XLLAn add-in for powering up Excel computing ability
SPL IDEFull-featured data computing capacity and IDE for advanced users
When & where esProc Desktop helps?
Want to perform queries on files in SQL but installing a database isn’t simple
Merging & splitting lots of files are hard, and manual handling is tedious and time-consuming
Excel does not support certain necessary formulas, and VBA coding is complicated
Excel cannot handle computations on grouped sheet, and manual edits involve heavy workload
Want to handle repeated operations through coding, but Python is too difficult
......
How esProc Desktop helps?
Query Orders.csv in SQL
Complicated preparation, involving installing database, creating tables and importing data, etc.
Write SQL query directly
The xls files in a certain directory are named after dates. Each file stores sale records of a date. And we are trying to find files of January in the year 2020, merge them and calculate the yearly sale amount for each customer.
Usually programming is needed to achieve batch file processing, but it has a high technical threshold
Write SQL directly and execute it
select Client,year(OrderDate) as y,sum(Amount) as Total
from d:/data/202001*.xlsx
group by Client,year(OrderDate)
Note: SPL SQL supports filename containing wildcard characters, so it can automatically merge multiple files.
Split value of cell A1 in an Excel file to multiple cells. The expected result is as shown in C1:F1:
Excel does not support splitting a string according to separators using a formula
Just enter the formula in cell C1:
=spl("=?.split(""->"")",A1)
Note: The result will automatically fill each cell in D1-F1 after the formula is entered in C1
Find employees in each department whose salaries rank in top 3
Excel does not support computations on a grouped sheet, and heavy manual work is involved