Blog Blog
Home
Home/DataGrip/The Many Ways of Exporting Data in IntelliJ-based IDEs

The Many Ways of Exporting Data in IntelliJ-based IDEs

Maksim Sobolevskiy

In DataGrip, as well as all other JetBrains IDEs that feature database support, there is a powerful engine to export data in many formats. You can even create your own export format.

ExportSq1

Let’s look closer at this engine.

Export directions

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

To export to file:
      — Context menu on a table/view and → Dump data to file.
      — Context menu on a query and → Execute to file.
      — In the toolbar of the result-set or data editor, select Dump data button → To File…

DumpToFile

To export to the clipboard:

      — Select the data you need within a result-set or data editor and click Copy or press Ctrl/Cmd+C.
      — In the toolbar of a result-set or data editor, select Dump data button → To Clipboard

Default extractors

We’ll talk about copying data to the clipboard from the data editor, but the same applies to other described ways to export data.

Look to the left of the Dump data button for a drop-down where you can choose the extractor — the format to export data in.

ChooseSQLInserts

There are several built-in formats as you can see. Some of them let you export data as a set of INSERT/UPDATE statements, while others export data as text such as CSV, JSON, HTML, etc. For more details on how they work, please visit this page.

Still, users may need to extract data in many different ways.

Twitter

Creating a DSV-based extractor

Let’s see how we can extend the default functionality.

To create your own format based on CSV (or any DSV format), select Configure CSV formats…

ConfigureDSV

In this dialog, in addition to changing the existing CSV and TSV extractors, you can create your own. For example, Confluence Wiki Markup.

ConfluenceWikiMarkup

Once created, it appears among the other extractors.

ConfluenceWikiMarkuipInMenu

Creating any text extractor with scripting

For the more complicated cases, consider using scripting extractors. Several of them are already there such as CSV-Groovy.csv.groovy, HTML-Groove.html.groovy, and others. These scripts are written in Groovy, but they can also be written in JavaScript. Our examples use Groovy.

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

      CSV-Groovy is just the name of the script;
      csv is the extension of the result file if you extract to file;
      groovy is the extension of the script itself. It helps IntelliJ IDEA highlight your code if that’s where you create/edit your scripts.

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.

ChooseGoToSCriptsDirewctory

You should edit an existing extractor or just add your own to this folder.

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

Here is the diff view of two scripts: the existing CSV-Groovy.csv.groovy and our new one which we’ll name CSV-ToOneRow-Groovy.csv.groovy.

ScriptsDif

Note the available context bindings:

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

These objects are equal when dumping the whole page to 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.

CodingAssistance

Once the new script file is in the folder, you can use the extractor!

ChooseCSVGToOneRow

Copy these values and paste them into the query.

ToOneRowSQL

Here’s another example 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.

SQLInsertsMultiRowExtractor

Again, simply select it in the menu.

ChooseSQLMultiRow
The result:

InserMultiRowSQL

Another version of this extractor from our user is here: it also adds parentheses. It creates several lists if you’ve selected cells from several columns.

There are already some user-generated extractors on GitHub which everybody can use:

— Extractor to copy tab separated query results to paste into Excel with comma decimal separator.
— DBunit XML.
— Txt-file with fixed length columns. (Here’s a very similar one).
— Php array.
— Markdown.
— Jira.

We hope this tutorial will help you tweak DataGrip to your specific needs and encourage you to share extractors for all to use.

The DataGrip Team

#Tutorial
Comments below can no longer be edited.

