Query Files

Goal

Taking a quick look inside a data file usually means: open a database client, create a table, import, and only then write your query. Query Files skips all of that. The file you have open in Notepad++ becomes a table called THIS, and you just write SQL against it.

Under the hood runs DuckDB: modern SQL with window functions, QUALIFY, PIVOT and GROUP BY ALL, and fast enough for files with millions of rows. You can also query files on disk directly and join them, including wildcards such as 'logs/*.json'.

There already is a plugin that runs SQL on CSV (CSV Query), but it uses SQLite from 2011 and only handles CSV. Query Files is written in C++, uses a local duckdb.exe, and sends nothing over the internet. Source fully open (MIT license) on GitHub.

What to expect

See it in action

A query on an open CSV file, and the result as the plugin puts it in a tab.

-- this: C:\data\orders.csv
select customer,
       count(*)    as orders,
       sum(amount) as revenue
from this
group by all
order by revenue desc;

┌──────────┬────────┬─────────┐
│ customer │ orders │ revenue │
│ varchar  │ int64  │ double  │
├──────────┼────────┼─────────┤
│ Alpha    │      3 │  394.75 │
│ Beta     │      2 │  122.75 │
│ Gamma    │      1 │    15.0 │
└──────────┴────────┴─────────┘

THIS belongs to the plugin

THIS is a reserved word of Query Files and always read-only. Use it as a table (from this, join this t, describe this, copy this to 'out.parquet'), but not as a name: not as an alias, CTE or table name, and not as the target of insert, update or delete. A column that really is called this is written in double quotes.

Usage

1. Installation

Not yet in Notepad++'s Plugins Admin — manual install:

Download QueryFiles.zip from the latest release and unzip it into the Notepad++ plugin folder (64-bit Notepad++ required):

C:\Program Files\Notepad++\plugins\
  QueryFiles\
    QueryFiles.dll
    help.txt

2. DuckDB

Query Files uses the free duckdb.exe (duckdb.org). Put it on your PATH, next to the plugin, or pick it in Settings. Already using Datamodder File Tools with DuckDB? Then Query Files picks that one up automatically.

3. Activate plugin

Restart Notepad++. The plugin appears automatically under Plugins → Datamodder Query Files.

4. Get started

Open a data file, choose Query this file, and click the ▶ button in the toolbar:

Plugins → Datamodder Query Files → Query this file
Plugins → Datamodder Query Files → Run query   (▶)

Frequently asked questions