CSV and JSON files

A Local files connection lets you query files on your Mac with SQL without loading them into a database. Each group of files becomes a dataset: a read-only table that you can open, query, export and use in jobs. Queries run in DuckDB, so you write DuckDB SQL.

Create a Local files connection

1. Click Connections in the left rail.
2. In the Data sources section, click + (Add source).
3. In Create connection, choose Local files under Files and import.
4. Enter a Connection name.
5. Under File datasets, click Add dataset.
6. Click Choose files and select one or more .csv, .json, .jsonl or .ndjson files.
7. Check SQL table name. QueryLane fills it from the first file name, with spaces and punctuation replaced by _. You use this name in queries.
8. Leave File format on Detect automatically or pick CSV, JSON or JSONL. Automatic detection needs all files of the dataset to have the same extension.
9. Add more datasets with Add dataset if you need them, then click Create.

When you select several files for one dataset, QueryLane combines them into one table and matches columns by name.

Format options

For CSV files, CSV options has:

• First row contains column names: on by default. Turn it off when the file has no header row.
• Read every column as text: reads all columns as strings instead of detecting numbers and dates.
• Delimiter: leave empty to detect it, or type a single character such as ;.

For JSON files, JSON options has Record layout: Detect automatically, JSON array, One object per line or Unstructured JSON.

Query the files

In the explorer, the connection shows the Datasets group with one table per dataset. Open a dataset to browse its rows, or start a query and use the dataset name as a table:

SELECT country, count(*) AS customers
FROM customers
GROUP BY country
ORDER BY customers DESC;

You can join datasets of the same connection with each other. Explain shows the DuckDB plan. Query results work like any other result: you can export them or copy them into a database connection with Copy data.

QueryLane reads the files every time a query runs, so changes you save to a file appear on the next run. The connection stores the file paths, so a moved or deleted file makes queries fail with Dataset file was not found.

To add or remove files and datasets later, click Edit on the connection row in Data sources. The button is shown while the connection is closed.

You can also pick a Local files connection in a job step. See Step types.

Limits

• The connection is read-only. Each run accepts one statement, and it must be a SELECT (with or without WITH) or an EXPLAIN. You cannot insert, update, delete, create tables or import into it.
• Queries can read files only from the folders that hold the dataset files, and cannot reach the network.
• A connection holds up to 100 datasets. A dataset holds up to 1,000 files. Dataset names must be unique, regardless of case.
• Each query runs in its own DuckDB session with up to 1 GB of memory, 2 GB of temporary disk space and 2 threads.
• A single JSON object can be up to 16 MB.