# Data sources

> Query CSV, Parquet, JSON and Excel files locally with DuckDB SQL. Open folders and globs, attach databases, and keep data on your computer.

Source: https://ducklocal.app/docs/data-sources

**[中文](zh/data-sources.md)**

DuckLocal turns files into views in an in-memory DuckDB connection, or in a
database file if you opened one. Other databases can sit beside it. There is
nothing to configure. See the [local data analysis workflow](https://ducklocal.app/use-cases/local-data-analysis/) for a concise path from files to SQL, charts and export.

## Ways to open data

All of these go through the same resolution rules:

1. Paths on the command line — `ducklocal ./logs/ billing.parquet`
2. The **Open** menu in the title bar: **Open files**, **Open folder**, or
   **Enter a path or glob**, which opens the **Open data** dialog for a typed
   path or one from the native picker
3. Dropping files or folders anywhere on the window
4. On a Mac, files Finder hands to DuckLocal — see
   [Open from Finder](#open-from-finder)

To add a database beside the open one rather than replace it, use
**Open → Attach database…** — see [Attached databases](#attached-databases).

On macOS, you can double-click Parquet and DuckDB files in Finder to open them, or press the Space bar to preview the initial rows of Parquet, DuckDB, CSV, TSV, and JSON Lines files with Quick Look without launching DuckLocal.

## Supported file formats

Extensions are matched case-insensitively.

| Extension | How it is read |
| --- | --- |
| `.csv`, `.tsv`, `.txt` | `read_csv_auto` view |
| `.parquet` | `read_parquet` view |
| `.json`, `.ndjson`, `.jsonl` | `read_json_auto` view |
| `.xlsx`, `.xls`, `.xlsb`, `.ods` | imported as a table per sheet |

Gzipped text such as `.json.gz` and `.csv.gz` counts as a data file too, in `schema`, `source` blocks and when you attach a file; `logs.json.gz` becomes a view named `logs`. Before 0.4.0, `ducklocal schema` tried such a file as a database; on an older copy, read it in SQL with `read_json` or `read_csv`.

Anything else is not a data file — see [Database files](#database-files).

A workbook is the one format that becomes **tables**, not views, and it
imports **every sheet**: the first sheet takes the file stem (the file name
without its extension) as its name, the others are named
`{stem}_{sheet name}`. An empty sheet is skipped. Column
types are inferred from the cells — integers become `BIGINT`, an
integer/float mixture `DOUBLE`, dates `TIMESTAMP`, and anything mixed or
unrecognized `VARCHAR`; empty cells import as `NULL`.

## Folders and patterns

A **directory** is walked recursively, and every data file inside becomes a
view. Two rules apply to the walk:

- Hidden entries (names starting with `.`) are skipped, and their subtrees are
  not descended into.
- Symlinks are not followed. Following a link back up the tree would never
  terminate, and the linked-to directory is normally walked where it actually
  lives.

Files are attached in sorted order, so the same folder always produces the
same workspace.

A **pattern** may contain `*` (any run of characters) and `?` (exactly one).
Matching happens per path component, against the filesystem, so a pattern can
span directories:

```bash
ducklocal './data/2026-0*/*.parquet'
```

There is no escape character. `**` is not special — it behaves like a single
`*` and does not recurse. A pattern that matches a directory skips it; only
files are attached. Hidden entries are skipped here too.

Unquoted patterns are expanded by your shell before DuckLocal sees them. That
is fine. Quote a pattern only when you want DuckLocal to expand it — for
example, when the shell finds no match and would otherwise error out.

## Many CSV files at once

Each file DuckLocal attaches becomes its own view with its own sniffed schema,
so two CSVs that disagree never break each other. The pitfalls show up when one
SQL read spans many files:

```sql
SELECT * FROM read_csv('data/*.csv', union_by_name = true)
```

- **Type sniffing can disagree across files.** The reader infers a column's
  type from a sample; a column that is numeric everywhere but holds a stray
  `'NULL'` string in one file fails the whole read with a conversion error, and
  a cast in the `SELECT` list comes too late — the read itself errors. Read
  every column as text and cast in SQL instead:

  ```sql
  SELECT try_cast(amount AS DOUBLE) AS amount
  FROM read_csv('data/*.csv', union_by_name = true, all_varchar = true)
  ```

  `union_by_name = true` also tolerates files whose column sets differ.
- **The string `'NULL'` is not `NULL`.** A literal `'NULL'` (or `'N/A'`,
  `'null'`) in a CSV is ordinary text, so `WHERE amount IS NOT NULL` does not
  filter it. Use `NULLIF(amount, 'NULL')`, or `WHERE amount <> 'NULL'`.

The two fixes compose: `try_cast(NULLIF(amount, 'NULL') AS DOUBLE)`.

## Database files {#database-files}

DuckLocal has one **main database** at a time: in memory, or a DuckDB file
you opened. Data files become views in the main database. You can also
[attach other databases beside it](#attached-databases).

Any path that **exists** and is not a data file is opened as the main
database instead of being attached as a view. The extension does not matter:
a `.db`, `.duckdb`, or even a `.md` path is treated as a database, and DuckDB
decides whether it can actually be opened.

A few consequences are worth knowing:

- **A new database comes only from the Open data dialog.** Type a path that
  ends in `.duckdb` and does not exist yet, and DuckLocal creates the
  database, along with any missing parent folders. Everywhere else — the
  command line, the pickers, a drop, Finder — a path that does not exist is
  reported as `File not found`.
- **Opening a database replaces the main one.** Your registered files and
  attached databases are attached again into the new connection. S3
  credentials are not; configure S3 again.
- **If the file cannot be opened**, DuckLocal tells you why
  ([below](#when-a-database-will-not-open)). At launch it falls back to an
  in-memory workspace rather than failing to start. While it is running, the
  workspace you had stays as it was.
- **A file that is already attached** beside the main database is detached
  first, then opened as the main database. If that open fails, the
  attachment comes back. Opening the file that is already the main database
  reuses it.

Attaching data files while a database file is open **writes those views into
that database file** — they persist there, not merely in the session.

### When a database will not open {#when-a-database-will-not-open}

When DuckDB refuses a file, DuckLocal reads the file's first bytes itself and,
where they explain the failure, puts a reason and a way out above DuckDB's own
message. DuckDB's message stays underneath for the exact error. The
explanation is in the interface language.

| What the file is | What DuckLocal tells you |
| --- | --- |
| A DuckDB file written by a **newer DuckDB** than the one built into DuckLocal | Both versions, and SQL to run in the newer DuckDB that copies the file into a format DuckLocal reads (below) |
| A DuckDB file from **v0.9 or earlier** | Run `EXPORT DATABASE 'dir'` with the DuckDB that wrote it, then `IMPORT DATABASE 'dir'` in DuckLocal |
| A **SQLite** database | It is not a DuckDB file; attach it instead — with [Attach database…](#attached-databases), or `INSTALL sqlite; ATTACH '…' (TYPE sqlite);` |
| Something else that is **not a DuckDB file** | Open CSV, Parquet, JSON or Excel files as data files instead |

For a file from a newer DuckDB, the message fills in the file's path and
DuckLocal's DuckDB version. Run it in the DuckDB that created the file, then
open `compat.duckdb`:

```sql
ATTACH '/path/to/file.duckdb' AS src (READ_ONLY);
ATTACH 'compat.duckdb' AS dst (STORAGE_VERSION 'v2.0.0');
COPY FROM DATABASE src TO dst;
```

Files written before DuckDB v1.2 do not record which release created them.
Any other failure — a file another program has locked, one you may not read —
shows DuckDB's message alone. See
[Troubleshooting](troubleshooting.md#opening-a-database).

### Which DuckDB can read a database {#storage-version}

When a database file is open, the status bar shows its path. Hover over it to
see the file's storage format — the oldest DuckDB that reads it, such as
**Storage format: readable by DuckDB v1.0.0+** — and, when the file records
it, the release that created it (**Created by DuckDB v1.4.3**). Check this
before you hand a file to someone on an older DuckDB.

DuckLocal runs DuckDB 2.0, but a database file it creates is written in the
format DuckDB 1.x wrote (**readable by DuckDB v1.0.0+**), so the duckdb CLI,
Python or DBeaver on DuckDB 1.x still open it. A file another program wrote in
DuckDB 2.0's own format (**v2.0.0+**) opens too, and keeps that format: 1.x
cannot read it.

## Attached databases {#attached-databases}

Attach another DuckDB or SQLite file beside the main database to query across
them — a snapshot next to today's data, say.

1. Open the **Open** menu in the title bar and choose **Attach database…**.
2. Type the path, or click **Browse** to pick the file.
3. Leave **Read-only** checked unless you mean to change the file. It is
   checked by default.
4. Click **Attach**.

The database gets an **alias** from its file name, and you query its tables
as `alias.table` (or `alias.schema.table`):

```sql
-- snap-2026-10-01.duckdb attached as snap_2026_10_01
SELECT t.id, t.amount, s.amount AS amount_last_week
FROM orders t
JOIN snap_2026_10_01.orders s USING (id);
```

How the alias is made:

- The file stem, with every character that is not an ASCII letter or digit
  replaced by `_`.
- `db_` in front when it does not start with a letter:
  `2026-10 logs.duckdb` becomes `db_2026_10_logs`.
- A counter when the name is taken: `snap`, then `snap_2`. `main` is never
  used.
- Attaching a file again later gives it back the alias it had, so SQL written
  against it keeps working.

Details:

- **SQLite files** are recognized by their contents and attached through
  DuckDB's `sqlite` extension.
- **Unchecking Read-only** attaches the file for writing. A DuckDB path that
  does not exist is then created as a new, empty database; read-only, the
  attach fails instead.
- **The same file is never attached twice.** Attaching a file that is
  already open — as the main database or as an attachment — says
  *This database is already open, as …*.

### In the sidebar {#in-the-sidebar}

Every database appears in the schema tree, the main one and attached ones
alike, empty ones included. A database shows **read-only** or **sqlite**
beside its name where that applies, and its file as a tooltip.

Right-click a database for:

- **Use as default database** — runs `USE`, so table names without a
  database resolve there. The status bar shows where they resolve now.
- **Detach database** — detaches it and forgets it, so it does not come back
  next time. If it was the default database, the session moves back to the
  main database first. The file itself is untouched.

The rest of that menu is described in
[Schema browser and history](schema-and-history.md).

### Kept across connections {#kept-across-connections}

Attached databases are stored in DuckLocal's own history database, with their
alias and read-only setting, and attached again into every new connection:
on the next launch, after you open another database, and after **New
in-memory database**.

- A database whose file is **missing** is skipped quietly. Its registration is
  kept, so it comes back once the file does — DuckLocal never creates an
  empty database in its place.
- A database that is **already open**, as the main database or otherwise, is
  not attached a second time.
- If the new main database already has a database by that alias, DuckLocal
  says *Could not attach …: the name … is taken in the open database.*

## Open from Finder {#open-from-finder}

On a Mac, Finder can hand files to DuckLocal directly:

| File | Double-click | Open With, or drop on the Dock icon |
| --- | --- | --- |
| `.parquet` | Opens in DuckLocal | Yes |
| `.duckdb`, `.ddb` | Opens in DuckLocal | Yes |
| `.dash` | Opens in DuckLocal | Yes |
| `.csv`, `.tsv`, `.json`, `.jsonl`, `.ndjson`, `.xlsx`, `.xls` | Stays with the app that already opens them (Numbers, Excel, a text editor) | Yes |

DuckLocal registers itself as the default only for formats nothing else on a
Mac claims. If another app already took over Parquet or DuckDB files, choose
DuckLocal under **Open With** — or use Finder's **Get Info → Open with →
Change All…** to make it the default.

A file from Finder is handled exactly like a drop on the window: a data file
becomes a view in the current workspace, a database file is opened as the
main database, and a `.dash` file opens as a [dashboard](dashboards.md) tab.
If DuckLocal is not running, it launches first and opens the file once its
own startup has finished.

## Quick Look previews {#quick-look}

On a Mac, select a data file in Finder and press **Space** to see its columns
and first rows without opening anything. The preview ships inside
DuckLocal.app; there is nothing to install separately. It needs macOS 12 or
later, like the app.

| File | What the preview shows |
| --- | --- |
| `.parquet` | Columns and types, the first 50 rows, and from the file's footer: row count, row groups, compression, and the writer |
| `.duckdb`, `.ddb` | The number of tables, the storage format (**Readable by DuckDB …**), and the five largest tables, each with its estimated row count and first 50 rows |
| `.csv`, `.tsv` | Columns with their sniffed types, and the first 50 rows — a table instead of raw text |
| `.jsonl`, `.ndjson` | Columns and the first 50 rows |

Plain `.json` and Excel files are left to macOS's own previews.

What the preview does and does not do:

- It never writes. A database is attached read-only.
- It never scans a whole file: rows are a `LIMIT`, and a row count appears
  only where the format stores one (Parquet's footer, a database's estimate).
- It downloads nothing: DuckDB extensions are neither installed nor loaded.
- Cells longer than 200 characters are cut off. The page text is in English.
- A file it cannot read gets a page saying why.

If pressing Space shows no DuckLocal preview:

1. Make sure DuckLocal is in **Applications**, and launch it once. macOS
   registers the preview when it first sees the app.
2. Reset Quick Look in Terminal: `qlmanage -r`. Then try again.
3. Check that **DuckLocal Preview** is turned on under Quick Look in System
   Settings' extension settings.

## Naming

A file's view is named after its file stem, cleaned up into a valid identifier.
When that name is already taken in the connection, DuckLocal appends a counter:
`events`, then `events_2`, `events_3`. Two files with the same stem in
different directories therefore get distinct views.

Duplicate detection is by the path text as written, so `./a.csv` and `a.csv`
are treated as two different files.

## Limits

- **256 data files per open request.** The cap covers all the paths in one
  request together, not each folder separately — it exists so that dropping a
  home directory by accident does not tie up the connection for minutes. When
  the cap is hit, DuckLocal says so and attaches the first 256 files.
- Files re-attached at startup from the previous session are not subject to
  that cap.

## Managing registered sources

Registered files appear in the sidebar's **Local files** group, each with its
view name, file name, and row count. Selecting a file generates a `SELECT`
statement for it.

The **×** on a file row removes it, after a confirmation dialog. Removal drops
the view and forgets the registration; **the underlying file is untouched.**

**Refresh schema** reloads the catalog and the registered files.

## What is remembered

The list of registered files — their paths, view names, and kinds — is stored
in DuckLocal's own history database and re-attached on the next launch, so the
workspace you left is the workspace you get back. So are
[attached databases](#kept-across-connections). The main database file is
*not* remembered: each launch starts in memory unless you name a database.

See [Settings and app data](settings-and-data.md) for the on-disk locations.