SQL Console
SmoothCSV provides a powerful SQL query interface for CSV files through its SQL Console feature. This feature leverages SQLite as its underlying SQL engine, enabling you to perform complex data analysis directly on your CSV files.
Accessing the SQL Console
There are three ways to open the SQL Console:
- Via Command Palette: Search for “SQL console”
- Via Menu Bar: Navigate to
File>New SQL Console - Via
Open in SQL Consolein the filter widget
Writing SQL Queries
Query Syntax Overview
The SQL Console supports SQLite’s SQL syntax.
Table References
You can reference CSV files in queries using several formats:
-- Filename without extension (matches an open file by its base name)
SELECT * FROM customers;
-- Direct file path (simplest for files on disk)
SELECT * FROM "/path/to/file.csv";
-- file:// URL format
SELECT * FROM "file:///path/to/file.csv";
-- @file: prefix syntax (legacy, still supported)
SELECT * FROM "@file:/path/to/file.csv";
-- For untitled/unsaved files
SELECT * FROM "@untitled:Untitled-1";
The system attempts to locate files in the following order:
- Currently opened files in the editor
- Files in the specified filesystem path
Filename References
You can reference an open file by its filename without the extension. The lookup is case-insensitive and only matches currently opened documents.
If more than one open file shares the same base name, the console returns an ambiguity error so you can fall back to a full @file: or path reference.
Column References
Column names in queries can be specified in two ways:
-
When header row is present:
- Use the actual header values
- Enclose names with special characters in double quotes
SELECT "First Name", Age FROM "@file:/data.csv" WHERE "First Name" LIKE 'John%'; -
When no header row exists:
- Use default column names:
c1,c2, etc.
SELECT c1, c2 FROM "@file:/data.csv" WHERE c1 LIKE 'A%'; - Use default column names:
Data Type Handling
All CSV values are stored as text. Arithmetic operators (+, -, *, /) implicitly convert numeric-looking text to numbers, but comparison operators (>, <, >=, <=) compare values as text. For reliable numeric comparisons, use CAST:
-- Price column contains text values like "1000", "1500", etc.
SELECT
Price * 1.1 AS price_with_tax,
COUNT(*) AS count
FROM "@file:/data.csv"
WHERE CAST(Price AS REAL) > 1000
GROUP BY Category;