Directly Query Excel/Text Files Using SQL

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.




Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

2 Examples That Reveal The Importance of Proper SQL Formatting

Everything you should know about Risk Identification on Software projects

Airtable > Webflow Client Dashboard — No Code

GCP Client Libraries Auth — Service Accounts & GKE Secrets {Basic Stuff}

Step-By-Step guide to Setup GPU with TensorFlow on windows laptop.

Master to Master Replication using MySQL between RDS and EC2

How to extract online data using Python

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
chuxin huo

chuxin huo


More from Medium

Eight steps to deploy A CI/CD dockerized .NET App To Azure Web Apps with Azure Container registry.

Multi-version Odoo installation and configuration in PyCharm (odoo-10 and odoo-15)

Installing Postgresql In a Ubuntu Server

How to use C++Template in Python?