DataGrip
 
2024.3
Shortcuts: Windows
Get DataGrip

Import from Excel files

Last modified: 22 November 2024

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.

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

Data source with the Sakila database and the Excel file

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

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

    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 Alt02.

    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 .

    tip

    You can drag your SQL files to Database Explorer from any other location, too. For example, from a directory opened in your file browser, or from another software.

Step 1. Prepare your file in IDE

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

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 () , 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

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