DataGrip 2018.3 Help

Export data in DataGrip

DataGrip has a powerful engine to export data in various formats. You can even create your own export formats.

Export directions

Any result set, table, or a view can be exported to a file or copied to the clipboard.

Export to a file

  1. Right-click a result set, table, or a view, select Dump Data | To File.

  2. Right-click a query, click Execute to File and select the file type that you want to use for export (for example, Comma-separated (CSV)).

  3. On the toolbar, click the Dump Data icon (The Dump Data icon) and select To File.

Export a view to a file

Export to a clipboard

  • Select data in the result set or in the editor and press Ctrl+C.

  • On the toolbar, click the Dump Data icon (The Dump Data icon) and select To Clipboard.

Default extractors

You can select a default export format from the drop-down list near the Dump Data icon (The Dump Data icon). There are several built-in formats. You can export data as a set of INSERT or UPDATE statements. Also, you can select text formats like CSV, JSON, HTML, and other formats (for more information about export options, see Import/Export options.

Select the extractor

Creating a DSV-based extractor

You can extend the default functionality and create your own format that is based on CSV (or any DSV format).

  1. From the drop-down list near the Dump Data icon (The Dump Data icon), select Configure CSV formats.

  2. In the CSV Formats dialog, click the Add Format icon (The Add Format icon).

  3. Specify a name of a new format (for example, Confluence Wiki Markup).

  4. Define settings of the format, and click OK.

    When the format is created, you can select it in the drop-down list near the Dump Data icon (The Dump Data icon).

Configuring extractors

Creating any text extractor with scripting

For more complicated cases, use scripting extractors. DataGrip already has some of them (for example, CSV-Groovy.csv.groovy, HTML-Groove.html, and other extractors). These scripts are written in Groovy, but you can also write them in JavaScript. Consider our examples that use Groovy.

Looking closely at the file name, CSV-Groovy.csv.groovy:

  • CSV-Groovy is the name of the script.

  • csv is the extension of the result file.

  • groovy is the extension of the script.

Scripts are usually located in Scratches and Consoles/Extensions/Database Tools and SQL/data/extractors. Or you can select Go to scripts directory in the extractor menu to navigate there.

Go to scripts directory

Edit an existing extractor or add your own to this folder.

Let’s create an extractor that dumps your data to the CSV format, but only to one row. It can be useful if you are going to paste these values into an IN operator in a WHERE clause.

See the following diff of two scripts: the existing CSV-Groovy.csv.groovy and CSV-ToOneRow-Groovy.csv.groovy.

Viewing diff for two scripts

Consider available context bindings:

COLUMNS <DataColumn> //selected columns ALL_COLUMNS List<DataColumn> //all columns

These objects are equal when you dump a whole page to a file.

ROWS Iterable<DataRow> //selected rows

, where:

DataRow { rowNumber(); first(); last(); data(): List<Object>; value(column): Object } DataColumn { columnNumber(); name() }
OUT {append()} //object to output data FORMATTER {format(row, col); formatValue(Object, col)} //converts data to String TRANSPOSED Boolean //checks if data editor is transposed (Gear Icon → Transpose) TABLE DasTable //object that represents the actual table you’re extracting data from

DasTable has two important methods:

Before v2017.3

DasObject getDbParent() JBIterable<DasObject> getDbChildren(Class, ObjectKind)

Since v2017.3

DasObject getDasParent() JBIterable<DasObject> getDasChildren(ObjectKind)

Additional information about the API can be found here.

When you create or edit Groovy scripts in IntelliJ IDEA, and have Groovy installed, coding assistance is available.

Coding assistance for Groovy scripts

Once CSV-ToOneRow-Groovy.csv.groovy is in the folder, you can use the extractor.

Use the extractor

Copy these values and paste them into the query.

Copy values and paste them into the query

Here’s another example that is based on SQL-Insert-Statements.sql.groovy. MySQL and PostgreSQL allow using multi-row syntax. To use this type of extractor, create a new SQL-Inserts -MultirowSynthax.sql.groovy file in the scripting folder.

Create a new SQL-Inserts-MultirowSynthax.sql.groovy file

Again, simply select it in the menu.

Select the script in the menu

See the result in DataGrip:

The result of the script

Feel free to use other generated extractors on GitHub:

Last modified: 28 November 2018