SQL Playground: Query a CSV with SQL

What this SQL playground does

The SQL panel runs SELECT queries over a CSV or TSV file. Filter rows, group them, count them, and compute totals, then read the answer as a table. The engine reads one table: the file open in the editor. It supports a practical subset of SQL, not a full database.

The panel only opens for CSV and TSV. A JSON file opens the jq panel instead, so convert JSON to CSV first if you want to query it with SQL. The box above loads your CSV and shows its row and column count; it does not run SQL.

What the SQL subset covers

The engine accepts SELECT and nothing else. A query that starts with UPDATE or DELETE returns an error, and your file is never changed by a query. Here is what the subset covers.

• Clauses: SELECT, DISTINCT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, and OFFSET.
• Aggregates: COUNT, SUM, AVG, MIN, and MAX, including COUNT(DISTINCT column).
• Conditions: =, != or <>, >, >=, <, <=, LIKE, IN, BETWEEN, IS NULL, NOT, AND, OR, and parentheses.
• Arithmetic: +, -, *, /, and %, also inside an aggregate, such as SUM(price * quantity).
• Text functions: UPPER, LOWER, TRIM, LENGTH, SUBSTR, CONCAT, and REPLACE.
• Number functions: ROUND, ABS, CEIL, and FLOOR, plus COALESCE and CASE WHEN.
• A scalar subquery on the right side of a WHERE comparison, such as WHERE salary > (SELECT AVG(salary)).

How to query a CSV with SQL

Use the box to check the file, then continue in the editor where the SQL panel lives. The steps below take you from a pasted file to a query result you can copy or apply.

1. Paste your CSV into the box, or select Try an example. The box accepts up to 512 KB.
2. Check the summary in the result panel, such as 6 rows, 5 columns. A problem shows as an error with a line number.
3. Select Open in full editor. The file opens in the Table view.
4. In the toolbar, select SQL in the Transform group. You can also press Cmd+K (Ctrl+K on Windows and Linux) and run Query / Transform.
5. Type a query. The result shows as a table while you type, and an error shows below the input field.
6. Select Copy to copy the result. To replace the file with it, select Apply as new data and confirm.

Worked example: orders per customer

This file lists six orders. The query counts the orders of each customer, sums their amounts, and sorts the customers by that sum, largest first. The AS keyword names the new columns.

-- orders.csv
order_id,customer,region,status,amount
1001,Acme,West,shipped,120.50
1002,Globex,East,pending,80.00
1003,Acme,West,shipped,45.25
1004,Initech,East,shipped,300.00
1005,Globex,East,shipped,99.99
1006,Initech,West,pending,15.00

-- Query
SELECT customer, COUNT(*) AS orders, SUM(amount) AS total
GROUP BY customer
ORDER BY total DESC

-- Result
customer   orders   total
Initech    2        315
Globex     2        179.99
Acme       2        165.75

Reading the result

GROUP BY customer collapses the six rows to three. Each customer placed two orders. Initech comes first because 300 plus 15 is the largest sum. Without AS, an aggregate column keeps its text, such as COUNT(*) or SUM(amount).

When you apply a result, the CSV layout is kept, including the delimiter and the column order. History can bring back the earlier version of the file.

Limits to know before you write a query

CSV cells are text. The operators >, >=, <, and <= compare numbers, and SUM, AVG, MIN, and MAX read numbers. The = and != operators compare text, so WHERE amount = 80 does not match a cell that holds 80.00. Use WHERE amount = '80.00', or compare with BETWEEN.

Joins, UNION, and more than one table are not supported. WHERE compares a column or an expression to a value, not to another column. Window functions and common table expressions are also out. A query that uses one of these, or names a column the file does not have, shows an error. It never shows a partial result.

Common uses

SQL suits a CSV when you know the question and want the answer fast, without opening a spreadsheet, writing a script, or loading a database. These are typical jobs.

• Count rows per category in an export.
• Find the top ten rows by a number, with ORDER BY and LIMIT.
• List the distinct values in a column.
• Total a column for each month, region, or account.
• Pull the rows that match a text pattern with LIKE.
• Cut a large file down to the rows you need, then apply the result.

Free and Pro

The SQL panel is part of Pro, a one-time purchase of US$19.99 with lifetime access and no subscription. Free users can open a Pro tool and select Try it free today, which gives one free try of each Pro tool per day. The Table view, search, and CSV to JSON conversion stay free.

Frequently asked questions

Related tools