On not a few occasions, you want to use SQL to query data in an Excel file in a Java program to compute.

As a database query language, SQL requires data to be queried first be stored in the database. So you create a temporary table in a database, read the Excel data and import it in, write a SQL query to execute and delete the temporary table when the job is done. That sounds simple, but each action requires code, maybe a lot of it. Also, Excel file formats require different query syntax and a program is probably useless next time you encounter an Excel data query.

esProc SPL (Structured Process Language) provides an all-in-one solution to perform the query in a very simple way. Only two lines of code are needed.

For example, here’s info.xlsx file:

To get male employees data, SPL produces the following script:

A2 returns the desired data. After that you can sort, group (See SPL Grouping) and summarize it as you like.

It’s also convenient to handle various Excel data query scenarios with SPL:

1. Query Excel files of all formats, such as free format spreadsheet, crosstab, main table & subtable.

2. Export and append Excel data to another Excel file to output an Excel file with a sophisticated format.

3. Read and write an Excel file containing a huge volume of data.

4. Combine and split an Excel file.

More information can be found in Parsing and Exporting Excel Data in SPL.

SPL is integration-friendly with a Java program. Read How to Call an SPL Script in Java to learn more.

To begin to work with esProc, see Getting Started with esProc.