DataGrip
 
2023.1
Shortcuts: Windows
Get DataGrip
You are viewing the documentation for an earlier version of DataGrip.
  1. Export and import
  2. Import

Import

Last modified: 21 June 2023

To import data from a script file, run the file as it is described in the Run files page. In addition to script files, you can import data from a CSV, TSV, or any other text files that contain delimiter-separated values.

To learn how to add the SQL files that are stored on your machine to your project in DataGrip, refer to Attach a directory with SQL files.

Import data to a database

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

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

    Run an SQL file

  3. Select the settings for your run configuration.

    • Target data source / schema: databases or schemas against which you want to run your database scripts. This setting is dialect-dependent.

      If you select a data source as a target, DataGrip displays a schema in which the script will be run. It is the default schema.

    • Script files: SQL files that you want to run. To add files, click the Add button (the Add button) and navigate to files that you want to run. If a script contains schema switching, you will see a warning (Warning).

    Edit Configuration dialog

  4. Click Run.

tip

To see other options of how you can run an SQL file against a database, see Run files.

Import delimiter-separated values into a database

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

  2. Navigate to the file that contains delimiter-separated values and double-click it.

  3. In the Import "file_name" File dialog, specify the data conversion settings and click Import.

    You can select in what 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 Table and Target schema lists.

    To mark the first row as a header, right-click the row in the Data Preview pane and select First Row Is Header.

    For more information about data conversion settings, see Import File dialog.

    Import delimiter-separated values into a database

Import multiple CSV files into a database

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

  2. Navigate to the files that contain delimiter-separated values, select them, and click Open.

  3. In the Import "file_name" File dialog, specify the data conversion settings for every file. You can go through the files in the Sources pane. Click Import to import the files.

    You can select in what 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 Table and Target schema for all sources lists.

    To mark the first row as a header, right-click the row in the Data Preview pane and select First Row Is Header.

    For more information about data conversion settings, see Import File dialog.

    Import multiple CSV files into a database

note

If you drag a file into a schema or carry out the Import from File command for a schema, DataGrip creates a new table for the data that you import. If you perform the same actions for an existing table, DataGrip adds the data to that table.

Import multiple tables to a different schema, database, or existing table

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

  2. Open the Import 'table_name' Table dialog. To open the Import 'table_name' Table dialog, 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 CtrlShift0A, type copy tables to and press Enter.

  3. From the Target schema for all sources list, select a schema in which you want to create a table. In case of a single table, this menu item is called Target schema.

  4. In the Table list, type a name of a new table or select an existing table to add data to the selected table.

  5. In the Sources list, click tables that you want to configure.

  6. (Optional) Click the Add icon (the Add button) to add columns, keys, and indexes.

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

    Select Disable indexes and triggers, lock table (may be faster) if you want to 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.

  8. Click Import.

Restore a full data dump for MySQL and PostgreSQL

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.

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

tip

For PostgreSQL, you can use Restore with "pg_restore" on a table level.

Import dialogs

The Import "file_name" File dialog appears when you import CSV, TSV, or any other text files that contain delimiter-separated values.

Import delimiter-separated values into a database

Settings for files with delimiter-separated values

These setting are available in the Import File dialog only.

Item

Description

Formats

Select a template that successfully converts the file data into a table. You can change settings of predefined templates or add a new template. To add a template, click Add Format button (the Add Format button).

To save changes, click the Save icon the Save icon and select one of the following options:

  • Save Changes: save changed settings of the current template are saved.

  • Save As: create a new template and save settings of the new template.

  • Save All: save changes in all templates.

UseApp general add,App general remove, App actions previous occurence and App actions next occurence to create, delete and reorder the formats; App actions copy to create a copy of the selected format.

Value separator

Select or type the character that you want to use as a separator for values.

Row separator

Select or type the character that you want to use as a separator for rows.

Null value text

Select or type the text that you want to use if a cell contains the NULL value.

Add row prefix/suffix

Click the link and type a row prefix and suffix. Prefix and suffix are character sequences which in addition to the row separator indicate the beginning and end of a row.

Quotation

Each line in the area under Quotation is a quotation pattern. A quotation pattern includes:

  • Left: a quotation character that is inserted before a value.

  • Right: a quotation character that is inserted after a value.

  • Escape: an escape method or character for the cases when the quotation character is part of a value. The <duplicate> value means that if a quotation character occurs within a value, it is doubled. You can specify your own escape character.

If there is more than one pattern, the first pattern is used.

UseApp general add,App general remove, App actions previous occurence and App actions next occurence to create, delete and reorder the patterns.

Quote values

Select when you want to enclose values within quotation characters.

  • Never: do not quote values.

  • When needed: quote a value if it contains the value or the row separator.

  • Always: quote all the values.

Trim whitespaces

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

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.

Table name, structure and data mappings

Item

Description

Target schema

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

Table

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

Comment

Comment to a table.

Columns | Keys | Indexes | Foreign Keys

Data mappings and definitions of columns, keys, and indexes. Double-click the line to start editing.

The Mapped to field specifies relation of a data column from the file and the corresponding column in the database. If you clear this field, no data is added to the target column in the database.

To remove a column, select the corresponding line and click the Remove button the Remove button.

Data and DDL previews

Item

Description

Data Preview

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

DDL Preview

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

Encoding, errors, and logs

Item

Description

Encoding

(Import File dialog only) 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.