Data extractors

Data extractors set rules of how to copy or view your data in the editor (in the Text view mode). You can select a default extractor from the list near the Export Data icon (), configure the existing extractor, or create a custom extractor by using Groovy or JavaScript.

In DataSpell, the last selected extractor becomes the default one for newly opened editor tabs. For MongoDB, the default extractor is always JSON.

There are several built-in formats. You can export data as a set of INSERT or UPDATE statements, or as a part of the WHERE clause. Also, you can select text formats like CSV, JSON, HTML, or Markdown. For more information about exporting, see the Export section.

Configure an extractor for delimiter-separated values

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

From the list of data extractors, select Configure CSV formats.
In the CSV Formats dialog, click the Add Format icon .
Specify a name of a new format (for example, Confluence Wiki Markup).
Define settings of the format: set separators for rows and headers, define text for NULL values, specify quotation. Click OK.
When the format is created, you can select it in the drop-down list near the Export Data icon ().

Add a custom extractor

You can create your own extractor that you can write on Groovy or JavaScript.

In the Project tool window (View | Tool Windows | Project), navigate to Scratches and Consoles | Extensions | Database Tools and SQL | data | extractors.
Right-click the extractors node and select New | File.
Type the filename (for example, PHP.array.groovy), where:
- PHP: the name that is used in the extractor menu.
- array: output of the extractor.
- groovy: the extension of the script.
Type or paste the code of the extractor in a new file. You can use code of other extractors in the extractors folder to write your extractor. Also, you can try the following extractors:
- TSV-Icelandic-Groovy.tsv.groovy on GitHub: copy tab-separated query results and to paste them to Excel as comma-separated.
- Dbunit Dataset Export in Groovy on GitHub: an extractor for Dbunit export.
- Table-Text-Groovy.txt.groovy and Fixed-length-fields.txt.groovy: extractors that generate a txt file with fixed length columns.
- PHP-Array.groovy: an extractor to PHP array.
- Markdown-JavaScript.markdown.js: an extractor to Markdown.
Select the extractor from the list of extractors.
Consider the following example of adding the PHP-array extractor in DataSpell.

API for custom data extractors

Use the following API to create your custom data extractor.

Binding	Methods	Description
`DIALECT`	`getDbms()` followed by either one of the following methods: `isOracle` `isMysql` `isPostgres` `isBigQuery` `isRedshift` `isGreenplum` `isVertica` `isMicrosoft` `isSybase` `isDb2` `isHsqldb` `isH2` `isDerby` `isSqlite` `isExasol` `isClickHouse` `isCassandra` `isHive` `isSpark` `isSnowflake` `isMongo` `isCouchbase` `isCockroach` `isTransactSql` `isDocumentOriented`	Database dialect
`TABLE`	`getName()` `getParent().getName()`	Database table metadata
`ALL_COLUMNS`	`int columnNumber()` `String name()`	A list of all columns in the query result or currently open table editor.
`COLUMNS`	`int columnNumber()` `String name()`	A list of selected columns in the query result or currently open table editor.
`FORMATTER`	`format(Row, Column)` `formatValue(Object, Column)` `getTypeName(Object, Column)` `isStringLiteral(Object, Column)`	Default formatting methods.
`OUT`	`append(String)`	Extractor output.
`ROWS`	`int rowNumber()` `boolean first()` `boolean last()` `java.util.List<java.lang.Object> data()` `Object value(Column)`	`Row data stream.`
TRANSPOSED		Boolean value that indicates whether the table editor is in transposed mode.

Example

You can use the following simple CSV data extractor as a base for your custom data extractors. The full version of this data extractor is available in DataSpell. To locate it, in the Project tool window (View | Tool Windows | Project), navigate to Scratches and Consoles | Extensions | Database Tools and SQL | data | extractors

                ROWS.each {
                    row -> COLUMNS.eachWithIndex { column, i ->
                        if (i > 0) OUT.append(",")
                        OUT.append(FORMATTER.format(row, column))
                    }
                        OUT.append("\n")
                }
            

Last modified: 12 October 2022