34 Responses to The Many Ways of Exporting Data in IntelliJ-based IDEs

  1. Mike says:

    November 8, 2017

    At the head of the article is written «or any other JetBrains IDE featuring database support».
    I tried to find the same menus in IDEA 2017.3 EAP Ultimate Edition, but didn’t find it.

    The question is does IntelliJ IDEA 2017.3 EAP (Ultimate Edition) contains the features, described here? If yes, how can I convert XML to SQL with IDEA?

    Thanks.

    • Maksim Sobolevskiy says:

      November 9, 2017

      Can you please send us a screenshot of the absence of this menu? It should be there. Now you cannot convert XML to SQL, because this is import, not export. We considering extending import functionality as well.

  2. Peter says:

    December 10, 2017

    I would like to format dates to dd-mm-yyyy (the standard here). Is there a way to obtain the columns ‘raw’ value and apply a format or do I have to resort to cut+paste the string value from the FORMATTER.format ?
    I think it is a pity that FORMATTER.format does not take any localisation into consideration.

    • Maksim Sobolevskiy says:

      December 21, 2017

      You can use SimpleDateFormat or any other java formatter.

  3. Mahesh says:

    February 22, 2018

    What methods are available for DataRow and DataColun? Are there any JavaDoc available for the objects in data extractors?

  4. Dan says:

    March 27, 2018

    What are the limits on the size of the export? what if my SQL result is 1 MM rows or 1TB? (made up numbers, not sure where it starts being a problem).

    How do we export such sizes? how do you suggest scheduling exports as opposed to manually triggered?

    • Maksim Sobolevskiy says:

      March 28, 2018

      What export do you mean? To CSV? Or with the help of extractor?

      • Dan says:

        March 28, 2018

        1/ Could I use a “copy” or “UNLOAD” (similar to unload supported by REDSHIFT” and export the data (the result of the SQL) to external target location? if yes, any size limits?

        2/ if I use an extractor, I have to trigger it via the UI right? any limits there?

        is it all running internal or is it using a JDBC/ODBC connection? since those connections I am sure have some limits on the size they can support unless you implement chunking.

        • Maksim Sobolevskiy says:

          March 29, 2018

          There are no technical limits, it just requires more time for operation to be finished.

  5. Daniel says:

    May 19, 2018

    Is it possible to use the “Add table definiton” option with a custom extractor? I’m trying to do multirow syntax _with_ create table definition. Not seeing a way to do that…

    • Maksim Sobolevskiy says:

      May 21, 2018

      Sorry, but now it’s impossbile

  6. Matthias Vill says:

    May 22, 2018

    Is there a way to get a column’s type? Probably via TABLE? The DataColumn seems to lack this and DataGrip does not ship a groovy extension 🙁

    • Maksim Sobolevskiy says:

      May 22, 2018

      1. TABLE.getDasChildren(com.intellij.database.model.ObjectKind.COLUMN) will give you Iterable
      2. Cast it to DasColumn
      3. Then getDataType().getSpecification()

      • Matthias Vill says:

        May 23, 2018

        Thank you! Ended up with this:

        COL_TYPES = new HashMap()
        TABLE.getDasChildren(com.intellij.database.model.ObjectKind.COLUMN).each { column ->
        COL_TYPES.put(column.getName(), column.getDataType().getSpecification())
        }

        Now is there a way to get the DIALECT-specific quoting of table-/column-names?

        • Maksim Sobolevskiy says:

          May 23, 2018

          @Contract(“null,_,_->null;!null,_,_->!null”)
          String quoteIdentifier(String identifier, boolean force, boolean suppress);

          DIALECT.quoteIdentifier()

          • Matthias Vill says:

            May 23, 2018

            Thank you again!

            Is there something similar to TABLE for multi-table selects? When hovering over the result-column-headers types are provided, but I don’t see how I could get to them from a groovy-export-script.

            • Maksim Sobolevskiy says:

              May 23, 2018

              Finally, that is the stage where my answer is “No, it’s impossible” 🙂

  7. Oscar Camacho says:

    August 31, 2018

    Hi!
    I’m currently trying to create custom reports with this tool.
    I need to get the row count in all reports. Any ideas on how to do this?

    Thanks in advance.

    • Maksim Sobolevskiy says:

      August 31, 2018

      Can you show us the extractor you use?

  8. Oleksandr Gusiev says:

    February 15, 2019

    Hello, I want to create inserts from selects list and export to file. Everything works, but into Insert statements all tables named as MY_TABLE, as I understand problem in SQL-insert-statements.sql.groovy file from extractors folder, because TABLE value looks like null there. Please explain how to fix it, I am not good at groovy, unfortunately. It is very necessary for me!

    • Maksim Sobolevskiy says:

      February 18, 2019

      Can you please show us examples of these SELECTs?

      • Jason Harris says:

        June 12, 2019

        Hello, I’ve had the same issue. Here is an example of sql statement where all table in insert statements are MY_TABLE.

        SELECT * FROM students WHERE student_id=100;

        I put this in the groovy script and TABLE variable is null

        if (true) {
        this.binding.variables.each {k,v -> OUT.append(“$k = $v”).append(NEWLINE)}
        }

  9. Mikail Aliev says:

    May 16, 2019

    Hi Maksim,

    I’m looking for a way to extract data as SQL insert statements but with a special condition: if the column is auto_incremented, its value should be set to NULL. I have to run these queries later in another system but I have to manually delete the auto_incremented column name and value for each row.

    Any hint at where I should be looking at?

    Thanks!

    • Maksim Sobolevskiy says:

      May 17, 2019

      Why will you delete name? Do you need this column to be missed completely oк it should stay but with Null values?
      If first, try “Skip generated columns” in the extractors drop-down.

      • Jason Harris says:

        June 12, 2019

        I commented below when I should have replied here. Anyways, the same thing happens to me with this statement:

        SELECT * FROM students WHERE student_id=100;

        I did an inspection of the variables and TABLE = null;

  10. Jason Harris says:

    June 12, 2019

    Hello, I am having the same issue. Example:

    SELECT * FROM students WHERE student_id=100;

    Executing normally creates a result set with the table name in the tab.

    Execute to file with SQL Inserts as the format, the groovy script gets null for the table name, so all the inserts become “MY_TABLE”

    INSERT INTO “MY_TABLE”( …
    INSERT INTO “MY_TABLE”( …
    INSERT INTO “MY_TABLE”( …
    INSERT INTO “MY_TABLE”( …

    Postgres 9.6, DataGrip 2019.1.2, OS X Mojave 10.14.5

    • Maksim Sobolevskiy says:

      June 14, 2019

      Do you have students in the database tree on the left?

  11. David Brown says:

    November 1, 2019

    How to export using Groovy or any method multiple Services window result tabs say 100 from a loop cursor query as a single dump or export or better still looping query into a single tab?

    • Maksim Sobolevskiy says:

      January 23, 2020

      Sorry, there is no way to do that. You need to UNION queries.

  12. Sean Flanigan says:

    January 23, 2020

    I’ve been looking for an extractor for Atlassian Jira/Confluence, and finally found this article.

    I had to add to the Confluence rules visible in the screenshot, to get the right header markup:

    In Header Format down the bottom (after selecting “First row is header”):
    Value separator: ||
    Row Separator: Newline
    Null value text: Empty string
    Row prefix: ||
    Row suffix: ||

  13. Jason McGee says:

    February 24, 2020

    If I download a blob how can I save it to a file as text? I can see the text but I want to write it a file as text.

    • Maksim Sobolevskiy says:

      February 28, 2020

      It is not very clear what you are doing. Can you please elaborate? Screenshots would be perfect.

Latest Tweets

Related posts

DataGrip 2020.1.5

Introducing DataGrip 2020.1.5! Check out what’s new inside! Most important The issue with SQL Server Authentication on Windows is fixed. Other fixes DBE-9876: Forced execution in read-only mode now runs the correct query. DBE-10727: Schemas comparator respects column order. New features A new quick-fix is available: Refresh schema. If your colleague adds a new object after you have already refreshed your database, this fix will make sure your query is properly highlighted. We have also renamed the migration buttons in the schema comparator to make it clearer what they actuall
by Maksim Sobolevskiy

Introducing MongoDB Shell in DataGrip

Hi! We've released the new version of the MongoDB JDBC driver, which means that DataGrip now includes support for the new MongoDB Shell out of the box. This is the result of a close collaboration between engineering teams at MongoDB and JetBrains, and we are excited to finally release it to all our users. If you use DataGrip to work with MongoDB and Atlas, you get a console/shell experience that is 100% consistent with what you get in MongoDB’s own shell and other MongoDB developer tools. To use the newest version of the driver, just update it in the properties window of your MongoDB dat
by Maksim Sobolevskiy