It’s easy and convenient to query database data with SQL. Yet it’s not so convenient to install a database and then load a file to the database. Here I’ll introduce you a simple and fast way to directly query and Excel or text file using SQL.

Below is Excel file orders.xlsx:

To query the total of the sales amounts in every state, we just write the following SQL query:

select state, sum(amount) as sum_amount from d:/excel/orders.xlsx group by state

1. Run esProc

First you can download esProc DSK edition and a free license at Raqsoft website. You will be prompted to load the license file when you run esProc for the first time.

2. Create a new script file to enter script in it

$select state, sum(amount) as sum_amount from d:/excel/orders.xlsx group by state

Be aware that the query should be preceded by a dollar sign ($), which tells esProc that this is a SQL query. The default programming language esProc uses is SPL.

3. Press F9 to execute SQL

Click cell A1 to view the query result on the right-hand value viewing section.

4 Export query result to file

Then you can export the SQL query result to a new Excel file or text file. The SQL is as follows:

select state, sum(amount) as sum_amount into d:/excel/group.xlsx from d:/excel/orders.xlsx group by state

5. Command line execution

You can also execute the SQL query from the command line.

Under Windows, for instance, type “cmd” to open the Command Prompt, and then enter [esProc installation directory]\bin directory to perform operations shown in the following picture:

6. More about esProc

esProc supports most of the syntax in SQL92 standards, including JOIN, subqueries and even WITH clause. Read SQL File Query Examples to learn more about using SQL to query files directly.