Screens: Connections and explorer

Data export, import and native backup

From the object explorer's right-click menu you can export a table's data to a file and load a file's data into a table. You can also back up tables, schemas and databases with the dump tools your DBMS provides (pg_dump, mysqldump and so on) and restore from those files. All four features work only in the desktop app, because the desktop app's local server reads and writes files on this PC (Web and desktop).

Where you can use itWebDesktopWithout signing inSigned inLocal projectsAccount projects

What's on the screen

No.MenuWhat it doesShown on
1Explorer context menuData Export...Saves every row of the table to a CSV, JSON or Excel fileTables that exist in the real database
2Explorer context menuNative Backup...Creates a backup file with the DBMS's dump toolDepends on the DBMS (see 'Native Backup' below)
3Explorer context menuData Import...Inserts the rows of a CSV, JSON or Excel file into the tableTables that exist in the real database
Explorer context menuNative Reload...Restores a backup file into the databaseDepends on the DBMS (see 'Native Reload' below)
  • Tables created in the project but not yet applied to the database, and tables marked for deletion, don't showExplorer context menuData Export... or Explorer context menuData Import....
  • Select several tables of the same connection and database together (click while holding Ctrl or Cmd) and right-click to export or import them in one go.

Export data

  1. Right-click a table and choose Explorer context menuData Export.... The Export Data dialog opens. For several tables it is the Export Data · 3 tables dialog.
  2. In the Format step, choose CSV, JSON or Excel.
  3. In the Options step, set the options for that format (Field delimiter, CSV Encoding and Header for CSV; long-cell handling and file splitting for Excel, and so on).
  4. In the Preview step, check what the file will look like.
  5. In the Output step, set the Output folder and the file name, then click the Download button.
  • The dialog closes and the export continues in the background. The local server reads the table's rows from the database and writes them straight to the file, so rows never loaded on screen are included too.
  • When you export several tables, all files are saved in one folder. If names collide, the schema name or a number is added. The number of tables exported at the same time is set in Concurrent export jobs.
  • A file whose values had to be altered because of the format's limits gets _lossy in its name. That file can't be imported back as the original data.
  • The options are the same as in the dialog that exports SQL results. Each option is described in Exporting results.

Import data

  1. Right-click a table and choose Explorer context menuData Import.... The Import Data · orders dialog opens.
  2. In the Format step, choose the file format (CSV, JSON or Excel).
  3. In the Options step, drop or choose the file. For CSV choose the encoding, delimiter and Use first row as column names; for Excel choose the sheet. Data commit policy is also set here (table below).
  4. In the Column Mapping step, decide the value for each column of the table. If the first row holds column names, file columns with the same name (ignoring case) are connected from the start; otherwise file columns are connected in order. Check the values in the File Preview (first 20 rows) table below.
  5. Click the Start Import button. The dialog closes and the import runs in the background. When it finishes, the Data Import Result window shows the Processed rows, Committed rows and Failed/skipped rows items.

Value source offers five choices for each column.

Value sourceValue inserted
File columnThe value of the chosen file column
Fixed valueThe same value for every row
Database defaultThe column's database default
NULLNULL
Do not importLeaves the column out of the INSERT. Required columns can't be left out

Data commit policy decides how much is rolled back when an error occurs or you cancel.

PolicyWhen it commitsOn failure or cancel
Commit after full successOnce, after the whole file succeedsEverything is rolled back
Commit every N rowsEvery given number of rowsOnly the rows after the last commit are rolled back

If a row fails during the import, the import pauses and the Data Import Error window shows that row's number in the file, the target column and the original value. Choose the Skip, Skip all, Retry or stop button. The stop button appears as Stop and roll back all for the Commit after full success policy as Stop and roll back uncommitted rows for Commit every N rows and otherwise as Stop (keep committed rows). Stopping rolls back according to the policy above.

Import several tables at once

Drop the files together onto the Upload Multiple Files area in the Options step. A file whose name contains a table name is connected to that table automatically; Excel files are also matched by worksheet name. Import order starts out with parent tables first based on the FKs, and you can drag to change it. Tables are imported one at a time in this order, and if one fails or is canceled, the tables after it aren't started.

Native Backup

