Though Excel supplies a series of built-in query functionalities, such as FILTER, pivot table, SUBTOTAL, etc., these interfaces are not simple and flexible to operate. For users who are familiar with SQL, it’s more convenient to make queries in SQL.

Below is an orders table:

Task: Find the number of orders for each sales person (column C) in the year of 2009, and paste the result to column G and column H (as shown below):

It’s quite simple if you do this in SQL. Just one line is enough: select sellerid,count(1) orderNum from table_name where year(OrderDate)=2009 group by sellerid

Now let’s look at how to query and compute the above data in Excel with SQL:

1. Visit raqsoft website to download esProc(DSK edition) and install. 

Make sure you remember to download the license file (the free license is enough). Once esProc is installed and executed, the software will prompt you to load the license file.

2. Open the to-be-computed file in Excel, like the above orders table.

3. Select the area of A2:E12, and press Ctrl+C to paste it to the clipboard.

4. Open esProc designer, select cell A1, and press Ctrl+V to paste data from the clipboard to A1. Make sure that the cursor is placed above A1 in its editing status during the pasting.

5. Enter the SQL query ($select sellerid,count(1) orderNum from {A1.import@t()} where year(OrderDate)=2009 group by sellerid )in cell A2. Be aware that the query is preceded by a dollar sign ($) according to esProc syntax. The content of from is {A1.import@t()}, which means converting A1’s strings into tabular data for SQL execution and where @t option enables reading the first line as column headers.

6. Press F9 to execute the SQL query. Then you can click A2 to view the result in the value viewing section on the right.

7. Press Shift (for not leaving the column headers behind) and click the “Copy data” button on the right-hand value viewing section to paste A2’s result to clipboard.

8. Select cell G2 in Excel and press Ctrl+V to paste the result in.

OK, that’s the whole process.

Here’s another example of join operation. There are orders sheet and employee sheet in an Excel file. The task is to join the two sheets together.

The first two steps are same.

3. Select the column headers and detailed data from orders sheet, and press Ctrl+C to paste data to the clipboard and then Ctrl+V to paste it to cell A1 on esProc designer.

4. In the same way, copy and paste data of employee sheet to cell B1 on esProc designer.

5. Execute SQL query in cell A2 on esProc designer. The SQL is: $select o.OrderID, o.OrderDate,o.Amount, e.Name, e.Dept
from {A1.import@t()} o left join {B1.import@t()} e on o.sellerID=e.empID

The rest of the steps are same as those in the previous example.

esProc supports most of the syntax and functions in SQL92 standards, including nested subqueries and even WITH clause.