DataGrip 2024.2 Help

Import from Excel files

In this tutorial, we will learn how to import the data that you have in Excel files into your database.

Before you start

Make sure that you have the following:

  1. A running database instance ready to use.

  2. In DataGrip, a data source that can connect to your database instance.

  3. Excel file that contains your data.

For illustration purposes, in this tutorial we will use the Sakila PostgreSQL database running in Docker and the countries.xlsx Excel file.

Where do I get those materials?

So, the initial state includes the data source with Sakila PostgreSQL database and the countries.xlsx Excel file stored locally.

Start

In DataGrip, there are two ways of importing data from Excel files:

  • By using a context menu in Database Explorer (⌘ 1).

    In this case, you will use a context menu of an item to navigate to and select your Excel file in your file browser. You can select any Excel file that is accessible to you, no special association to DataGrip project is required.

    This case is preferable if your Excel file isn't located in a directory attached to your DataGrip project. For example, if you only need to run the import from a file once.

  • By dragging the file from Files tool window (Alt+2).

    In this case, you will drag your Excel file from Files tool window to your database in Database Explorer. You can drag the Excel file located in the directories attached to your project.

    This case might be preferable if the directory with your Excel file is already attached to your DataGrip project, and the file is available in the Files tool window .

Step 1. Prepare your file in IDE

For the Database Explorer scenario, you can skip this step.

To use the files that are stored locally on your machine in the Files tool window , you have to attach directories with them to your DataGrip project.

Attach the directory with dump files

  1. You can open the Files tool window by doing one of the following:

    • In the main menu, go to View | Tool Windows | Files.

    • On the tool window bar, click Files tool window icon Files.

    • Press Alt+2.

  2. In the Files tool window , click the Attach Directory to Project button (Attach Directory to Project) in the toolbar.

    Alternatively, right-click in the area of the Files tool window and select Attach Directory to Project.

  3. In the file browser, navigate to the directory that you want to attach. In our case, it is the myFiles directory.

  4. Click Open.

Directory with the Excel file is attached to the DataGrip project

Step 2. Start importing the data

To start importing data from your Excel file, you need to navigate to it first.

  1. In the Database Explorer (⌘ 1) , right-click the public schema and select Import/Export | Import Data from File(s).

  2. In the file browser window that opens, navigate to the countries.xlsx file.

  3. Click Open.

Select the Excel file in Database Explorer
  1. In the Files tool window (Alt+2) , navigate to the countries.xlsx file.

  2. Drag the countries.xlsx file to the database schema in Database Explorer (⌘ 1) that you want to import the data to.

Drag your Excel file from the Files tool window to Database Explorer

Once the import is initiated, the Import dialog opens.

Step 3. Configure the import settings

Importing data from an Excel file requires configuring the following settings in the Import dialog:

  • In the mapping settings section, you can configure the target schema and table, set column mapping, and so on.

  • In the source settings section, you can set a path to your Excel file, select a suitable data loader, and also set the first row of the Excel file to be the header row in your target database table.

  1. Configure the mapping settings:

    1. In the Import dialog, the mapping settings (the Mapping icon) is selected by default in the upper left pane.

    2. You can select in which schema to create a table and whether to import data to a new table or an existing one.

      In this tutorial, we will import data to a new countries table in the public schema. Let us use the preset corresponding values of the Schema and Table fields.

    Import data from Excel file: mapping settings
  2. Configure the source file settings:

    1. In the upper left pane of the Import dialog, select the source file to configure its settings.

    2. Select First row is header to mark the first row as a header.

    3. In the Mapping pane, set the following values in the Type column:

      Column

      Type

      country

      varchar(2)

      latitude

      numeric

      longitude

      numeric

    Import data from Excel file: source settings
  3. To start importing data from the Excel file to your database, click OK.

As a result, DataGrip imports the data from countries.xlsx Excel file to a new countries database table.

Excel file data is imported into the database table
Last modified: 26 August 2024