Exporting and importing data

Import delimiter-separated values into a database

You can import a CSV, TSV, or any other text file that contains delimiter-separated values into your database.

1. In the Database tool window (View | Tool Windows | Database), right-click a schema or a table and select Import Data from File.

2. Navigate to the file that contains delimiter-separated values and press Open.

3. In the Import <file_name> File dialog, specify the data conversion settings and click OK.

Export data into a file

You can export database data in files as SQL INSERT and UPDATE statements, TSV and CSV, Markdown, HTML tables and JSON data. A separate file is created for each individual table or view.

1. In the Database tool window (View | Tool Windows | Database), right-click a database object and navigate to Dump Data to File(s).

2. From the context menu, select the format that you want to use for export (for example Comma Separated Values (CSV)).

3. In the file browser, specify the destination directory and click Open.

Configuring data output formats and options

To configure the output formats for the Dump Data to File(s) command, select one of the following from the menu associated with the command:

• Configure CSV Formats: opens the CSV Formats dialog that you can use to manage your delimiter-separated values formats (for example, CSV or TSV).

• Go to Scripts Directory: opens a directory with the scripts that convert table data into various output formats.

For SQL INSERTs and UPDATEs, there are additional options: Add Table Definition (SQL), Skip Computed Columns, and Skip Generated Columns.

Dump data with mysqldump or pg_dump

You can create backups for database objects (for example a schema, a table, or a view) by running mysqldump for MySQL or pg_dump for PostgreSQL.

1. In the Database tool window (View | Tool Windows | Database), right-click a database object and navigate to:

   • Dump with "mysqldump": for MySQL data sources.

   • Dump with "pg_dump": for PostgreSQL data sources.

2. In the Dump with <dump_tool> dialog, specify the path to the dump tool executable in the Path to <dump_tool> field.

   (Optional) Edit the command-line options in the lower part of the dialog.

3. Click Run.

MySQL	PostgreSQL

Restore a dump with mysql, pg_restore, or psql

You can restore data dumps by means of the mysql client utility for MySQL, or pg_restore or psql for PostgreSQL.

The pg_restore option is used for custom-format pg_dump -Fc or directory-format pg_dump -Fd dumps.

The psql option is used for SQL-format dumps.

1. In the Database tool window (View | Tool Windows | Database), right-click a schema or a database and navigate to:

   • Restore with "mysql": for MySQL data sources.

   • Restore with "pg_restore": for PostgreSQL data sources. The pg_restore option is available for the most of database objects except for the data source level.

   • Restore with "psql": for PostgreSQL data sources. The psql option is available for the most of database objects except for table and schema levels.

   • Restore: for PostgreSQL data sources. Includes two tabs: pg_restore and psql.

2. In the Restore with <dump_tool> dialog, specify the path to the restore tool executable in the Path to <dump_tool> field.

   (Optional) Edit the command-line options in the lower part of the dialog.

3. Click Run.

MySQL	PostgreSQL