What is esProc Desktop?

A toolkit intended to analyze and process desktop data files
SPL WIN An interactive interface for computing file data(txt\csv\xls)
SPL XLL An add-in for powering up Excel computing ability
SPL IDE Full-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
Enter formula in I1:
=spl("=E(?1).group(Dept).conj(~.top(-3;Salary))",A1:G499)
Note: The group() function performs grouping; ~.top expression gets TopN in a group; -3 means getting top3 backwards.

SPL WIN

Interactive file processing interface


Real SQL on File

File format support

  • Text:txt, csv
  • Excel:all versions of xls and xlsx
  • Special format:Json, XML

SQL92 standards

  • where, group by, join and union
  • As, between, like, Top-N and limit n
  • from clause, with clause, nested query, case when

High-efficiency

  • Programming skills are nonessential
  • Do not require explicit or inexplicit data loading

SPL XLL

An add-in for powering up Excel computing ability


Versatile formulas

Power up Excel functions

  • Enable Vlookup to return multiple matches
  • Enable datedif to calculate quarter/week difference between two dates
  • Enable filter to perform fuzzy queries

Enable computations on grouped subsets

  • Get ranking of data in each group
  • Filter data in each group by an aggregate value
  • Combine rows in each group into one using a specified separator

Simplify computations between sheets

  • Cross join two sheets
  • Full join sheets & calculate difference of sheets

SPL IDE

Specialized IDE for Advanced Users


All-around programming ability

All-around programming ability

Powerful computing capability

  • Filter, search, distinct, sort, join, group & aggregation
  • Group by segment, enumerated grouping, order-based grouping, conditional grouping
  • Transposition, merge, complement, align with nulls, reverse grouping
  • relative position, cross-row reference, moving average

A wealth of library functions

  • Mathematical functions: Round-up, sin & cos, combination, factorial, gcd, matrix
  • Date functions:Time series, workday, quarter, beginning of a month, end of a month, number of days in a week
  • String functions:Position of a substring, splitting away date/numbers/words

Support of diverse/multiple data sources

  • xls, csv, txt, json, xml
  • MySQL, Access, Oracle, …

Resource



Course Business Programming with SPL

With simple syntax and moderate learning curve, esProc enables business people who are only familiar with Excel and have never writen a single line of code to experience an automatic and efficient data processing through programming.