# Export and import data

Export a table or a result as CSV, JSON or SQL, import a CSV into a new or existing table, and open a SQLite file straight from the desktop.

## Where to find it

| Action | How |
| --- | --- |
| Export a table | `Export...` in the table's context menu in the sidebar, or `Export` in the table's data toolbar |
| Export a query result | `Export result...` above the result grid |
| Export selected cells | `Export selection...` in the grid's context menu |
| Import a CSV file | `Import CSV...` in the table's context menu in the sidebar |
| Open a SQLite file | `Open With` in Finder, or drop the file on the app |
Exports and imports run as jobs, listed under `Jobs` in the status bar.

## Export formats and options

The export dialog has a tab for each format. `CSV` sets the `Delimiter`, the `NULL value` text, the `Line ending`, `Include header` and `Quote every field`. `JSON` writes an `Array of objects` or `Newline-delimited JSON`, with `Pretty print` and `NULL as JSON null`. `SQL INSERT` sets the `Table name`, `Rows per INSERT`, `Include CREATE TABLE` for a table, and `ON CONFLICT DO NOTHING` on PostgreSQL and CockroachDB.

Every format offers `UTF-8` or `UTF-8 with BOM` under `Encoding`. Pick the file under `Destination` with `Browse...`, then choose `Choose file`.

## What gets exported

`Scope` decides which rows are written. From the sidebar the choice is `Whole table (with current filters)`. A table's data tab adds `Current page` and, with cells selected, `Selected rows and columns`, and its whole-table export keeps the filters, sort and visible columns on screen. A query result offers `Query result` and `Selected rows and columns`.

Pending edits are never exported. While a table has unsaved changes, its export writes the stored rows and offers no selection.

## Large exports

An export writes rows to the file as they are read, so a large table does not have to fit in memory. Its row in `Jobs` counts the rows and bytes written, and the button at the end of the row cancels it. [Jobs](https://bobbytables.app/docs/jobs) describes the popover.

## Import CSV

`Import CSV` has three steps: `File`, `Target` and `Run`. Choose a file under `Source file` with `Browse...`, and `Preview` reads it. `Delimiter`, `Header` and `Encoding` start on `Auto-detect`, `Empty fields are NULL` starts on, and `Choose target` moves to the next step. `Existing table` maps each `CSV column` to a `Table column` by name, and `Skip` leaves a column out, while `New table` creates a table from the file's columns. A required column left unmapped, or a table column used twice, keeps `Run import` disabled.

## Import results

The import runs in one transaction. When a value does not fit, the import stops, rolls back every row, and returns to `Target` with the error, which names the CSV row when the app can identify it. Correct the file and choose `Run import` again. A finished import reports the rows imported and offers `Open table`.

## Opening a SQLite file

Bobby Tables registers as an editor for `.sqlite`, `.sqlite3`, `.db` and `.db3` files. In Finder, choose `Open With` and then Bobby Tables, or drop the file on an app window. On Linux, open it from the file manager or pass its path to the app on the command line. The app switches to a workspace already open on that file, connects a saved SQLite connection for it, or opens it without saving a connection.

## Good to know

- Exports write the value the database stores. A result column shown through a directive keeps the heading the grid shows; see [Result display directives](https://bobbytables.app/docs/result-directives).
- Exporting saved connections is a separate feature; see [Import and export connections](https://bobbytables.app/docs/connection-import-export).
- Import works on PostgreSQL, CockroachDB, MySQL, MariaDB, TiDB, SQLite, SQL Server, ClickHouse, Snowflake and Oracle. Export works on the same engines and Cloudflare D1.
- ClickHouse has no rollback, so a failed import keeps the batches already written.
- MongoDB has neither import nor export, and Firestore exports loaded documents from its own workspace.
- A read-only connection can export but cannot import.

## Related

- [Results](https://bobbytables.app/docs/results.md)
- [Jobs](https://bobbytables.app/docs/jobs.md)
- [Import and export connections](https://bobbytables.app/docs/connection-import-export.md)
- [Database snapshots](https://bobbytables.app/docs/snapshots.md)
- [Files](https://bobbytables.app/docs/files.md)