DataSpell 2024.3 Help

Import

Enable the Database Tools and SQL plugin

This functionality relies on the Database Tools and SQL plugin, which is bundled and enabled in DataSpell by default. If the relevant features are not available, make sure that you did not disable the plugin.

  1. Press Ctrl+Alt+S to open settings and then select Plugins.

  2. Open the Installed tab, find the Database Tools and SQL plugin, and select the checkbox next to the plugin name.

Import data

  1. In the Database tool window ( Window | Tool Windows | Database) , right-click a data source, or a schema and select SQL Scripts | Run SQL Script.

    Run an SQL file from the Database tool window

  2. In the file browser window that opens, navigate to the SQL file that you want to run.

  3. Click Open.

    You can view the output in Run tool window. For more information about tool window controls, refer to Run tool window.

Import data from CSV files

DataSpell uses data extractors to import data from CSV files. For more information about data extractors, refer to the Data extractors topic.

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

  2. Navigate to the CSV files and select them.

  3. In the mapping tree of Import dialog, select the mapping node (the Mapping icon) and specify the data conversion settings.

    You can select in which schema to create a table and whether to import data to a new table or an existing one. To select a schema or a table, use Schema and Table fields.

  4. In the mapping tree, select the source file to configure its settings. To mark the first row as a header, select First row is header.

  5. Apply the changes and close the dialog.

Import DSV file: mapping settings
Import DSV file: CSV Source settings

Import data from tabular data files

DataSpell uses scripted data loaders to import data from tabular data files. For more information about scripted data loaders, refer to the Data loaders topic.

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

  2. Navigate to the tabular data files and select them.

  3. In the mapping tree of Import dialog, select the mapping node (the Mapping icon) and specify the data conversion settings.

    You can select in which schema to create a table and whether to import data to a new table or an existing one. To select a schema or a table, use Schema and Table fields.

  4. In the mapping tree, select the source file to configure its settings. To mark the first row as a header, select First row is header.

  5. Apply the changes and close the dialog.

Import tabular data file: mapping settings
Import tabular data file: Tabular data Source settings

Import tables

  1. Select the tables that you want to copy to a different schema, database, or existing table.

  2. Open the Import dialog. To do that, you can use the following actions:

    • Right-click the selection and navigate to Import/Export | Copy Tables to.

    • Drag the selection to a schema or a database.

    • Press Ctrl+Shift+A, type copy tables to and press Enter.

  3. In the mapping tree of Import dialog, select the mapping node (the Mapping icon) and specify the data conversion settings.

    You can select in which schema to create a table and whether to import data to a new table or an existing one. To select a schema or a table, use Schema and Table fields.

  4. In the mapping tree, select the source file to configure its settings.

  5. (Optional) Select Insert inconvertible values as null if you want to insert NULL when the IDE meets an inconvertible value.

  6. Apply the changes and close the dialog.

Import tables: mapping settings
Import tables: table source settings

Restore Microsoft SQL Server table data

You can restore a Microsoft SQL Server table data by using the bcp utility. The tool is not integrated into DataSpell. You can read about it at learn.microsoft.com.

  1. In the Database tool window ( Window | Tool Windows | Database) , right-click the table that you want to import your data to and select Import/Export | Restore with 'bcp'.

  2. In the Restore with bcp (<data_source_name>) dialog, specify the path to the bcp executable in the Path to bcp field.

  3. In the Source file, specify the path to the file that you want to restore the data from.

  4. Click Run.

Restore a Microsoft SQL Server table data using bcp

Restore a full data dump for MySQL and PostgreSQL

You can restore data dumps by using 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.

If you see no restore options in the context menu, verify that you use a correct JDBC URL for the data source.

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

    • Restore with "mysql": for MySQL data sources. In the Path to mysql field, specify the path to the MySQL executable (for example, C:\Soft\mysql-8.0.19-winx64\bin\mysql.exe).

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

    • Restore with "psql": for PostgreSQL data sources. The psql option is available for the most 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.

Import dialog

