Import & Export

Mantidb can write any table, view or read-only query result to a file, and load rows from a file into an existing table. Both run in the background with progress, and both respect your connection's safe mode.

Export

Open the export sheet

You can start an export in three ways:

• File ▸ Export… (⇧⌘E).
• Press ⌘K and choose Export….
• Right-click a table, view or materialized view in the sidebar and choose Export….

The sheet is titled Export followed by the name of what you are exporting. Its subtitle tells you what will be written, for example the object type, the estimated row count and how many filters apply.

What gets exported

A query result can only be exported when its SQL is a single, read-only statement. Otherwise you see "Export one statement at a time." or "Only read-only queries can be exported."

Exporting is the way to get every row of a query whose result was cut at the row limit.

Formats

Choose a Format at the top of the sheet. CSV is the default, and Mantidb remembers your last choice.

For SQL INSERT, set the Table name to use in the statements. It defaults to the source table, or query_result for a query.

Columns

The Columns list shows how many columns are selected ("N of M"). Use All or None, or tick individual columns. Columns hidden in the grid start unchecked.

Format details

The formats have fixed settings:

• CSV: NULL is written as an empty field, empty text as "", and binary values as 0x… hex.
• JSON: null, booleans and numbers keep their JSON types.
• SQL INSERT: one statement per row; there is no batching.
• Excel (XLSX): integers of 10^18 or more are stored as text, and cells are cut at 32,767 characters.

There are no options for the CSV delimiter, header or NULL text, the JSON style, or the INSERT batch size.

Excel limits

An Excel sheet holds at most 1,048,575 data rows, and the file can be at most 4 GB. For larger exports Mantidb tells you: "Excel sheets hold at most 1,048,575 rows; export as CSV instead."

Running an export

1. Pick the format and columns.
2. Click Export… and choose where to save. The file name defaults to <title>.<ext>.
3. Watch the progress ("N rows written…"). Click Stop to cancel.
4. When it finishes, the sheet shows "Exported N rows to file". Click Show in Finder to see the file.

Rows are streamed in batches of 1,000 on a separate session, so the workspace stays usable. Mantidb writes to a hidden .partial file and moves it into place only when the export succeeds, so a failed or stopped export never leaves a half-written file behind.

Import

Open the import sheet

Imports always go into an existing table:

• File ▸ Import… (⇧⌘I), when a table is selected.
• Right-click a table in the sidebar and choose Import….

A file picker opens first ("Choose a CSV, JSON or SQL file to import"). Accepted extensions are csv, tsv, txt, json, jsonl, ndjson and sql. The sheet "Import into <table>" then shows the file name and size; use Choose File… to pick another file.

Format

Choose CSV, JSON or SQL dump. Mantidb guesses the format from the file extension.

Files must be UTF-8. Other encodings are not supported.

CSV options

Mantidb reads standard RFC 4180 CSV, including quoted fields that span several lines, CRLF or LF line endings, and a UTF-8 BOM. CSV files are streamed, so they can be large.

JSON files

A JSON file can be an array of objects or JSON Lines (one object per line). The columns are all the keys found, in the order they first appear. Nested objects and arrays are imported as JSON text.

The whole JSON file is loaded into memory. For very large files, convert them to CSV first.

Column mapping

The Columns list has one row per table column, with a Source picker:

• A file column — its values go into this table column.
• Default — the column is left out of the INSERT, so the server default applies.

Columns are matched automatically by name, ignoring case and non-alphanumeric characters. If the file has no header and no names match, columns are matched by position. Primary key columns are marked key.

Preview

The Preview shows the first rows of the file as they will be imported (up to 20 rows). For SQL dumps it shows the first 20 statements, with a reminder that they run one by one, not in a transaction.

Duplicate keys

For CSV and JSON, choose what happens when a row's primary key already exists:

Mantidb uses the engine's native upsert: ON CONFLICT on PostgreSQL and SQLite, ON DUPLICATE KEY UPDATE on MySQL / MariaDB, and MERGE on SQL Server.

On error

• Skip failing rows and report them (default) — good rows are imported; bad rows are listed.
• Stop and roll back everything — the first error cancels the whole import.

For SQL dumps, the choice is Stop at the first error.

Transactions and batches

CSV and JSON imports run in one transaction, using multi-row INSERT statements of 500 rows (or about 1 MiB). Each batch runs under a savepoint; if a batch fails, it is retried row by row so that only the bad rows are reported.

Clicking Stop rolls back everything: "Import stopped. Nothing was imported."

SQL dumps

SQL dumps run statement by statement, outside a transaction. COPY … FROM stdin blocks are rejected — dump with pg_dump --inserts instead. To restore a full database, use Backup & Restore.

Safe mode

Imports go through the connection's safe mode. The confirmation shows the first 3 INSERT statements followed by a note that every other row of the file follows, 500 rows per INSERT. Imports are blocked on read-only connections.

Results and error report

When the import finishes, Mantidb shows a summary such as "Imported N rows · X duplicates skipped · Y failed". Each failed row is listed as "Row N · line L" with its error (up to 1,000 are kept).

Click Save Error Report… to write <file>-import-errors.csv, with the columns row, line and message.