DBeaver Documentation

DOWNLOAD pdf

Data import

Table of contents

Overview

DBeaver enables you to import data into tables. This guide explains the import process using XLSX as an example. For details on supported formats, see Data Transfer.

Note: Each format may have specific configuration settings that can be adjusted to meet your needs.

Steps to import data

  1. Choose the database table (or tables) you want to import data into. Do this by right-clicking on the table name in the Database Navigator section and then clicking on Import Data.

  2. In the window that appears, choose XLSX and click Next.

  3. In the following window, choose the file that contains the data you wish to import into the table, select the appropriate settings, then click Next. For more details on importer settings, see Import parameters.

Tip: You can import files from a remote file system using the Cloud Storage by clicking the Browser remote file system button . This feature is exclusively available to users of the Ultimate Edition, Team Edition, and CloudBeaver PRO versions.

  1. In the next window, set XLSX-to-table mappings. Please refer to our mapping process guide for more detailed information.

  2. Select your data load settings in the subsequent window, and then click Next. For more information, please refer to our article's section Data load settings article.

  3. In the final window, you can review all the settings you selected earlier. If you missed something, you could go Back and fix it. When you are ready, finish the import by clicking Proceed.

  4. If the XLSX file is valid and there are no errors, you will see a notification window with information about the completion of the task. You can keep working with your database during export, as the data loading will be performed in the background.

Import parameters
Setting name Description
Header position Determines the location of the column names in the Excel table, either at the top or none. This setting specifies whether the column names are located in the first row of the Excel table or if there are no column names present.
Skip empty rows If this setting is enabled, any open string values encountered during the data processing will be ignored and not inserted into the corresponding cells in the row. If the setting is disabled, all cells in the row will be filled with a NULL value if an empty string is encountered.
Import all sheets Specifies that all sheets in the file should be imported during the data import process.
Specific sheet name Enables you to choose a particular sheet from the Excel file for importing during the data transfer process.
Date/time format Use this setting to specify the date format used in the XLSX file. This is used to clarify the date format during the import process and does not affect the output data. You can refer to the section Patterns for data import for details on the format pattern syntax.
Timezone ID The local machine timezone is used by default. There are three ways to specify the timezone:
1) Local zone offset: Specify the offset from UTC in the format of either a positive or negative number (e.g., +3, -04:30).
2) Specific zone offset: Specify the offset from GMT or UTC in the format of GMT+/-X or UTC+/-X (e.g., GMT+2, UTC+01:00).
3) Region-based: Specify the timezone using a region-based identifier such as UTC, ECT, PST, etc.
Sample rows count Determines the number of rows that will be used as a sample to estimate the length and data types of the imported data.
Minimum column length This value is used when creating a new column and, if necessary, specifying its type. It indicates the minimum number of characters or digits expected in the column. This information helps determine the appropriate data type and size for the column during the creation process.
Save task Opens the Save Task window to assist in creating a task during the data transfer process. This window provides options and settings for creating and configuring a task related to the data transfer operation.

Patterns for data and time format

When specifying the date/time format for data import, ensure that you use the correct format specifiers. A common issue involves confusing format codes, such as using MM (for months) when mm (for minutes) is intended.

Refer to the table below for a list of common format symbols and their meanings.

Symbol Meaning Presentation Examples
G era text AD; Anno Domini; A
u year year 2004; 04
y year-of-era year 2004; 04
D day-of-year number 189
M/L month-of-year number/text 7; 07; Jul; July; J
d day-of-month number 10
Q/q quarter-of-year number/text 3; 03; Q3; 3rd quarter
Y week-based-year year 1996; 96
w week-of-week-based-year number 27
W week-of-month number 4
E day-of-week text Tue; Tuesday; T
e/c localized day-of-week number/text 2; 02; Tue; Tuesday; T
F week-of-month number 3
a am-pm-of-day text PM
h clock-hour-of-am-pm (1-12) number 12
K hour-of-am-pm (0-11) number 0
k clock-hour-of-am-pm (1-24) number 0
H hour-of-day (0-23) number 0
m minute-of-hour number 30
s second-of-minute number 55
S fraction-of-second fraction 978
A milli-of-day number 1234
n nano-of-second number 987654321
N nano-of-day number 1234000000
V time-zone ID zone-id America/Los_Angeles; Z; -08:30
z time-zone name zone-name Pacific Standard Time; PST
O localized zone-offset offset-O GMT+8; GMT+08:00; UTC-08:00;
X zone-offset 'Z' for zero offset-X Z; -08; -0830; -08:30; -083015; -08:30:15;
x zone-offset offset-x +0000; -08; -0830; -08:30; -083015; -08:30:15;
Z zone-offset offset-Z +0000; -0800; -08:00;

For more information on valid patterns, see the Java DateTimeFormatter Patterns.