SQL on CSV

Read-only queries with SQLite's rules. SELECT with DISTINCT, *, table.* and aliases; FROM with several tables, sub-queries as tables, JOIN (inner), LEFT, RIGHT and FULL JOIN with ON, CROSS JOIN and comma joins; WHERE; GROUP BY (by expression, alias or position) and HAVING; ORDER BY with ASC, DESC and NULLS FIRST or LAST; LIMIT and OFFSET. Sub-queries wherever a value goes, IN (SELECT ...), EXISTS and NOT EXISTS, also ones that use the outer query's columns. WITH, and WITH RECURSIVE for tables that build on themselves. UNION, UNION ALL, INTERSECT, EXCEPT and VALUES. Window functions with OVER: PARTITION BY, ORDER BY, ROWS, RANGE and GROUPS frames, EXCLUDE and named WINDOW definitions, for row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value, nth_value and every aggregate, which also take FILTER (WHERE ...). Expressions: arithmetic, ||, comparisons, AND, OR, NOT, IS [NOT] NULL, IN (list), BETWEEN, LIKE with ESCAPE (as in SQLite, letter case is ignored for A to Z only), CASE and CAST. Numbers as SQLite 3.51 reads them: 12, 1.5, 1e3, hex 0x1F and digit separators such as 1_000. Functions: count, sum, avg, min, max, total, group_concat, string_agg, abs, round, length, lower and upper (which change A to Z only, as SQLite's do), trim, ltrim, rtrim, substr, replace, instr, coalesce, ifnull, nullif, iif, typeof, and date, time, datetime and strftime without modifiers. Refused with a message that says why, and for most what to write instead: NATURAL JOIN and JOIN ... USING, an aggregate in a sub-query that uses only the outer query's columns, GLOB, COLLATE, CAST to BLOB or another type SQLite reads as BLOB and BLOB literals such as x'41' (there are no BLOB values), the JSON operators -> and ->>, query parameters (?, :name, @name, $name), row values such as (a, b) IN (...), the words asc, by, cast, cross, desc, end, false, first, full, glob, inner, last, left, like, natural, nulls, offset, outer, right, true and with as a name without double quotes, which SQLite accepts in most places (write "end"), and any statement that changes data or reaches outside the loaded files (INSERT, UPDATE, DELETE, CREATE, ATTACH).

Close by design, and compared with SQLite itself. The engine follows SQLite's type rules (INTEGER, REAL, TEXT and NULL, integer division, 64-bit integers, column affinity when text meets numbers, NULLs first when sorting, sum() and avg() added with SQLite's compensated summation) and was checked against SQLite 3.45 on 95 hand-written queries and 6,000 randomly generated expressions, then for sub-queries, WITH, UNION, RIGHT and FULL JOIN and window functions on 129 hand-written and 13,500 random queries, also against SQLite 3.51, and, against both, date(), time(), datetime() and strftime() on every day of the years -4713 to 9999 (as text and as a Julian day number), every time of day to the second and 12,000 random queries, and LIKE, NOT LIKE, the text functions and names in mixed case on 67,800 random queries over text with emoji, accents, combining marks, other spaces and NUL characters, and CAST type names, names, numbers and comments in the query on 22,000 more, and REAL values that are infinite, huge or of mixed sign in sums, window frames, comparisons, grouping, CAST and dates on 21,000 more, with identical values and types apart from the differences below. Known differences: A column that is neither grouped nor aggregated is allowed only when it has one value in each group; SQLite would silently pick a row, this tool explains the problem instead. A text value longer than the browser can hold (about 500 million characters in Chrome and Edge, more in Firefox and Safari) stops the query with a message, where SQLite allows 1 billion bytes. Rows that tie on every ORDER BY term, including the ORDER BY inside OVER (...), can come out in another order than SQLite's, so row_number(), lag() or first_value() can differ on such ties; add a column that tells the rows apart. The words true and false always mean 1 and 0, while SQLite reads them as the column when a loaded file has a column named true or false. Spaces outside ASCII, such as the no-break space, separate words here, while SQLite reads them as part of a name. Where SQLite 3.45 and 3.51 differ, the tool follows 3.51: min() and max() with FILTER over a moving window (SQLite 3.45 ignored the FILTER there); sum(), total() and avg() of finite values whose total overflows (two values of 1e308, say), which are Inf in 3.51 and NULL in 3.45; an integer sum over a moving window that leaves 64 bits when a value leaves the frame, which 3.51 reports as an overflow in sum() and carries on in floating point from 0 in total() and avg(), while 3.45 wraps around; a minus sign before +9223372036854775808, which 3.51 reads as the INTEGER -9223372036854775808 and 3.45 as a REAL; a start, length or number of digits beyond 32 bits in substr() and round(), which 3.45 reads as a 32-bit number (4294967298 as 2); replace() with an empty pattern, which 3.45 returns unchanged (an INTEGER stays an INTEGER) and 3.51 as text; digit separators such as 1_000, which 3.45 refuses, and a hex number with a letter straight after it (0x1g), which 3.45 reads as 0x1 named g; a number written with a large exponent, which 3.51 reads with its own rounding (4E-253 is 3.9999999999999996e-253 there and 4e-253 in 3.45); strftime()'s %G, %g, %U and %V, which 3.45 does not have (it gives NULL); a fraction of a second of .9995 or more, which 3.51 reads as .999 and 3.45 rounds up to the next second; the days from 1 March of a century year before the year 0 that is not divisible by 400 to the end of the following February (-0100-03-01 to -0099-02-28, for instance), which 3.45 counts a day late, and 29 February of the years 300, -100, -500 and every 400 years before, which 3.45 accepts though those years have none; and the last digit of a REAL written as text (15 digits), by strftime('%J') (16) or '%f', or by round(), when the value lies a hair below halfway between two last digits: 3.51 writes CAST(2440336.494814815 AS TEXT) as 2440336.49481482 and round(0.9365, 3) as 0.937, 3.45 as 2440336.49481481 and 0.936. Two smaller ones: with LIMIT and no ORDER BY, SQLite stops after the rows it returns, so an error in a later row (abs() of the smallest integer, say) never comes up there, while this tool works out every row first and reports it; and lag() with an offset of -2 or less, which SQLite documents as non-negative, gives the row that many ahead where SQLite gives the default.