The Import dialog appears when you import a file, a table, or a query result set into your database.

In the mapping tree that is in the upper-left pane of this dialog, you can create file-to-table, table-to-table, and query-to-table mappings (the Mapping icon). In the settings pane in the upper-right part of the dialog, configure mapping settings and change settings of the sources (CSV file, table, and query). The preview pane in the bottom part of the dialog displays generated previews of your data, of the source data with different source settings, and of DDL statements.

  1. Mapping tree.

  2. Source settings.

  3. Preview pane.

Mapping tree

Icon

Item

Shortcut

Description

the Add icon

Add

Alt+Insert

Create a new object.

the Remove icon

Remove

Alt+Delete

Delete the selected object.

the Edit icon

Edit

F4

Open selected object in the object editor.

the Edit On Selection icon

Edit On Selection

Toggle between opening the object in object editor on selection and by double-clicking it.

the Back icon

Back

Ctrl+Alt+Left

Previous object.

the Forward icon

Forward

Ctrl+Alt+Right

Next object.

Settings

Item

Description

Path

Defines the path to source file.

Charset

Determines which charset is used to read the file.

Format

Determines which format to use to read the file.

Click to configure the CSV file format.

First row is header

Treat the first row as a row that contains column names.

First column is header

Treat the first column as a column that contains row names.

Trim whitespaces

Ignore or remove whitespace characters. If this checkbox is cleared, the whitespace characters are treated as parts of the corresponding values.

Column

Source file column name.

Type

Source file column data type.

Item

Description

Path

Defines the path to source file.

Loader

Determines which scripted data loader is used to import the file data.

First row is header

Treat the first row as a row that contains column names.

Column

Source file column name.

Type

Source file column data type.

Item

Description

Table

Defines the source table.

Item

Description

Target

Defines the target object against which the query is run.

Query

Defines the source SQL query.

Item

Description

Schema

Schema in which you want to create or to copy a table. You can select schemas in another data source.

Table

Name of a new table or an existing table in case you want to add data to the existing table.

  • Click Auto Generate for the name to be generated automatically.

  • If available, click to create a new table with the specified name in the same Import dialog. The table with its objects will appear in the mapping tree as a new node.

Mapping

the Add icon, the Remove icon, the Up icon, the Down icon

Use these buttons to add items, remove them, and move them up and down the list.

Target Column

Column name in the target table.

Source Column

Column name in the source table.

Encoding

Select the character encoding for your data in the source file.

Write errors to file

Write errors that occur during the import in a text file.

Insert inconvertible values as null

Insert the NULL value into the table instead of the data that cannot be converted.

Disable indexes and triggers, lock table (may be faster)

Disable indexes and triggers during the import. Note that if you selected this option, it might lead to a situation when a trigger does not fire and fails to pass its results. Though it might improve the performance of the importing process. The option is available only when the target table (to which you copy values) has indexes or triggers.

Data Preview pane

Item

Description

the Modify Source icon Modify Source

and

file: <file_name> format: <file_format>

Opens the CSV Source section with source file settings.

Data Preview

The generated preview of how the data is read using the configured settings.

Item

Description

the Modify Source icon Modify Source

Selects the source file path in the Path field.

the Reload Page icon Reload Page

Reloads the generated preview.

Data Preview

The generated preview of how the data is read using the configured settings.

Item

Description

the Reload Page icon Reload Page

Reloads the table preview.

the Modify Source icon Modify Source

and

table: <table_name>

Opens the Table Source section with source table settings.

Data Preview

The generated preview of how the data is read using the configured settings.

Item

Description

the Reload Page icon Reload Page

Reloads the query result set.

the Modify Source icon Modify Source

and

table: <table_name>

Opens the Query Source section with source query settings.

Data Preview

The generated preview of how the data is read using the configured settings.

Item

Description

Data Preview

The generated preview of how your data will be imported to the database.

DDL Preview

A statement or statements that DataSpell will run to create a table. You can edit the statements in the DDL preview field.

Last modified: 29 October 2024
ExportData extractors