Native Backup runs the DBMS's dump tool on this PC to create a backup file. Dump tools aren't bundled with NeoSQL, so install them on this PC first. H2 and SQL Server back up directly in the database without a dump tool (table below).

  1. Right-click the node to back up and choose Explorer context menuNative Backup.... The Native Backup dialog opens.
  2. Choose the dump tool's executable in Local client. When you choose it, it is run once to show its version. The path you choose is remembered on this PC. The Local client field is not there for H2 and SQL Server.
  3. Check Output folder and File name. The file name includes the date and time.
  4. Click the Start Backup button. The dialog closes and the backup runs in the background.
DBMSDump toolFileNodes you can back up
PostgreSQLpg_dump.dumpTable, schema, database
MySQLmysqldump.sqlTable, schema (= database)
MariaDBmariadb-dump.sqlTable, schema (= database)
Oracleexpdp (Data Pump).dmpTable, schema, connection (whole database)
SQLitesqlite3.sqlTable, connection (whole file)
H2Not needed.sqlTable, connection
SQL ServerNot needed.bakDatabase (except system databases)
  • You can back up several databases of the same connection, or several tables of the same database, together. The number run at the same time is set in Concurrent jobs.
  • For PostgreSQL, a warning appears when the dump tool's major version differs from the server's. The backup still runs, but the file may not restore, so use a tool of the same major version.
  • Oracle creates the file in a Data Pump directory on the database server and then downloads it to the folder you chose. Enter the directory name in Oracle Data Pump directory; you need READ and WRITE on that directory. The file on the server is not deleted.
  • SQL Server creates a copy-only full backup in a folder on the SQL Server host. The folder is a path on the database server, not on this PC, and the SQL Server service account must be able to write to it.

Native Reload

The Explorer context menuNative Reload... menu restores a file made by Native Backup (or a dump made with the same tool) into the database.

  1. Right-click the node to restore into and choose Explorer context menuNative Reload.... The PostgreSQL Native Reload dialog opens.
  2. Read the warning at the top of the dialog to see what restoring changes on this DBMS and what can't be rolled back.
  3. Choose the backup file in Backup file and the restore tool in Local client. If you chose a dump tool in Native Backup, the restore tool in the same folder is filled in.
  4. Click the Start Reload button. When it finishes, Native Reload completed. is shown and that node in the explorer is read again.
DBMSRestore toolFileNode to restore intoExisting objects and rollback
PostgreSQLpg_restore.dump, .backupDatabaseExisting objects aren't dropped. If names collide, the whole restore is rolled back
MySQL, MariaDBmysql, mariadb.sqlSchema (= database)Runs the file as is, including its DROP, CREATE DATABASE and USE statements. What has already run isn't rolled back on an error
Oracleimpdp (Data Pump).dmpConnectionUploads the file to a Data Pump directory and restores it. Existing tables are skipped, and the whole job isn't rolled back
SQLite, H2sqlite3, not needed.sqlConnectionRuns the SQL in the file. Changes already made may not be rolled back on an error
SQL ServerNot needed.bakDatabase (except system databases)Replaces the database with the backup. Existing connections are closed, and it can't be rolled back once overwritten

SQL Server backup files aren't uploaded from this PC. Enter a server path that the SQL Server service account can read in SQL Server backup file.

Progress and canceling

Export, import, backup and restore all continue as background tasks after the dialog closes. Click the task indicator in the status bar at the bottom to open the Background Processes list, where each task shows its progress and a Cancel button. For an import, the View result details button in this list reopens the result dialog even after it has finished. The status bar is described in App menu, top bar and status bar.

In the web app

The web app shows these four menus too. Clicking one only shows This feature is only available in the desktop app. and does nothing else. In the web app, use Exporting results to export SQL results.

Things to keep in mind

  • A connection profile's protection mode also applies to the Explorer context menuData Import... and Explorer context menuNative Reload... menus. On a connection whose protection mode is Read-only (block non-SELECT), these two features do not start, and with Confirm (DDL + risky DML) the Explorer context menuNative Reload... menu asks before you pick the file. Check the target connection again before using them on a production database (Safeguards for production databases).
  • The Explorer context menuNative Reload... menu can overwrite existing data or stop with only part applied, and on some DBMSs it can't be rolled back (table above). Back up the target database separately before restoring.
  • In an account project, if your role's DDL is restricted, Explorer context menuNative Reload... is dimmed. The same applies to a disabled connection.
  • In an account project, if sensitive-column masking applies to your role, Explorer context menuNative Backup... isn't shown, because the dump tools write the original data without masking.
  • The dump and restore tool paths are remembered only on this PC. Choose them again on another PC.