A column is INTEGER when every non-empty cell is a whole number, REAL when every non-empty cell is a number, and TEXT otherwise, so a ZIP code column such as 018956 stays text. The Tables panel shows the type of every column. Untick Detect number columns to read everything as text. Empty cells are NULL unless you untick Empty cells are NULL. Column names come from the first row; empty or repeated names become column_3 or name_2, and names with spaces or other characters need double quotes in the query.

Up to 32 MB per file. A 100,000-row file loads in well under a second and typical filters, totals and joins on equal columns (JOIN ... ON) take a fraction of a second on an ordinary laptop. The page is busy while a query runs; a query that takes longer than 20 seconds, a join that would produce more than 5 million rows, or a recursive WITH table that grows past 5 million rows, is stopped with a message suggesting how to narrow it. The 20 seconds count everything a query does, a single LIKE or function over a long text included; a text value can grow to about 500 million characters in Chrome and Edge, more in Firefox and Safari. The result table shows the first 500 rows and the first 1,000 columns; the downloads always contain every row and every column.

query_result.csv is standard CSV (comma, quotes doubled, CRLF line ends) with NULL written as an empty cell; a row whose only cell is empty is written "", so it is not read back as a blank line. It is UTF-8 and starts with a byte order mark, the mark Excel needs to read accented and Chinese letters in a CSV file. query_result.json is an array of objects, one per row, with numbers as numbers and NULL as null; integers beyond 2^53 are written as strings so no digit is lost, and repeated column names get _2, _3. JSON has no infinity: an infinite REAL (1e400 in a file, or a sum past 1.8e308) is written Inf or -Inf in the CSV, as SQLite writes it as text, and 9.0e+999 or -9.0e+999 in the JSON, as SQLite's JSON functions write it; JSON.parse and Python read that back as infinity. Your original files are never changed.

No. The page loads a small script into your browser and every operation runs on your device. You can check this yourself in the browser's network panel: after the page has loaded there are no upload requests. Closing the tab discards the text.

No. These basic tools are free and add nothing to your data. The only limits are the tool's own: files up to 32 MB each; a query stops after 20 seconds, or when a join, or a recursive WITH table, would produce more than 5 million rows. Within them, the practical limit is your device's memory.

Nothing you open or type. While the tab is open, the tool pages keep a short list of the steps taken in it, in the tab's session storage: the step (page opened, started, succeeded, first success, used again or failed), the tool's name, the time, how many files were used and, for a failure, an error code; never a file name, the contents of a file or your text. It holds at most the latest 200 steps, and the browser deletes it when the tab closes. The page never sends it. Where a Report a problem button is shown, you can attach the last 20 steps to your report; nothing is attached unless you tick that box. In local storage the page keeps only choices you made: a Light or Dark theme until you choose System again, the language you picked with the EN / 中文 switch if it differs from your browser's language, and this tool in your recently used list if you switched that list on at the tools page.