Why SQL on a CSV usually means a database first

Querying a CSV with SQL normally means loading it somewhere first: a local database, a notebook, or a command-line tool that reads the file into memory each time. Each needs a setup step, a schema, a connection, an import, before the first question gets an answer, and the file that answers it is no longer the one on screen.

Query the file directly

Open the file and switch to the SQL editor: View ▸ SQL Editor, or ⌘2.
The file is already a table, named after itself (shop_orders), or simply this. Columns keep their real type, decimal-comma amounts included, so amount < -1000 compares numbers, not text.
Run Query is ⌘↩ (Tabulay Pro, included in the 14-day trial). A query that returns the file's own rows keeps the grid editable; an aggregate shows as a read-only result.

Three queries, on Tabulay's own sample files

These come from the sample files Tabulay ships with, tested against them, each on its file's real columns.

Which stock lines are below their reorder point? Returns the file's own rows, from warehouse_inventory.csv: fix a count right in the grid, then ⌘S.

SELECT *
FROM this
WHERE qty_on_hand < reorder_point
ORDER BY reorder_point - qty_on_hand DESC

What's the stock worth in each warehouse, and its share of the total? A window function, OVER (), computes the share alongside the group.

SELECT warehouse,
       sum(qty_on_hand) AS units,
       round(sum(qty_on_hand * unit_cost), 2) AS stock_value,
       round(100 * stock_value / sum(stock_value) OVER (), 1) AS pct_of_total
FROM this
GROUP BY warehouse
ORDER BY stock_value DESC

Which product categories get returned the most? From shop_orders.csv; FILTER counts a subset inside the same aggregate, no subquery needed.

SELECT category,
       count(*) AS lines,
       round(100 * count(*) FILTER (WHERE status = 'returned') / count(*), 1) AS return_pct
FROM this
GROUP BY category
ORDER BY return_pct DESC

Check the plan, export the result

Explain (⌥⌘E) shows the real query plan the engine will run, with timings on request, so a slow query isn't a guess (Tabulay Pro, included in the 14-day trial). Export View as CSV (⇧⌘E) writes out the rows in front of you, whatever got them there: a query's result, or a filtered view.

Free vs Pro, stated plainly

Filter chips answer the everyday version of these questions with clicks, free for everyone, and compile to the same SQL behind the scenes: switch to the editor at any point and pick up exactly where the chips left off. The Examples menu writes ready-made queries from your own file's columns for free; writing your own SQL, running any query, and Explain are Tabulay Pro. Once a file is loaded, the engine can't read or write any other file, load an extension, or change its own settings, and every query runs as a single statement.

Other ways to query a CSV

The command line has its own SQL-over-CSV tools: fast, scriptable, and worth it for a query run the same way every day. There's no grid to check a result against, though, and a typo in a column name is a stack trace, not a suggestion.

A database gives you indexes, joins across many tables, and several people querying at once. Getting a single CSV file into one means a server or an embedded engine to set up, a schema to define, and a reimport every time the source file changes.

Try it

Open a file, press ⌘2, and try one of the three queries above on your own columns. The full SQL feature list is on the tech specs page, and totaling decimal-comma bank exports covers the finance case in more depth.

Get notified at launch

How to run SQL on a CSV file on Mac